Query Help,

[Marc Robillard]

hi all,

Quick question for you expert !

I got 3 table

Bill
BillDet
BillPayment

I need on 1 query to obtain : Bill_id, Sum(Billdet_ItemTotal), and
Sum(BP_Amount)

Tables goes like this

Bill
Bill_id PK
....

BillDet
BillDet PK
Bill_id FK
Billdet_ItemTotal
....

BillPayment
Bp_id
Bill_id
BP_Amount
....

the Query I need is to return all Bill that have a payment Greater or equals
to the bill Total.

here is what I got : but Access is king of messing the caculation, I don't
know Why! It will return the correct BillTotal, but only the last
PaymentTotal is good.


SELECT DISTINCT Bill.Bill_id, Sum(BillDetails.BillDet_TotalItem) AS
SumOfBillDet_TotalItem, Sum(Bill_Payment.BP_Amount) AS SumOfBP_Amount,
Bill_1.Bill_id
FROM (Bill AS Bill_1 INNER JOIN (Bill INNER JOIN BillDetails ON Bill.Bill_id
= BillDetails.Bill_id) ON Bill_1.Bill_id = Bill.Bill_id) LEFT JOIN
Bill_Payment ON Bill_1.Bill_id = Bill_Payment.Bill_id
GROUP BY Bill.Bill_id, Bill_1.Bill_id

Regards to all !

Thanks in advence.

Marc. R.

 

[Jeff Boyce]

Marc

While it is probably possible to create a single SQL statement (i.e., 1
query) to do what you're asking, why? As in "why does it matter how many
queries it takes as long as you get the right answer?" (I suspect other
newsgroup readers can offer SQL that does this in one SQL statement...)

One approach (using more than one query) might be to first find totals
(sums) for all bills, then create a second query that uses the first query
joined to the bill-payment table to do the selection/comparison you
mentioned (payment > Bill-Total).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Marc Robillard]

Thank that Help a lot, I create some Queries that I use into my main query,
as you said and it works !