Very close on Query Design 1

[kvest]

I am working on a query that will be used for a report that displays the following:

A user selected Job Site (tblJobSites)
A Supervisor (sometimes more than one)(tblJobSiteSupervisors)
and then all personnel assigned to that job site on the same days as the supervisor (tblParticipants).

tblParticipants tblJobSites tblJobSiteSupervisors
BookingNumber(pk) JobSiteNumber(pk) ID (pk)
Sunday (y/n) Sunday (Y/n) (etc) LastName
Monday (y/n) SupervisorNumber Sunday (Y/n)(etc)
Tuesday (y/n)(etc)
JobSiteNumber

I have all three tables in my query and can select the Job Site I want easily. The problem is that I get all the supervisors matched with all the participants. I need to limit the returns to only the participants that match the workdays of the supervisor over them. The workday (Y/N boxes) are checked for the days the supervisor or participant work. I figure this is a join problem, but dont know where to go from here.

Another glitch is that the supervisors may work M-F while some of the participants only are there on M or W or any of the other combinations.

I can post the SQL code if needed, but how do you put that inside another box.

Thanks in advance....

 

[dhookom]

Is it possible for you to normalize your table structure? I wouldn't go much further in development with field names like Sunday, Monday,...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kvest]

dhookum,
I am more than willing to change them, but didnt know of any other way to permit all of the possible combinations of workdays. I am open to suggestions.

 

[dhookom]

I don't know what your base requirements are but
[tt][blue]
tblParticipants
===============
ParticipantID
BookingNumber
JobSiteNumber

tblParticiDays
==============
ParticipantID
Wkday numeric field to store 1 for Sunday, 2 for Monday,...
[/blue][/tt]
do the same for supervisors and/or job sites. I would think you could then combine the table in a query and join the WkDay fields with others to return useful information.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kvest]

I need the ability to capture any combination of possible work days for Participants, JobSites, and Supervisors. Even though M-F will most likely by the most common, M & TH, or any other match up is possible.

I have never setup something of this type (workdays), thus this obvious misdirected effort. What a mess.

 

[dhookom]

If your previous participant table had 3 days checked as yes, this would require 3 records in tblParticiDays. This would be normalized an allow for more flexibility. If you don't understand normalizing, do a google search.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kvest]

Thanks....I will do that.

I just met with my users again and beat some correct terms out of them. What they have labeled as "Supervisor" is actually a JobSite Administrator. Each job site will only have one of them. The real "Supervisors" are not tracked as I thought they were and there is not a need to have this match up as I was trying to do.

I now have a flawed design that will still work, a new lesson to remember, and have avoided a complete redesign.

A star for your quick answers and lessons.