By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,704 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

ACCESS Query Calcs: Number

P: 2
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.
Oct 10 '06 #1
Share this Question
Share on Google+
2 Replies


P: 48
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
Oct 11 '06 #2

P: 2
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.
Oct 11 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.