Complex Query! For me at least

[met]

I have a table with a name field, and 90 or so different
product numbers that are also field names. When a person
orders a certain product, a 1 is placed in that product
#'s field. Is there any way to write a query or create a
report that goes through the data and only pulls out the
product #'s that that person ordered, and not show that
Person X ordered 0 of 88 different product #'s and 3 of
product # A and 1 of product # B. I'm thinking i'll have
to write some basic code, but am not very familiar. Any
help would be appreciated. thanks, met.

 

[Ken Snell]

Your table structure sounds as if it is not normalized. You're storing data
horizontally, not vertically. As such, a query that will do what you seek
will be very complex, having to test all 90 fields in order to decide what
to display and select. Very messy.

Change your table structure to something similar to this:

tblProducts (contains IDs and names of all products)
fldProdID (primary key)
fldProdName (text field)

tblCustomers (contains IDs and names of the people)
fldCustID (primary key)
fldCustName (text field)

tblCustProd (is used to show which products a customer has ordered on
specific dates)
fldCustID (composite primary key)
fldProdID (composite primary key)
fldDateOrdered (date/time field)

Thus, the third table will have one record for each product that a customer
has ordered for each date on which the order was placed. Now a query will be
very simple to prepare (caution: this query statement is "air code - not
tested fully!"):

SELECT fldCustName, fldProdName, fldDateOrdered
FROM tblCustomers INNER JOIN (tblProducts INNER JOIN tblCustProd ON
tblProducts.fldProdID = tblCustProd.fldProdID) ON tblCustomers.fldCustID =
tblCustProd.fldCustID;