Need Help!

[Guest]

I have a query that I want to do a count of people from various areas. The
table that the query is being created from is a data entry table. It has the
following fields: ID, Training code, Date of training, area (dept.). This
info is entered every week. A given associate could have numerous records.
Every month I need to run a report to see how many associates from each area
have taken training. The problem is that when I do a count, It counts the
same person more than once (depending on how many times they have taken
training). What I have now groups by area, and counts by ID. This gives me:
Area 1 - 25......when it should be Area 1 -11. Anyone have any ideas?

 

[Allen Browne]

You could do this several ways: a) stacking one query on another, b) by
using a subquery, c) by using a report, or d) by writing a function.

1. Create a query using your training table.

2. Drag the Area field into the grid.

3. Drag the EmployeeID field into the grid.

4. Type this expression into the Field row in the next column:
TrainingYear:Year([Date of Training)

5. In the Field row in the next column, enter:
TrainingMonthMonth([Date of Training])

6. Depress the Total button on the toolbar.
Access adds a Total row to the grid, and shows Group By under each field.

7. (Optional) If you want to limit this to a particular period, andd the
[Date of Training] field to the grid in the next column. In the Total row
under this field, choose Where. Enter your criteria under this.

8. Test: in each area you should see one row for each person each month.
Save the query. Close.

9. Create another query, using the one you just created as an input "table."
You can now group by the area, year, and month, and get a count of
employees.

 

[Guest]

Allen,

Thanks for your response! Going by what you have told me, I think I got it!
Will this allow me to count an associate only once even though they could be
entered into the database more than once in a given month? Thanks!

You could do this several ways: a) stacking one query on another, b) by
using a subquery, c) by using a report, or d) by writing a function.

1. Create a query using your training table.

2. Drag the Area field into the grid.

3. Drag the EmployeeID field into the grid.

4. Type this expression into the Field row in the next column:
TrainingYear:Year([Date of Training)

5. In the Field row in the next column, enter:
TrainingMonthMonth([Date of Training])

6. Depress the Total button on the toolbar.
Access adds a Total row to the grid, and shows Group By under each field.

7. (Optional) If you want to limit this to a particular period, andd the
[Date of Training] field to the grid in the next column. In the Total row
under this field, choose Where. Enter your criteria under this.

8. Test: in each area you should see one row for each person each month.
Save the query. Close.

9. Create another query, using the one you just created as an input "table."
You can now group by the area, year, and month, and get a count of
employees.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a query that I want to do a count of people from various areas. The
table that the query is being created from is a data entry table. It has
the
following fields: ID, Training code, Date of training, area (dept.). This
info is entered every week. A given associate could have numerous records.
Every month I need to run a report to see how many associates from each
area
have taken training. The problem is that when I do a count, It counts the
same person more than once (depending on how many times they have taken
training). What I have now groups by area, and counts by ID. This gives
me:
Area 1 - 25......when it should be Area 1 -11. Anyone have any ideas?

 

[Guest]

Allen,

Never mind................It worked like a charm! You are the man!

Thanks!

You could do this several ways: a) stacking one query on another, b) by
using a subquery, c) by using a report, or d) by writing a function.

1. Create a query using your training table.

2. Drag the Area field into the grid.

3. Drag the EmployeeID field into the grid.

4. Type this expression into the Field row in the next column:
TrainingYear:Year([Date of Training)

5. In the Field row in the next column, enter:
TrainingMonthMonth([Date of Training])

6. Depress the Total button on the toolbar.
Access adds a Total row to the grid, and shows Group By under each field.

7. (Optional) If you want to limit this to a particular period, andd the
[Date of Training] field to the grid in the next column. In the Total row
under this field, choose Where. Enter your criteria under this.

8. Test: in each area you should see one row for each person each month.
Save the query. Close.

9. Create another query, using the one you just created as an input "table."
You can now group by the area, year, and month, and get a count of
employees.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a query that I want to do a count of people from various areas. The
table that the query is being created from is a data entry table. It has
the
following fields: ID, Training code, Date of training, area (dept.). This
info is entered every week. A given associate could have numerous records.
Every month I need to run a report to see how many associates from each
area
have taken training. The problem is that when I do a count, It counts the
same person more than once (depending on how many times they have taken
training). What I have now groups by area, and counts by ID. This gives
me:
Area 1 - 25......when it should be Area 1 -11. Anyone have any ideas?