combining data from different queries

[Wendy]

Hi

I have 2 tables

I have one table (customers) with Customer No, Name, Address fields. I want
to link it another table (products) which has Customer No, Product No,
ProdName, Date.

I'd like to create a new table Mailingdata with Customer No, Name, Address,
Product No1, ProdName1, Product No2, ProdName2, Product No3, ProdName3, and
PurchaseDate.

However many products there are I would still only like one record per
customer in the mailingdata table at the moment I'm getting one customer
record per product . Is this possible?

Thanks

Wendy

 

[JK]

Assuming that you have CustomerID in both tables

SELECT DISTINCT tblCustomers.[Last Name], tbleCustomers.[First Name]
,....etc
FROM tblCustomers INNER JOIN tblProducts ON tblCustomer.CustomerID=
tblProducts.CustomerID
ORDER BY tblCustomers.[Last Name],tblCustomers[First Name], ... etc ... ;

Regards/JK

 

[Wendy]

Thanks JK but I wanted one record per customer irrespective of how many
products there are, your code does what I already have which is one
record per product.

Wendy

Assuming that you have CustomerID in both tables

SELECT DISTINCT tblCustomers.[Last Name], tbleCustomers.[First Name]
,....etc
FROM tblCustomers INNER JOIN tblProducts ON tblCustomer.CustomerID=
tblProducts.CustomerID
ORDER BY tblCustomers.[Last Name],tblCustomers[First Name], ... etc ... ;

Regards/JK

Hi

I have 2 tables

I have one table (customers) with Customer No, Name, Address fields. I
want
to link it another table (products) which has Customer No, Product No,
ProdName, Date.

I'd like to create a new table Mailingdata with Customer No, Name,
Address,
Product No1, ProdName1, Product No2, ProdName2, Product No3, ProdName3,
and
PurchaseDate.

However many products there are I would still only like one record per
customer in the mailingdata table at the moment I'm getting one customer
record per product . Is this possible?

Thanks

Wendy

 

[JK]

Wendy,

If you have CustomerID in both Tables *and *you have selected Fields from
the customers' table *only*, this will give you *one* record for each
customer regardless of how many Products each customer has but does not
select those customers who have no products at all.

Isn't it what you are looking for?

The only way can get more than one record per customer is:

You omitted the word "DISTINCT", or you are selecting Field from Both
tables.
If you still getting multiple customers copy your code in here and let's
have a look.

Regards/JK

Thanks JK but I wanted one record per customer irrespective of how many
products there are, your code does what I already have which is one
record per product.

Wendy

Assuming that you have CustomerID in both tables

SELECT DISTINCT tblCustomers.[Last Name], tbleCustomers.[First Name]
,....etc
FROM tblCustomers INNER JOIN tblProducts ON tblCustomer.CustomerID=
tblProducts.CustomerID
ORDER BY tblCustomers.[Last Name],tblCustomers[First Name], ... etc ...
;

Regards/JK

Hi

I have 2 tables

I have one table (customers) with Customer No, Name, Address fields. I
want
to link it another table (products) which has Customer No, Product No,
ProdName, Date.

I'd like to create a new table Mailingdata with Customer No, Name,
Address,
Product No1, ProdName1, Product No2, ProdName2, Product No3, ProdName3,
and
PurchaseDate.

However many products there are I would still only like one record per
customer in the mailingdata table at the moment I'm getting one
customer
record per product . Is this possible?

Thanks

Wendy

 

[Larry Daugherty]

Don't do it in such a way that you are copying data from one table to
another. That breaks a relational rule.

I suggest a table, tblAddress which might have addresses with many
purposes; mailing, billing, shipping, etc. This table is related,
one-to-many,to tblCustomer on the one side and tblAddress on the many
side. In the relationships window, establish Referential Integrity
and enable Cascading Deletes.

Address information is no longer needed in the tblCustomer.

HTH