Query Confusion!!!!!

[Guest]

Hi All,
I've reached a point where I have confused myself. I have three tables:
Table 1 holds Member Information (MemberId, Name, Address, etc) Table 2 holds
Pledge Info(MemberId,Amount, Date, PledgeID, etc) and Table 3 holds Payment
Info(Member Id, Pymt ID, Amount, Date). Table 2 & 3 are related to Table 1
by the MemberId field present in each table.

My problem comes when conducting a query in which I want to see the sum of
pledges and sum of payments for a given member. I created seperate queries,
one to show pledges and one to show payments. How can I combine both to get
one query only without having duplicate entries?

 

[Tom Ellison]

Dear Patty:

If a member has 2 pledges and 3 payments, you cannot possibly show all that
information in one row. Do you want just a summary of the pledges and
payments made, so there is only one row for each member? Do you want to see
all members, or only those with at least one pledge or payment?

If you have a query you attempted to use to do this, please post it.
Someone here may be able to help you fix it up so it will do what you want.
If you could show the results this query produces, and the results you'd
actually like, that would also be very helpful.

Tom Ellison

 

[Guest]

Add a field to the Payment table for PledgeID. This way you can relate the
pledge to the payment.

 

[Michel Walsh]

Hi,


Maybe something like


SELECT table1.MemberID,
Nz(SUM(table2.Amount), 0),
Nz(SUM(table3.Amount), 0)

FROM (table1 LEFT JOIN table2
ON table1.memberID=table2.memberID)
LEFT JON table3
ON table1.memberID=table3.memberID



Hoping it may help,
Vanderghast, Access MVP