Query, linked tables

[Ioia]

I have a table called tblPA where all personal data of Personal Assistants
(PA) is recorded, being PaID the primary key. The second table linked by PaID
as foreign key, is called tblavailabilitytowork, it has a y/n field for each
time frame they can work, e.g.: on MondayAM, MondayPM, MondayOvernight, etc.
When a client asks for a PA for certain hours of work, let’s say e.g.
Tuesdays and Wednesdays PM, I should be able to create a query with the list
of PAs that are able to work in such time frame. How can I do it?
Thank you
Ioia

 

[Jerry Whittle]

You will need to create a large, slow Union query that has a select statement
for each of the Y/N fields. Something like:

Select PaID, "YesMondayAM" As TheShift
From tblavailabilitytowork
Where MondayAM = Yes
UNION
Select PaID, "YesMondayPM"
From tblavailabilitytowork
Where MondayPM = Yes
UNION
Select PaID, "YesMondayOvernight"
From tblavailabilitytowork
Where MondayOvernight = Yes
UNION
And so on for each Y/N field in the table.

You can then save the above query and then join it to the tblPA table on the
PaID field. You can then find out what PAs can work by putting criteria in
TheShift field. You’ll need to look for YesMondayAM, YesMondayPM, etc.
Probably an In statement would work best.

Of course if you add any shifts, such as ChristmasPM, you’ll need to redo
the rather ponderous Union query AND the tblavailabilitytowork table plus any
forms or reports based on that query and table.

OR

You can rebuild the tblavailabilitytowork properly. Instead of across like a
spreadsheet, you should be going down like database table.

Example:

ATW_ID (primary key autonumber field)
PaID (foreign key field to tblPA
Shift (text field)

ATW_ID PaID Shift
1 1 MondayAM
2 1 MondayPM
And so on. Now your query is as simple as:

Select PA, Shift
From tblPA , tblavailabilitytowork
Where tblPA.PaID = tblavailabilitytowork.PaID
And Shift in("MondayAM", "TuesdayAM", "FridayAM")
Order by PA;

You might want to create a lookup table of Shifts so that someone doesn’t
type FrydayPM or something incorrect. Some would say that the Shifts should
be their own table and linked with a foreign key, but I’d denormalize that.

 

[Ioia]

Thank you very much, I've tried the first option and it works fine.
Ioia