One Criteria, Multiple Columns - Report Issues

K

kalyhan

Hello Everyone.

I've set my query to search for one part number, over 7 columns. I used
the same search criteria (on separate lines), in each of the 7 columns which
could possibly hold the part number. This is a simple Receiving DB where the
clerk may get 7 different items under one purchase order. If the purchasing
dept. should want to see if, and when a particular part came in, they won't
be interested in
the other items that the part may have arrived with.

From my table, each of the 7 part number fields are followed by:
QTY Received
Delivered to Department
Back Order QTY (If Any)

These are identical fields except that the names are followed by a "1" or
"2" to
keep them straight.

My query seems to work fine, but I do have a question as to the report -
must the report contain all 7 fields and accompanying data (QTY, B/O), where
a part number may show up, or could my report show 1 part number column, 1
qty column, 1 backorder column, and the query results - regardless which of
the 7 columns the part number (and accompanying data) originated from?

Thanks in advance for any assistance!

Karen
 
J

Jeff Boyce

Karen

From your description, your data structure is just what you'd need to have
.... if you were working with a spreadsheet <g>! If you have to look in
multiple columns to find a value, your database would probably benefit from
further normalization before you go any further.

If you take a look at the Northwind example db that comes with Access, you
will find an Order AND an Order Detail table. This design allows an Order
to have as many (or as few) "details" (products ordered) as needed, without
using "one more column" for each.

I suspect that approach would make your task a lot simpler.

Good luck!

Jeff Boyce
<Access MVP>
 
Top