Query Construction Help

[worldwise]

I have these two tables:

CHECKS [1:M] RECEIPTS
checkNumber (PK) receiptID (PK - autonumber)
checkAmount checkNumber (FK)
checkDate receiptAmount


I would like a single table with the following 3 columns:

Checks.checkNumber, Checks.checkAmount, Sum(Receipts.receiptAmount)

*Each check should only be listed once

Thanks in advance for any help you could give me.

-kevin

 

[PHV]

Why not simply an aggregate query ?
SELECT Checks.checkNumber, Checks.checkAmount, Sum(Nz(Receipts.receiptAmount),0) As TotalReceipt
FROM Checks LEFT JOIN Receipts ON Checks.checkNumber = Receipts.checkNumber
GROUP BY Checks.checkNumber, Checks.checkAmount;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[worldwise]

PHV, thanks for your quick reponse. I've tried you're code and run into this error:

==========================================================
Wrong number of arguments used with function in query expression 'Sum(Nz(Receipts.receiptAmount),0)'.
==========================================================

Also, I'm not quite sure why you are GROUPING by Checks.checkNumber OR Checks.checkAmount. I mean, Checks.checkNumber is the primary key for that table so there is nothing to group by?

Also, each checkNumber will only have one checkAmount entry so I'm also confused why you are grouping by this field?

 

[PHV]

Sorry for the typo:
SELECT Checks.checkNumber, Checks.checkAmount, Sum(Nz(Receipts.receiptAmount,0)) As TotalReceipt
FROM Checks LEFT JOIN Receipts ON Checks.checkNumber = Receipts.checkNumber
GROUP BY Checks.checkNumber, Checks.checkAmount;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[worldwise]

Is Nz an argument that Access accepts? It's giving me trouble:

=========================================================
Microsoft JET Database Engine (0x80040E14)
Undefined function 'Nz' in expression.
=========================================================

 

[PHV]

OK, so try this:
SELECT Checks.checkNumber, Checks.checkAmount, Sum(Receipts.receiptAmount) As TotalReceipt
FROM Checks INNER JOIN Receipts ON Checks.checkNumber = Receipts.checkNumber
GROUP BY Checks.checkNumber, Checks.checkAmount;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[worldwise]

PHV,

OK. Great! that works. Thanks.

I kinda get the query now and I'm trying to see if there is a better way to write it? Don't get me wrong, I very much appreciate your help, but the GROUP BY part seems like a workaround because there really is no point to group by those attributes since they are unique.

I mean, if I wanted to get more attributes from the CHECKS table, I would then start having to add them to the GROUP BY clause right?

I think this would also work - but I can't get the syntax right. What do you think?

SELECT checkNumber, checkAmount FROM CHECKS LEFT JOIN ON
(SELECT Receipts.checkNumber, Sum(receiptAmount) FROM
RECEIPTS GROUP BY Receipts.checkNumber) WHERE Checks.checkNumber = XXX.checkNumber.

I think my problem is that I do not know how to identify the 2nd table that I created. Therefore I have XXX.checkNumber.

 

[PHV]

but the GROUP BY part seems like a workaround
It is mandatory when you play with aggregate functions.
Search your local drives for files named jet*.chm and feel free to consult them.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[worldwise]

Thanks! Will do!!

I still want to find out if I can do it this other way so you might find another post from me when you're browsing the Tek-Tips forumns~~