Multiple field query

[prodtest]

I have an Access database that stores serial numbers in various fields:

Chassis Card1 Card2 Card3 etc
------- ----- ----- -----
68723 89546 56746 87422
68974 55478 46954 23791
68028 12489 45972 41976
etc

The chassis field is the primary key and unit serial number, and the card fields are serial numbers of what is fitted to the chassis.
I need to be able to find a record based on a serial number which maybe stored in any of the fields. So I could enter 46954 and find what serial numbers where associated with it and then enter in 12489 and do the same.
Is it possible to do this in one query rather than using a seperate one for each field?
Cheers

 

[PHV]

The hard way is a query with a LOT of OR operators in the WHERE clause.
The standard way is to normalze your database schema:

http://www.abcdataworks.com/fundamentals.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

if you prefer not to open Word docs, you can read the exact same article on a web page here --

http://r937.com/relational.html

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)