How to create such a query?

[QB]

Hello,

I need to output data and am lost on how-to achieve it.

I have 2 tables: Billing and Extras. Each project can have multiple entries
in the Billing table, but only one entry in the Extras table. The structure
of each table is very similar

Billing
B_Id
B_ProjNo
B_Date
B_Amount

Extras
B_E_Id
B_E_ProjNo
B_E_Date
B_E_Amount

What I need to do is output a listing by project number and date the total
amount. For some reason I just can't wrap my head around this one?!

For Example
Billing
abc 2009/04/30 200.00
abc 2009/05/15 250.00
abc 2009/06/22 175.00

Extras
abc 2009/05/15 55.00

Would result in
abc 2009/04/30 200.00
abc 2009/05/15 305.00
abc 2009/06/22 175.00

I can't quite figure out how-to group not only on the ProjNo but also each
date.

Thank you for your help in advance.

QB

 

[John Spencer MVP]

The SQL for the query would look like the following.

SELECT Billing.ProjNo
, Billing.B_Date
, SUM(Nz(Billing.B_Amount,0) + Nz(Extras.B_E_Amount,0)) as Total
FROM Billing LEFT JOIN Extras
ON Billing.B_Id = Extras.B_E_Id
AND Billing.B_Date = Extras.B_E_Date
GROUP BY Billing.ProjNo
, Billing.B_Date

In Design view (query grid), you would
-- Add both tables
-- Drag from Id to Id and from Date to Date to set up the join
-- Double click on each join line and choose the option to show ALL from
Billing and matching from Extras
-- Add ProjNo, Date, and Amount from billing table
-- Change the Amount field to read
Total: Nz(Billing.B_Amount,0) + Nz(Extras.B_E_Amount,0)
-- Select View:Totals from the menu
-- Change GROUP BY to SUM under the Total field

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[QB]

I changed the

ON Billing.B_Id = Extras.B_E_Id

To

ON Billing.B_ProjNo = Extras.B_E_ProjNo

Since the B_Id and B_E_Id have no correlation (simply autombers) and it
seemed to do what I wanted....

Except, you had to know that was coming...

In the case where the Extra is made on an alternate date then the Billing
the amount never appears?! If I retake my initial example and slightly
modify it to illustrate the issue:

For Example
Billing
abc 2009/04/30 200.00
abc 2009/05/15 250.00
abc 2009/06/22 175.00

Extras
abc 2009/05/14 55.00
abc 2009/05/15 65.00

The Query would result in
abc 2009/04/30 200.00
abc 2009/05/14 55.00
abc 2009/05/15 315.00
abc 2009/06/22 175.00

Thank you once again,

QB

 

[John Spencer]

Well, with the information you have given us, I see no way to combine
the extras with a date of 05/14 with any record in billing (at least
reliably).

If you add a billing for abc on 5/13 then would you want the extra on
5/14 to be combined with the 5/13 billing or the 5/15 billing?

You could probably construct a query that would assign extras to the
nearest billing date and even have a way to assign to the earliest (or
latest) billing date in the case of ties.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================