Access 2007 3 table query

[Debbie Toney]

Access 2007 3 table query: Contact table, Event table, Payment table. Contact may have an Event but no Payment, a Payment but no Event, or an Event and Payment. My query is duplicating the Event and Payments. I have attached an example: The Contact Smith has 2 Events (Rome night and Men club) and 2 Payments ($4000 and $100) that are not associated. I want the query to return 4 records: 2 showing the Payments with associated Contact info (with nothing in the Event fieelds) and 2 showing the Events with associated Contact info (with nothing in the Payments fields.

I can copy and paste SQL with instruction but don't know how to write it.

** Edit **
[imgnothumb]http://bytes.com/attachments/attachment/4571d1295202541/sampleqryresult s.jpg[/imgnothumb]

[malcolmk]

Why not just create two queries in the query wizard, one to show events and one to show payments?
Wizard is easiest for simple queries I think.

[Debbie Toney]

I need one query that shows ALL Contacts and ANY Payments and/or ANY Events they may have.

[mshmyob]

Go into the SQL view and copy/paste your SQL.

cheers,

[Debbie Toney]

SELECT DISTINCTROW [CONTACT DEMOGRAPHICS].CID, [CONTACT DEMOGRAPHICS].SOURCE, [CONTACT DEMOGRAPHICS].LastName, [CONTACT DEMOGRAPHICS].FirstName, [CONTACT DEMOGRAPHICS].Address, Event.EID, Event.[Contact ID Number] AS [Event_Contact ID Number], Event.[Date OF Event], Payments.PID, Payments.[CONTACT ID NUMBER] AS [Payments_CONTACT ID NUMBER], Payments.DatePaymentEntered, Payments.PaymentAmount, Payments.DirectFundsTo, IIf([paymentamount]=4000,"",[name of event]) AS Expr1
FROM ([CONTACT DEMOGRAPHICS] LEFT JOIN Payments ON [CONTACT DEMOGRAPHICS].CID = Payments.[CONTACT ID NUMBER]) LEFT JOIN Event ON [CONTACT DEMOGRAPHICS].CID = Event.[Contact ID Number];

[NeoPa]

Fundamentally, you need to join the Event and Payment data together by using a UNION query, then JOIN this data to your Contact table on the right side of a LEFT JOIN. The Event and Payment data in the UNION query part would need to occupy separate fields.

[Debbie Toney]

NeoPa - got it right!!!! thanks.