DCount with a Context

[Guest]

Does anyone know if you calculate the 'Domain Aggregate' functions in a given
context? If a table has a lot of employee's in it, I only want to count
records for the emp_id of whatever row I'm looking at.

I have a table with a number of columns ranging in detail from country to
employee id. The table contains 24 months of data and if a peson was
employed in a given month, a record exists for them.

In order to get a 12 rolling sum, I'd like to use DCount to count employee
id's looking back 12 months. I came up with the following:

Rolling 12 Count:
DCount("[EMPLOYEE_ID]","tblHRTrendRpting","[INT_DATA_MONTH] between " &
[INT_DATA_MONTH] & " AND " & [INT_DATA_MONTH]+11)

Where [INT_DATA_MONTH] is a number representing number of months from the
current month. For example, in February 2007, January 2007 = 1, December
2006 = 2, etc.

The DCount function works in that on each row it places a count of every
employee looking back 12 months. I would like to count within employee. For
example, if emp_id 1234 has been employed for the past 12 months as of
January 2007, I want the data to be 12 (1 employee x 12 months). Instead I'm
getting say 39,996 (3,333 employees x 12 months).

Thanks for any suggestions.

 

[Guest]

This would be very slow compared to a subquery or join to a totals query but
you could try:

DCount("[EMPLOYEE_ID]","tblHRTrendRpting","[EMPLOYEE_ID]=" & [EMPLOYEE_ID] &
" AND [INT_DATA_MONTH] between " &
[INT_DATA_MONTH] & " AND " & [INT_DATA_MONTH]+11)

This assumes your EMPLOYEE_ID is numeric.

 

[Guest]

Hi Duane,
[EMPLOYEE_ID] was text, so I added the ' and it worked perfectly. Thanks
for the help.

John

This would be very slow compared to a subquery or join to a totals query but
you could try:

DCount("[EMPLOYEE_ID]","tblHRTrendRpting","[EMPLOYEE_ID]=" & [EMPLOYEE_ID] &
" AND [INT_DATA_MONTH] between " &
[INT_DATA_MONTH] & " AND " & [INT_DATA_MONTH]+11)

This assumes your EMPLOYEE_ID is numeric.

--
Duane Hookom
Microsoft Access MVP

Does anyone know if you calculate the 'Domain Aggregate' functions in a given
context? If a table has a lot of employee's in it, I only want to count
records for the emp_id of whatever row I'm looking at.

I have a table with a number of columns ranging in detail from country to
employee id. The table contains 24 months of data and if a peson was
employed in a given month, a record exists for them.

In order to get a 12 rolling sum, I'd like to use DCount to count employee
id's looking back 12 months. I came up with the following:

Rolling 12 Count:
DCount("[EMPLOYEE_ID]","tblHRTrendRpting","[INT_DATA_MONTH] between " &
[INT_DATA_MONTH] & " AND " & [INT_DATA_MONTH]+11)

Where [INT_DATA_MONTH] is a number representing number of months from the
current month. For example, in February 2007, January 2007 = 1, December
2006 = 2, etc.

The DCount function works in that on each row it places a count of every
employee looking back 12 months. I would like to count within employee. For
example, if emp_id 1234 has been employed for the past 12 months as of
January 2007, I want the data to be 12 (1 employee x 12 months). Instead I'm
getting say 39,996 (3,333 employees x 12 months).

Thanks for any suggestions.