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:

CrewCode Sun Mon Tue Wed Thu Fri Sat
CrewA 3 3 8 8 5 5 8
CrewB 3 3 8 8 5 5 8

How can I get it so it only counts the number of people on a particular crew for that day? It should read something like this:

CrewCode Sun Mon Tue Wed Thu Fri Sat
CrewA 3 3 3 3 0 0 3
CrewB 0 0 5 5 5 5 5

(pretend that the numbers are going under each of those days)

I'm thinking that it has to do with something along the lines of using "count" and "like", but I'm not sure.

As of now, the query is set up to look like this:

Field: CrewCode SunCrew MonCrew...
Table: CrewInfo Trainees Trainees...
Total: GroupBy Count Count...
Sort: Ascending

By the way...
  • 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!!