ACCESS Query Calcs: Number

[philfreer]

Former Access user trying to recall how I would do this in a query for a nonprofit furniture bank: I have related tables (extracted from super-old PFS files) with records of individual donor pickups that include, among others, the following fields: DateOfPickUp, DOW (day of week, e.g., "Monday", "Wednesday" , etc.), Area (of Pickup, e.g., "A", "B", "C", & "D"). I'm trying to calculate, for example, on how many Mondays (or Tuesdays, Wednesdays, etc.) did we schedule pickups in Area
"A" (or "B"...). From that, I can then determine (using a count of pickups for each day of the week divided by the aggregate number of times we scheduled pickups for any one day of the week) which days are most cost-effective to operate.

[sanniep]

Former Access user trying to recall how I would do this in a query for a nonprofit furniture bank: I have related tables (extracted from super-old PFS files) with records of individual donor pickups that include, among others, the following fields: DateOfPickUp, DOW (day of week, e.g., "Monday", "Wednesday" , etc.), Area (of Pickup, e.g., "A", "B", "C", & "D"). I'm trying to calculate, for example, on how many Mondays (or Tuesdays, Wednesdays, etc.) did we schedule pickups in Area
"A" (or "B"...). From that, I can then determine (using a count of pickups for each day of the week divided by the aggregate number of times we scheduled pickups for any one day of the week) which days are most cost-effective to operate.

You could add a autonumber field to the table above then create a query with the DOP, DOW, Area and Autonumber field. Use the Group By Function and use Count on the autonumber field. Then you will have your number of pickups per day per Area.

You could aslo, if desired, make this query a crosstab query with the DOW and DOP as rowheader, the Area as Columnheader and the number of pickups (count of Autonumber) as value. This will result in the number of pickups per day with the Area as columnheader.

Sander

[philfreer]

You could add a autonumber field to the table above then create a query with the DOP, DOW, Area and Autonumber field. Use the Group By Function and use Count on the autonumber field. Then you will have your number of pickups per day per Area.

You could aslo, if desired, make this query a crosstab query with the DOW and DOP as rowheader, the Area as Columnheader and the number of pickups (count of Autonumber) as value. This will result in the number of pickups per day with the Area as columnheader.

Sander

Sander, thanks. That answered another problem I was experiencing, but I think that I didn't write up the problem correctly. What I'm trying to do is calculate how many, for example, Mondays did I shedule pickups in Area "A", not how many pickups were performed. Inotherwords, during a 1 year period, how many Mondays were spent in Areas "A", or "B", etc. regardless of the number of pickups that were performed. Again, thanks, Phil.