Linking different tables in Query

[Mansoor Ahmad]

Dear All

In my database I have three main tables for three different customers, say customer 1, customer 2 and customer 3. Each table contains similar fields like part no, qty, analysis date etc. These tables at the moment are not linked together in any relationship. I want to search data for comparison from three tables and present on a report or in the form of a graph. For example I want to search for total quatity of parts month wise for each customer.

I have tried to connect the three tables in a relationship with a fourth table but the data query retrieves is not straight forward. It sums all the data and I can’t put it right on report.

Is there anybody who can help me here? Thank you.

 

[ColinEssex]

You should only have 2 tables -

One for customers and One for parts purchased by the customers link them with an IdNo.

Col

 

[Fuga]

I think you should consider this:

Table 1:
Fields: customerID, CustomerName, Customeradress..etc

Table 2:
Fields: CustomerID(linked), qty, parts no....etc

Fuga.



Oops, sorry col, didn´t see you.

Mansoor: Listen to Colin.

 

[Mansoor Ahmad]

Thank you very much for your reply. I still have a little confusion Let me explain my problem in a bit detail. Three tables, each for one customer and have following fields
Part No, analysis date, qty, comments.

I want to run a query such a way that I can produce a report on the following format

Month Qty
Jan 02 Qty for Customer 1 Qty for customer 2 Qty for customer 3

Feb 02 Qty for Customer 1 Qty for customer 2 Qty for customer 3

Mar 02 Qty for Customer 1 Qty for customer 2 Qty for customer 3

If I cannot do it with three table, is there any other way?

Hope to hear from you again. Thank you.

 

[ColinEssex]

You need to sort your tables out as Fuga and myself have suggested.

When you have done this your report will work. If not then post back again.

What are you going to do if you have 100 customers? - have 100 tables?

Col

 

[Mansoor Ahmad]

Colin and Fuga

Thank you very much. You have a good point. I will try to do as you advised and let you know. Thank you

 

[Mansoor Ahmad]

Colin and Fuga

You are life savers. Thank you very much for your help. Following your advice I have now put all customers in one table and is easy to compare different customers. Life is much easier and my databse is less messy now.

Thanks again.

 

[llkhoutx]

Use a Union Query, e.g.

Select * from Table1
UNION
Select * from Table2
UNION
Select * from Table3;

The fields in each table (or Select) must be identical. Add what criteria you want.