Overview: to have variable rates for generating a report in Access 97
Detail:
I am designing a timesheet organisation database, whereby users can enter their timesheets for the week etc. in one FE, and then the administrators can review the timesheets, accept/reject them, and produce reports using the data from the submitted timesheets in another FE. ie.
|User FE| --> |datastore| <-- |Admin FE|
The problem I’ve reached is with the rate of pay for each staff member. As historical reports need to be able to be produced, then a table of staff rates and their applicable dates.
(RateID, StaffID, Rate, start date, end date)
And has the relationship :
|Rate| >--- |Personnel| ---< |TimeSheet|
where ---< indicated one-to-many
There are physically 8 different reports that can be run (the user will see as 21 different reports). All reports have a format similar to:-
Name, Agency, Discipline, Date, WeekNo, Year, Total, Cost
Whereby cost is generated using the staff member’s rate and multiplying it by the number of hours worked.
The Rate needs do be found by comparing the date of the timesheet, and finding which rate of pay was applicable for that timeframe. I have been able to produce this on a single form using a combobox that runs a query where the start date criteria is <=([forms]![#test1].[date]) and end date criteria is >=([forms]![#test1].[date])
However, this cannot be reproduced with either continuous forms or a report as the first result from the combobox is replicated throughout the report.
One thought that I had was to create a table using the data from the form, but I do not know how to do this as I have had no experience with make table queries, therefore cannot see a straight forward way of doing this.
If this cannot be completed in Access97, but can in access XP, then I will persuade the company that 2 copies of XP will be required (as only 2 people will be using the admin FE)
Any help with is matter will be greatly appreciated, and if any more detail, or a cut down copy of the database is required, then please just post a reply.
Regards
Samulayo
Detail:
I am designing a timesheet organisation database, whereby users can enter their timesheets for the week etc. in one FE, and then the administrators can review the timesheets, accept/reject them, and produce reports using the data from the submitted timesheets in another FE. ie.
|User FE| --> |datastore| <-- |Admin FE|
The problem I’ve reached is with the rate of pay for each staff member. As historical reports need to be able to be produced, then a table of staff rates and their applicable dates.
(RateID, StaffID, Rate, start date, end date)
And has the relationship :
|Rate| >--- |Personnel| ---< |TimeSheet|
where ---< indicated one-to-many
There are physically 8 different reports that can be run (the user will see as 21 different reports). All reports have a format similar to:-
Name, Agency, Discipline, Date, WeekNo, Year, Total, Cost
Whereby cost is generated using the staff member’s rate and multiplying it by the number of hours worked.
The Rate needs do be found by comparing the date of the timesheet, and finding which rate of pay was applicable for that timeframe. I have been able to produce this on a single form using a combobox that runs a query where the start date criteria is <=([forms]![#test1].[date]) and end date criteria is >=([forms]![#test1].[date])
However, this cannot be reproduced with either continuous forms or a report as the first result from the combobox is replicated throughout the report.
One thought that I had was to create a table using the data from the form, but I do not know how to do this as I have had no experience with make table queries, therefore cannot see a straight forward way of doing this.
If this cannot be completed in Access97, but can in access XP, then I will persuade the company that 2 copies of XP will be required (as only 2 people will be using the admin FE)
Any help with is matter will be greatly appreciated, and if any more detail, or a cut down copy of the database is required, then please just post a reply.
Regards
Samulayo
