Results 1 to 4 of 4

Thread: Count/Like in MS Access

  1. #1
    Join Date
    Feb 2009

    Smile Count/Like in MS Access

    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!!

  2. #2
    Join Date
    Dec 2005
    did you find an answer to this b/c I'm looking also

  3. #3
    Join Date
    Feb 2009
    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.

  4. #4
    Join Date
    Dec 2005
    thanks, it will really help

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center