Finding matched and unmatched records in 3 tables 1

[GPM4663]

Hi Everyone,
I have three tables tblCredit, tblInvoice and tblBudget. Each table has a customer, item and quantity field. I need to develop a query that shows the tblCredit.quantity, tblInvoice.quantity and tblBudget.quantity for each related customer and item record. My problem is that a customer can have a tblBudget.quantity for an item with no quantity in the other tables or a tblcredit.quantity with no tblinvoice.quantity etc. So if I run a normal query with the tables linked I only get a record if all three have a quantity value which is rarely the case.

If I had two tables I would just change the join type to show all the records for one table and then run a separate unmatched query followed by a union query. But with three tables the combinations seem complex and I was wondering if there is a better way to do it without creating dozens of queries. There is a seperate tblCustomers and tblItems if that would help in the solution. I tried a few combination of using inner joins but couldn't get it right.

Thanks in advance to anyone who can help me.

GPM

 

[lupins46]

Are you saying that if a customer appears on any of the 3 tables then they should appear in the output?

 

[PHV]

A starting point:
...
FROM ((tblCustomers AS A
LEFT JOIN tblCredit AS C ON A.CustomerID = C.CustomerID)
LEFT JOIN tblInvoice AS I ON A.CustomerID = I.CustomerID)
LEFT JOIN tblBudget AS B ON A.CustomerID = B.CustomerID
WHERE Trim(C.CustomerID & I.CustomerID & B.CustomerID & "") <> ""

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

 

[GPM4663]

Hi Guys,
PH - thanks for that, I am definately one step closer. I need to incorporate the fact that there are item numbers that also linked to each record. For example if a customer had a budget quantity for two items but an invoice quanity for one I get the invoice quantity in both lines of the record. Any ideas - I was trying to do it just now but got an error about aggregates.

Lupins46 - yes that's it exactly, so even if it was only in one table it would appear and the other quantity values would be blank.

Thanks, both you guys this is really helping.

GPM

 

[GPM4663]

Hi Guys,
PHV - many thanks for your post I finally got things running. I extended the query to include the item values as well and have everything running the way I wanted. It even opens quite quickly. Your exmample was ideal and got me thinking about using it more in the future.

Thanks again,

GPM