Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 7th, 2003, 09:38 PM
Registered User
 
Join Date: Oct 2003
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Using SQL to reconcile 2 diff Tables (chk Regs)

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


 
Old October 9th, 2003, 02:35 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
DetailsView: chk 4 changed data before modechange? ledneh ASP.NET 2.0 Basics 0 December 18th, 2007 07:00 PM
updating SQL tables leopardj Beginning PHP 2 January 29th, 2007 06:42 AM
Diff b/w SQL Server 7 and SQL Server 2000 ashu_from_india SQL Server 2000 1 April 25th, 2005 01:00 PM
update the database when selecting a chk box ..... urindian ASP.NET 1.x and 2.0 Application Design 1 February 5th, 2005 04:39 PM
chk null rajesh23_82 BOOK: Professional Crystal Reports for VS.NET 0 January 26th, 2005 03:26 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.