Combinning multiple Fields into One

M

MValentine

I have a table in which I have created the following fields:
"NewTechnician", "Tech2", and "Tech3". I also have a field called
"WorkDate". I am wanting to create a query which will select all Technicians
which have work date assigned for today ( Date() ). I am able to create a
query which will select the technicians which have work scheduled for today.
However I would like now to group the technicians from "NewTechnician",
"Tech2" and "Tech3" into one common field called "TodaysTech". Is this
possible and how would it be done?
Thanks
Michael
 
J

John Vinson

I have a table in which I have created the following fields:
"NewTechnician", "Tech2", and "Tech3".
Click to expand...

Then you have an improperly structured table. This non-normalized
structure will give you nothing but trouble! Someday you'll need FOUR
techs; or you'll want to search all three Tech fields together.
I also have a field called
"WorkDate". I am wanting to create a query which will select all Technicians
which have work date assigned for today ( Date() ). I am able to create a
query which will select the technicians which have work scheduled for today.
However I would like now to group the technicians from "NewTechnician",
"Tech2" and "Tech3" into one common field called "TodaysTech". Is this
possible and how would it be done?
Click to expand...

Normalize your data into THREE tables:

Technicians
TechID
LastName
FirstName
<other bio information>

Work
WorkID
WorkDate
Description
<other info about the task itself>

Assignments
WorkID < link to Work
TechID < link to Technicians
<other info about this tech/this job>

By searching a query joining Technicians to Assignments to Work you
can find all the techs on any given day.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Top