How to get all the fields from both tables in a single query table

[hrdpgajjar]

Hi,
I have two tables,
1. Customer data
2. Supply table

Now Customer data table has all my customer details, and Supply table has all material supplied details. Both table has only one filed in common is Registration No (its not primary key).

Now I want to create a query to know how many customers are there without supply. I have tried to create a relationship between two tables (with Regi.No linked) but it shows the value where both tables have entry for registration number (so the query results zero, i.e all customers had taken supply).

I want to sort out Customer data without supply (Regi. No without supply or supply pending)


How can I do that with a query?


Thanks

 

[Gasman]

Try using the Query wizard and the Find Unmatched query.

 

[hrdpgajjar]

Try using the Query wizard and the Find Unmatched query.

tried it. its not working. Actually one field "Material supply date" is not present in Customer Data table.

 

[arnelgp]

use Cartesian query?

 

[hrdpgajjar]

use Cartesian query?

sorry i dont know about it. can u help how can i use it?

 

[June7]

Find Unmatched query is correct approach if tables have relationship. What is RegistrationNo field - why is it in both tables? Provide sample data and SQL of attempted query. If you want to provide db for analysis, follow instructions at bottom of my post.

 

[hrdpgajjar]

Find Unmatched query is correct approach if tables have relationship. What is RegistrationNo field - why is it in both tables? Provide sample data and SQL of attempted query. If you want to provide db for analysis, follow instructions at bottom of my post.

Registration number is a unique number given to each case (or file). So it is present in both the tables. If I make relationship with Regi. No it gives fewer options like 1.Include only related fields in both tables 2. include all data from one table and only the connected field of other table and 3. Vice versa.

In this I am not able to get the missing fields (i.e. customer Regi no with out supply date)


Thanks

 

[gemma-the-husky]

You should be able to get all customer information for customers with no matching entry in the supply table.


However, you do need a way to relate the tables, and often you need to use a third table. The registration number sounds curious, and doesn't sound correct.

eg for Customer Orders, generally you would have
1 customer = n orders and 1 order = n ordered_items
so the customer and ordered_items are not directly related. You need the third table, in this case orders


You may have a customer_prices table to manage the prices.
So you get

1 customer = n customer_prices and
1 product = n customer_prices

So you find all the items for which you have quoted the customer using this intermediate junction table.


So now you can use a combination of queries to find
a) all the items you quoted a customer for
b) all the items they have ordered from you
c) by comparing a) and b), any quoted items they have not ordered from you.

 

[Pat Hartman]

Registration number is a unique number given to each case (or file). So it is present in both the tables.

This field does not belong in the customer table. That would imply that the customer would only ever have a single registration. There seems to be something missing from the description. Is there a Registration table somewhere with RegistrationNumber as the primary key? We don't know what a registration is so it is hard to put this data into context. There also should be a Material table. So, maybe we need to start by normalizing the tables.

If you are joining two tables and the join field is NOT the PK of one side, there is something wrong with the data.