T
Tiffany
GregSky,
Thank you for all of the information. However, much of it
is over my head. The functions could probably work for
me, but I am just unable to covert them to my query and I
do not have any resorces at my company that can assist me.
I really like the idea of using the select query. Let me
give you a little more information. I am creating a
database that will calculate the amount of vacation and
employee hase earned.
I have two tables:
tblEmplInfo (primary key is EEID - employee id number)
tblServiceInfo (this has multiple row for each EEID, that
include the numbers of hours the employee worked for each
year)
Two queries:
qryEmplInfo
qryServiceInfo - I need to calculate how many vacation
hours the employee earned each year. The earning rate is
based on the total number of vacation hours they have
accumulated as of that year. That is what I need the
running total for. I created a running total in forms and
it worked, however, once I figured the amount of vacation
earned based on the running total, it would not let me
create a running total on the earned vacation.
I also have two forms where people in my department will
enter the information for the vacation calculation.
frmEmplInfo
frmServiceInfo (subform in frmEmplInfo)
I need the running total (each year) of service hours for
each employee.
I tried to use the select query that you gave me an
example of, but it is not working correctly. It adds all
of the hours for the year, for all of the employees to the
previous year's balance. It does not start over for each
employee.
Here is my select query:
SELECT tblServiceInfo.*, nz(DSum("[Service
Hours]","tblServiceInfo","[EEID]=[tblServiceInfo].[EEID]
and [Year]<=" & [tblServiceInfo].[year])) AS Cumulative
FROM tblServiceInfo;
This was the results:
EEID Year Service Hours Cumulative
1 2000 1 1
1 2001 2 12
1 2002 4 31
1 2003 5 54
2 2001 9 12
2 2002 8 31
2 2003 3 54
3 2002 7 31
3 2003 15 54
3 2004 21 75
3 2005 20 95
Here are the answers to the questions you posed to me:
--How many records will be displayed as a result? --
Thousands will be stored in the table, but less than 100
will show up in the final product, the subform and report.
--Will the query need to be updateable? --Yes, the user
will use the form to calculate vacation.
--Will the user scroll through these records? --Yes, if
there are more records than what can fit on the screen in
the subform.
--Will the user need to make changes and have the
calculations immediately update? --Yes, the user will
need to be able to make changes, but the calculations can
be updated after the records are saved.
--Will the user reorder the records, thus requiring
recalculation of the cumulative totals? --This should not
happen. The user should enter the data in year order.
--Are there groupings? Yes, I will need to be able to
retrieve the last cumulative value after each Group (eeid).
Thank you in advance for any assistance you can provide!
Thank you for all of the information. However, much of it
is over my head. The functions could probably work for
me, but I am just unable to covert them to my query and I
do not have any resorces at my company that can assist me.
I really like the idea of using the select query. Let me
give you a little more information. I am creating a
database that will calculate the amount of vacation and
employee hase earned.
I have two tables:
tblEmplInfo (primary key is EEID - employee id number)
tblServiceInfo (this has multiple row for each EEID, that
include the numbers of hours the employee worked for each
year)
Two queries:
qryEmplInfo
qryServiceInfo - I need to calculate how many vacation
hours the employee earned each year. The earning rate is
based on the total number of vacation hours they have
accumulated as of that year. That is what I need the
running total for. I created a running total in forms and
it worked, however, once I figured the amount of vacation
earned based on the running total, it would not let me
create a running total on the earned vacation.
I also have two forms where people in my department will
enter the information for the vacation calculation.
frmEmplInfo
frmServiceInfo (subform in frmEmplInfo)
I need the running total (each year) of service hours for
each employee.
I tried to use the select query that you gave me an
example of, but it is not working correctly. It adds all
of the hours for the year, for all of the employees to the
previous year's balance. It does not start over for each
employee.
Here is my select query:
SELECT tblServiceInfo.*, nz(DSum("[Service
Hours]","tblServiceInfo","[EEID]=[tblServiceInfo].[EEID]
and [Year]<=" & [tblServiceInfo].[year])) AS Cumulative
FROM tblServiceInfo;
This was the results:
EEID Year Service Hours Cumulative
1 2000 1 1
1 2001 2 12
1 2002 4 31
1 2003 5 54
2 2001 9 12
2 2002 8 31
2 2003 3 54
3 2002 7 31
3 2003 15 54
3 2004 21 75
3 2005 20 95
Here are the answers to the questions you posed to me:
--How many records will be displayed as a result? --
Thousands will be stored in the table, but less than 100
will show up in the final product, the subform and report.
--Will the query need to be updateable? --Yes, the user
will use the form to calculate vacation.
--Will the user scroll through these records? --Yes, if
there are more records than what can fit on the screen in
the subform.
--Will the user need to make changes and have the
calculations immediately update? --Yes, the user will
need to be able to make changes, but the calculations can
be updated after the records are saved.
--Will the user reorder the records, thus requiring
recalculation of the cumulative totals? --This should not
happen. The user should enter the data in year order.
--Are there groupings? Yes, I will need to be able to
retrieve the last cumulative value after each Group (eeid).
Thank you in advance for any assistance you can provide!