A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Query, linked tables



 
 
Thread Tools Display Modes
  #1  
Old May 12th, 2010, 10:18 AM posted to microsoft.public.access.queries
Ioia
external usenet poster
 
Posts: 16
Default Query, linked tables

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

  #2  
Old May 12th, 2010, 01:39 PM posted to microsoft.public.access.queries
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default Query, linked tables

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

  #3  
Old May 13th, 2010, 11:20 AM posted to microsoft.public.access.queries
Ioia
external usenet poster
 
Posts: 16
Default Query, linked tables

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:38 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.