DATABASE DESIGN HELP

[Malhyp]

Hi there, I would like to know how I should be creating my query. I have
listed an example of the database below.

DATABASE NAME
db1

DATABASE TABLES THEN FILEDS
tblCustomer: custId, name, sirName, location, phone.
tblProduct: prodId, productName, productName2
tblCategory: catId, categoryName
tblLocation locationId, location

At the moment I have a query that pulls all this info together. The problem
I have is that each customer has say for example 6 productNames that could
mix in with any of 6 categoryNames as well.

So when I create the query it shows the following as an example for one
customer only.

query1
custId, name, sirName, location, phone, productName, productName2,
categoryName
BOB1 BOB JONES MELB 00000 PINE PINUS
FURNITURE
BOB1 BOB JONES MELB 00000 PINE PINUS
FRAMING
BOB1 BOB JONES MELB 00000 HWD HARDWOOD FURNITURE
BOB1 BOB JONES MELB 00000 PINE PINUS
HOUSING
BOB1 BOB JONES MELB 00000 HWD HARDWOOD INTERNAL
BOB1 BOB JONES MELB 00000 PINE PINUS
EXTERNAL

So for each customer it shows 6 results.

Is this the correct way to create a query?

The main problem created by this query is that when I create a MAIL MERGE
using Outlook or Word it creates 6 seperate emails because of the 6 seperate
query results.

Any help is appreciated.

Cheers
Mally.

 

[Damian S]

Hi Mally,

You don't have anything that links customers to products, for instance, you
might use a table called tblCustomerProd - CustomerProdID (autonum), CustID,
ProdID

Then you would only get products that relate to a specific customer.

Damian.

 

[Malhyp]

HI there Damian Im sorry i do have another dable that has something similar
to the following. it might be where im going wrong.

tblProd
idProduct, custId, productName, categoryName.

tblProd is linked to all the other tables. It is in this table that I select
which custId, productName, categoryName the customer wants to be listed under.

 

[Damian S]

If you have CustID in tblProd, you are saying that a product can only belong
to a single customer. Is this what you are trying to achieve?

Damian.

 

[Malhyp]

No realy, one customer has the choise of selecting which six categories and
product names. More than one customer can select the same combination.