Prev: subtotal in access?
Next: The not "Max" records
From: Ioia on 12 May 2010 05:18
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

From: Jerry Whittle on 12 May 2010 08:39
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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Ioia" wrote:

> 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
>
From: Ioia on 13 May 2010 06:20
Thank you very much, I've tried the first option and it works fine.
Ioia

"Jerry Whittle" wrote:

> 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.
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "Ioia" wrote:
>
> > 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
> >
 | 
Pages: 1
Prev: subtotal in access?
Next: The not "Max" records