I'm designing a crew list, which shows which trainees are on which crews.
I have two tables: CrewInfo and Trainees.
CrewInfo: info about the crew, including what days it has off (in the table, I have check-box columns for each day of the week, so I just check off the respective columns for that particular crew's days off)
Trainees: info about each of the trainees, including what crew(s) each trainee is on (in the table, I have drop-down columns for each day of the week, so the user chooses the crew that a trainee is on for that day. To prevent a trainee from being assigned to a crew that is off, I said that the crew names in the selection are only those where crewinfo.[CheckboxForWhateverDayOff]=No )
I'm trying to create a query that has the CrewCode (unique for each crew) and the number of people on a crew for each day. Obviously, crews that are off on a particular day should have 0 for that day.
I put in about 8 trainees and assigned them to two different crews (3 on one, 5 on the other).
The problem: All the crews show up on the query, but they all (even those without anyone assigned to that crew) have the same numbers for each day, those numbers being the total number of trainees working that day. So a record for a crew looks something like this:
I have a query like this set for each day, and it works just fine (it only lists the crews that have people assigned to them*). In each of those queries, I have CrewCode linked to the name of the column in Trainees for that particular day. *that's fine for those queries, but for this one, I want all crews listed, regardless if trainees are assigned to them or not.
I don't have any relationships established in this query that I'm trying to do. When I do establish a relationship (like CrewInfo -> TuesCrew), it only shows the crews that have trainees assigned to it for that day. If I change the relationship to have one-to-many (like CrewInfo -> TuesCrew and CrewInfo -> WedCrew), it'll only show crews that have people assigned to it for Tues AND Wed. If a crew is off for one of those days, it's not shown at all.
I'm sorry for being so verbose, and I appreciate any help that you may give! Thanks!!
A work colleague was able to figure it out the other day, but we haven't had a chance to meet so he can explain to me how he did it. (I looked at the SQL of it but didn't understand it fully.) When we do meet, I'll try to remember to put the answer here.