I have 2 queries, I export the results to excell and then manually reconcile them. The SQL for the 2 queries are as follows :

qrySolomanCks:

SELECT CVR.CheckNum,
            CVR.CheckDate,
            CVR.Name,
            CVR.CheckAmount,
            CVR.CpnyID
FROM dbo_xvw_CheckVoucherReport as CVR
WHERE (((CVR.CheckDate)
Between #1/1/2003#
And #6/30/2003#))
GROUP BY CVR.CheckNum,
            CVR.CheckDate,
            CVR.Name,
            CVR.CheckAmount,
            CVR.CpnyID
ORDER BY CVR.CheckNum;

qryCkVcrRept:
SELECT Master.CheckNum,
            Master.CheckDate,
            Master.VendorName,
            Sum(Detail.InvoiceAmount) AS SumOfInvoiceAmount
FROM Master
INNER JOIN Detail
ON Master.CheckNum = Detail.CheckNum
GROUP BY Master.CheckNum, Master.CheckDate, Master.VendorName
HAVING (((Master.CheckDate)
Between #10/1/2002#
And #12/31/2002#))
ORDER BY Master.CheckNum;


I am wondering how i can reconcile these using SQL OR TSQL the Reconciling info are

Master.CheckNum should =CVR.CheckNum

and

CVR.CheckAmount, = Sum(Detail.InvoiceAmount) AS SumOfInvoiceAmount

Sorry if i seem not to be trying but i am on my 2nd day of my 1st job

:D

PS Thanks for all the help guys, or i would never have this job!!



Mike

Use a UNION ALL clause to make one query out of the 2 like this;

SELECT
CVR.CheckNum,
CVR.CheckDate,
CVR.Name,
CVR.CheckAmount,
CVR.CpnyID
FROM dbo_xvw_CheckVoucherReport as CVR
WHERE (((CVR.CheckDate)
Between #1/1/2003#
And #6/30/2003#))

UNION ALL

SELECT
Master.CheckNum,
Master.CheckDate,
Master.VendorName AS Name,
Sum(Detail.InvoiceAmount) AS CheckAmount,
"" as CpnyID
FROM Master
INNER JOIN Detail
ON Master.CheckNum = Detail.CheckNum
GROUP BY Master.CheckNum, Master.CheckDate, Master.VendorName
HAVING (((Master.CheckDate)
Between #10/1/2002#
And #12/31/2002#))
ORDER BY Master.CheckNum;


The UNION ALL keeps any duplicate values that you may have. If you desire to remove the duplicates, use UNION without the ALL. On a union statement all fields must appear in the same order and they must be named (or aliased) the same. This is why I aliased some of them.
I do notice that you are doing a grouping function on one of the queries. Make sure that both queries run well by themselves before combining them.


Let me know if it worked.



Sal