How to get all the fields from both tables in a single query table (1 Viewer)

hrdpgajjar

Registered User.
Local time
Today, 21:09
Joined
Sep 24, 2019
Messages
36
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

Enthusiastic Amateur
Local time
Today, 15:39
Joined
Sep 21, 2011
Messages
9,589
Try using the Query wizard and the Find Unmatched query.
 

hrdpgajjar

Registered User.
Local time
Today, 21:09
Joined
Sep 24, 2019
Messages
36
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

..forever waiting... waiting for jellybean!
Local time
Today, 23:39
Joined
May 7, 2009
Messages
15,037
use Cartesian query?
 

June7

AWF VIP
Local time
Today, 06:39
Joined
Mar 9, 2014
Messages
3,883
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

Registered User.
Local time
Today, 21:09
Joined
Sep 24, 2019
Messages
36
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

Super Moderator
Staff member
Local time
Today, 15:39
Joined
Sep 12, 2006
Messages
14,732
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

Super Moderator
Staff member
Local time
Today, 10:39
Joined
Feb 19, 2002
Messages
34,436
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.
 

Users who are viewing this thread

Top Bottom