One to many problem

  • Thread starter Thread starter Wendy
  • Start date Start date
W

Wendy

Hi

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, Date.

I need to link them so that all products will appear for the same customer
in one record for mailing, at the moment I get one record per product which
I can't use for my mailmerge.

Thanks

Wendy

SELECT Customers.CustNo AS [Customer No], Customers.custname AS Customer,
Customers.Add1 AS [Address Line 1], Products.ProdNo AS [Product No],
Products.Descript AS Product
FROM Customers INNER JOIN Products ON (Customers.LocNo = Products.LocNo) AND
(Customers.CustNo = Products.CustNo)
WITH OWNERACCESS OPTION;
 
Wendy,

This would probably be much easier for you if you did this as a report.
In a report, you'll be able to set the customer as a heading and list
all the product information in the detail section of the report. You
can put your address info where you need it too. If you wish to do
something like a form letter, you can easily do that as well. In that
case, you would simply put your product info in a subreport to be used
on the main report.

I hope that gives you a little direction at least.
 
Thanks Jeff but I need to produce a second letter in some cases, which was
why a 2 page word document was the preferred option. If by form letters
you mean those used in word mailmerge then that would be ideal but I don't
know how to use a report as a mailmerge data source.

Wendy



Jeff L said:
Wendy,

This would probably be much easier for you if you did this as a report.
In a report, you'll be able to set the customer as a heading and list
all the product information in the detail section of the report. You
can put your address info where you need it too. If you wish to do
something like a form letter, you can easily do that as well. In that
case, you would simply put your product info in a subreport to be used
on the main report.

I hope that gives you a little direction at least.


Hi

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, Date.

I need to link them so that all products will appear for the same customer
in one record for mailing, at the moment I get one record per product which
I can't use for my mailmerge.

Thanks

Wendy

SELECT Customers.CustNo AS [Customer No], Customers.custname AS Customer,
Customers.Add1 AS [Address Line 1], Products.ProdNo AS [Product No],
Products.Descript AS Product
FROM Customers INNER JOIN Products ON (Customers.LocNo = Products.LocNo) AND
(Customers.CustNo = Products.CustNo)
WITH OWNERACCESS OPTION;
Click to expand...
Click to expand...
 
Back
Top