RE: DATABASE DESIGN HELP

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" wrote:

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

Relevant Pages

  • Re: Trouble with sendkeys - Alternative?
    ... One customer can be involved in many sales (but not ... You can then create a query based on your three tables. ... If you want to include the sum of the "Value" columns in a crosstab query, ... Graham Mandeno ...
    (microsoft.public.access.formscoding)
  • Re: Combo list generated by query.
    ... Open a new query in design mode. ... Select your trelCust_has_Proc table, the ID field, and the CustID field. ... customer -- the first field needs to be the CustID.) ... you need a table that lists one row for each valid CustomerID X ...
    (microsoft.public.access.queries)
  • Re: Crosstab query by quarter nightmare
    ... The Cartesian query gives every combination of customer and savings in the ... I am getting the same count for each savings. ... So you are recording when a customer came on board. ... So the whole crosstab relies ...
    (microsoft.public.access.queries)
  • Re: [PHP] OOP slow -- am I an idiot?
    ... OOP has overhead. ... That saves you putting wasteful SQL queries in your ... If you need to do a complex query with a couple of joins and such, ... I want to create a "customer" class which fetches its attributes from a ...
    (php.general)
  • Re: list box combining
    ... You could try using a query similar to this to populate your listbox. ... Group By CustId, CustName ... > the list box that both lead to the same customer when clicked. ...
    (microsoft.public.access.forms)
Loading