query that pulls specific names out of a number of fields

[oxicottin]

I want to create a query that pulls specific names out of a number of fields.
I have 10 fields labeled, Operator1,Operator2,etc.

I created a query where I used the criteria, Between[Start Date] and [Stop
Date] to only select from a specified date range. What criteria could I use
to pull say Bill Bunker's name from my 10 Operator fields?

Thanks,

Chad

 

[Jeff Boyce]

Chad

?!You have ten ("Operator") fields in which "Bill Bunker" could show up!?
This sounds more like a spreadsheet than a relational database.

If you want to make good use of what Access can do, you'll need to normalize
your table structure. If you keep your current (10 "copies" of the same
field) structure, you'll need to customize your queries, your forms, your
reports, your code and your macros to accommodate the structure (rather than
your data). And then, when the number of Operators changes, you get to go
through every single thing you've built and modify each!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Vinson]

I want to create a query that pulls specific names out of a number of fields.
I have 10 fields labeled, Operator1,Operator2,etc.

You have a spreadsheet, not a table. If you have a many (event) to
many (operator) relationship, the correct table structure would be to
have THREE tables - this table; a table of Operators; and an
OperatorAssigned table with *one* record for each operator assigned.

I created a query where I used the criteria, Between[Start Date] and [Stop
Date] to only select from a specified date range. What criteria could I use
to pull say Bill Bunker's name from my 10 Operator fields?

You'll need ten criteria, one on each of the ten fields, all on
separate lines of the query grid to use OR logic. Be sure to have the
date criterion repeated ten times as well, one on each line.

My best advice is to normalize your table design. Queries will be MUCH
easier!

John W. Vinson[MVP]