Creating query to summarize data from three tables

[promark747]

This is probably easy, but I'm stumped. I have three large tables (50k+ records) that I import daily from external csv files. The tables do not have identical fields (so I can't append/merge them into one table), but do have some common ones such as Customer Account Number. For a simple example:



Table 1

Customer	Name
A	Alan
B	Bob
C	Chuck

Table 2

Customer	Apples
A	1
A	1
B	3

Table 3

Customer	Oranges
B	5
B	5
C	6

I want to write a query to summarize all the data with an output that looks like this:

Customer	Name	Apples	Oranges
A	Alan	2
B	Bob	3	10
C	Chuck		6

I have tried many combinations of inner and outer joins but cannot seem to find the right settings. Usually I end up with data that is many times higher than the expected amount (as if the query is adding from duplicate lines).

Any help would be appreciated.

[June7]

Try:

Build queries that summarize the data in tables 2 and 3 with GROUP BY on Customer. Then join those two queries to Table 1.

[roaftech]

Hi,
what you are getting is Access's version of acoustic feedback (or back-pressure if you understand a bit of plumbing). It can be infuriating to get multiple records but it helps to remember that it's only a computer and it doesn't understand what you thought you told it to do.

As an alternative to the answer already offered, are you able to reconstruct your Table 2 to include all types of fruit together with the customer code, so that you can use append queries to incorporate your new data each day? The append query should be able to add the new data only to each respective field in the combined table. This gives you two tables - 'customers' and 'all fruit' - and will eliminate the need to try to read three tables simultaneously. (You could merge them into one table but I would recommend keeping the customer details separate.)
Table 1: Customers - as above
Table 2: Fruit
Customer..|..Apples..|.. Bananas..|..Cherries..|..Damsons..|..Etc...
..A.......|.....2....|............|............|.. .........|........
..B.......|..........|......4.....|............|.. .........|........
..B.......|..........|............|............|.. ...8.....|........
..C.......|.....2....|............|............|.. .........|........
..D.......|..........|............|.....7......|.. .........|........

You could then 'sum by' customer and include the link to Table 1 to include their name (and any other details).