The query is too complex or can't open anymore databases

[Anne]

I never thought that there was a limitation to the fields or calculations in
a query. Now I get this message and I know I need to change what I
something.
FieldName: Wed
WedSum: (Select Sum(nz([wed],0)) from [tblHoursbyDay] where [EmployeeId]
=a.EmployeeID and [TransID] <=a.TransID)
MaxWed: DMax("Wedsum","7QryOvertime","employeenew=" & [employeenew])
WedTotal: (Select Sum(nz([wed],0)) from [tblHoursbyDay] where [EmployeeId]
=a.EmployeeID)
Besides those fields, I have about 10 more fields identifying the employee,
the foremen, where they work, the job number.
Each week I have about 500 lines of data for about 130 employees, because
each employee works on several jobs.

I am running these 3 parameters for each day of the week.
This is to determine, when overtime start.
The week starts Wednesday, and usually by Saturday (if they work) it could
be that the employee's hours run into overtime. But some employees, work on
6 to 8 jobs a day. That is where all these transactions come from and.

After that I have to run a year to date query to calculate how much was
earned so far to calculate the maximum for social security, federal
unemployment and Florida unemployment. After that query, when I put the 2
queries together, then add the regular hours and overtime hourse, and
calculate the burdin, that is when it bombs.

It would prefer to to have to go to make a table, because there are printout
where one can check data and it would be a big problem to keep recreating
the table after every change.

Before I decided to let access calculate where overtime starts, everything
was working well. It would still be working well and I figured I can just
tie into the previous queries, if I didn't run into this limitation of space
or whatever?

Is there a more effient way of calculating where the overtime starts, so I
wouldn't have this problem. Microsoft knowledgebase, says the problem was
solved in access 7. What is Access 7, I use Access 2003?

Any help would be appreciated. I am filling in as payroll person for one of
my clients who is using my program, but doing the entries myself now, I
wanted to make things easier and not have to have to person inputting the
data make the decision as to when overtime starts. Looks like tomorrow I
will still have to use the old program.
Anne

 

[Allen Browne]

Hi Anne. The core of this problem is that the data structure is wrong.

Instead of repeating fields named Mon, Tue, Wed, ... you need a structure
that lets you record the number of hours into one field, e.g.:
EmployeeID who worked
StartDateTime date and time when the person started.
Hours number of hours worked (double)
This would let you query, group by date, crosstab to display your current
view, sum between dates, and so on. You would not need to run multiple
subqueries to get the results.

Presumably what you are wanting to do is sum the hours for each worker in
the pay period, and pay some at the normal rate, and the extras at the
overtime rate.

With the suggested structure, you will finish up with many more records each
week, but a much simpler set of calculations, and the issue of running into
the limit of opening more tables or databases does not arise.

Sometimes the 'too complex' message indicates Access does not understand the
calculated field (e.g. its data type), but in your case it may be that the
entire query is too complex with the levels of additional subquerying you
are performing because of the bad structure.

Access 7 was known as Access 95. Access 2003 is called version 11, as you
can see if you choose About on the Help menu. Microsoft did extend the
number of databases you could open from 1024 to 2048 in Access 97, I
believe, so that's probably what you were reading about.

HTH

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

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

I never thought that there was a limitation to the fields or calculations
in a query. Now I get this message and I know I need to change what I
something.
FieldName: Wed
WedSum: (Select Sum(nz([wed],0)) from [tblHoursbyDay] where [EmployeeId]
=a.EmployeeID and [TransID] <=a.TransID)
MaxWed: DMax("Wedsum","7QryOvertime","employeenew=" & [employeenew])
WedTotal: (Select Sum(nz([wed],0)) from [tblHoursbyDay] where
[EmployeeId] =a.EmployeeID)
Besides those fields, I have about 10 more fields identifying the
employee, the foremen, where they work, the job number.
Each week I have about 500 lines of data for about 130 employees, because
each employee works on several jobs.

I am running these 3 parameters for each day of the week.
This is to determine, when overtime start.
The week starts Wednesday, and usually by Saturday (if they work) it could
be that the employee's hours run into overtime. But some employees, work
on 6 to 8 jobs a day. That is where all these transactions come from and.

After that I have to run a year to date query to calculate how much was
earned so far to calculate the maximum for social security, federal
unemployment and Florida unemployment. After that query, when I put the 2
queries together, then add the regular hours and overtime hourse, and
calculate the burdin, that is when it bombs.

It would prefer to to have to go to make a table, because there are
printout where one can check data and it would be a big problem to keep
recreating the table after every change.

Before I decided to let access calculate where overtime starts, everything
was working well. It would still be working well and I figured I can just
tie into the previous queries, if I didn't run into this limitation of
space or whatever?

Is there a more effient way of calculating where the overtime starts, so I
wouldn't have this problem. Microsoft knowledgebase, says the problem was
solved in access 7. What is Access 7, I use Access 2003?

Any help would be appreciated. I am filling in as payroll person for one
of my clients who is using my program, but doing the entries myself now, I
wanted to make things easier and not have to have to person inputting the
data make the decision as to when overtime starts. Looks like tomorrow I
will still have to use the old program.
Anne

 

[Anne]

Yes, I am already thinking about that and I need some help in how to go
about it.
Right now the form is structured this way:
The user selects the employee from a combobox
The form has a default date field (tuesday of current week), which is the
weekending date.
The payrollItem is a combo for PayrollItemID where the default is "hours"
Next field is a combo for the JobID. Now follow the days of the week.

Data entry is very simple and fast, all the user has to enter for the Hourly
work, is the job number and the hours per day.
I could probably calculate backwards to arrive a the actual date for each
day of the week with dateadd?
Perhaps I can do a union query to arrive at a normal table structure?

Of course setting up the original table the correct way would be best, I am
I trying to have as little keystrokes as possible.
Current Table Structure:
TransID
ForemanID
EmployeeID
PayrollItemID
JobsID
the 7 days of the week
Weekdefault- default value =1 (used for linking)
Rate1 (misc $ earnings field earnings)
Retro Hours
CorrectionWeek (date for retropay)
Memo
WeDate: Weekending date

Any feedback is very much appreciated.
Thanks,
Anne
PS: This table is not a permanent table, it gets deleted every week after
the calculated data gets updated to the Year to date table.

Hi Anne. The core of this problem is that the data structure is wrong.

Instead of repeating fields named Mon, Tue, Wed, ... you need a structure
that lets you record the number of hours into one field, e.g.:
EmployeeID who worked
StartDateTime date and time when the person started.
Hours number of hours worked (double)
This would let you query, group by date, crosstab to display your current
view, sum between dates, and so on. You would not need to run multiple
subqueries to get the results.

Presumably what you are wanting to do is sum the hours for each worker in
the pay period, and pay some at the normal rate, and the extras at the
overtime rate.

With the suggested structure, you will finish up with many more records
each week, but a much simpler set of calculations, and the issue of
running into the limit of opening more tables or databases does not arise.

Sometimes the 'too complex' message indicates Access does not understand
the calculated field (e.g. its data type), but in your case it may be that
the entire query is too complex with the levels of additional subquerying
you are performing because of the bad structure.

Access 7 was known as Access 95. Access 2003 is called version 11, as you
can see if you choose About on the Help menu. Microsoft did extend the
number of databases you could open from 1024 to 2048 in Access 97, I
believe, so that's probably what you were reading about.

HTH

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

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

I never thought that there was a limitation to the fields or calculations
in a query. Now I get this message and I know I need to change what I
something.
FieldName: Wed
WedSum: (Select Sum(nz([wed],0)) from [tblHoursbyDay] where
[EmployeeId] =a.EmployeeID and [TransID] <=a.TransID)
MaxWed: DMax("Wedsum","7QryOvertime","employeenew=" & [employeenew])
WedTotal: (Select Sum(nz([wed],0)) from [tblHoursbyDay] where
[EmployeeId] =a.EmployeeID)
Besides those fields, I have about 10 more fields identifying the
employee, the foremen, where they work, the job number.
Each week I have about 500 lines of data for about 130 employees, because
each employee works on several jobs.

I am running these 3 parameters for each day of the week.
This is to determine, when overtime start.
The week starts Wednesday, and usually by Saturday (if they work) it
could be that the employee's hours run into overtime. But some employees,
work on 6 to 8 jobs a day. That is where all these transactions come from
and.

After that I have to run a year to date query to calculate how much was
earned so far to calculate the maximum for social security, federal
unemployment and Florida unemployment. After that query, when I put the 2
queries together, then add the regular hours and overtime hourse, and
calculate the burdin, that is when it bombs.

It would prefer to to have to go to make a table, because there are
printout where one can check data and it would be a big problem to keep
recreating the table after every change.

Before I decided to let access calculate where overtime starts,
everything was working well. It would still be working well and I figured
I can just tie into the previous queries, if I didn't run into this
limitation of space or whatever?

Is there a more effient way of calculating where the overtime starts, so
I wouldn't have this problem. Microsoft knowledgebase, says the problem
was solved in access 7. What is Access 7, I use Access 2003?

Any help would be appreciated. I am filling in as payroll person for one
of my clients who is using my program, but doing the entries myself now,
I wanted to make things easier and not have to have to person inputting
the data make the decision as to when overtime starts. Looks like
tomorrow I will still have to use the old program.
Anne

 

[Allen Browne]

Once you get the structure right, you can work on reducing the keystrokes
for the user.

One way to do that would be to execute an append query statement to add a
record for each employee + each date of the current pay period, showing zero
hours, the employee's normal foreman, and whatever else you can guess at.
This means the data entry person has just to replace the zero with the
hours.

But whatever the interface, the right data structure is crucial. (Sounds
like you understand the significance of that.)

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

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

Yes, I am already thinking about that and I need some help in how to go
about it.
Right now the form is structured this way:
The user selects the employee from a combobox
The form has a default date field (tuesday of current week), which is the
weekending date.
The payrollItem is a combo for PayrollItemID where the default is "hours"
Next field is a combo for the JobID. Now follow the days of the week.

Data entry is very simple and fast, all the user has to enter for the
Hourly work, is the job number and the hours per day.
I could probably calculate backwards to arrive a the actual date for each
day of the week with dateadd?
Perhaps I can do a union query to arrive at a normal table structure?

Of course setting up the original table the correct way would be best, I
am I trying to have as little keystrokes as possible.
Current Table Structure:
TransID
ForemanID
EmployeeID
PayrollItemID
JobsID
the 7 days of the week
Weekdefault- default value =1 (used for linking)
Rate1 (misc $ earnings field earnings)
Retro Hours
CorrectionWeek (date for retropay)
Memo
WeDate: Weekending date

Any feedback is very much appreciated.
Thanks,
Anne
PS: This table is not a permanent table, it gets deleted every week after
the calculated data gets updated to the Year to date table.

Hi Anne. The core of this problem is that the data structure is wrong.

Instead of repeating fields named Mon, Tue, Wed, ... you need a structure
that lets you record the number of hours into one field, e.g.:
EmployeeID who worked
StartDateTime date and time when the person started.
Hours number of hours worked (double)
This would let you query, group by date, crosstab to display your current
view, sum between dates, and so on. You would not need to run multiple
subqueries to get the results.

Presumably what you are wanting to do is sum the hours for each worker in
the pay period, and pay some at the normal rate, and the extras at the
overtime rate.

With the suggested structure, you will finish up with many more records
each week, but a much simpler set of calculations, and the issue of
running into the limit of opening more tables or databases does not
arise.

Sometimes the 'too complex' message indicates Access does not understand
the calculated field (e.g. its data type), but in your case it may be
that the entire query is too complex with the levels of additional
subquerying you are performing because of the bad structure.

Access 7 was known as Access 95. Access 2003 is called version 11, as you
can see if you choose About on the Help menu. Microsoft did extend the
number of databases you could open from 1024 to 2048 in Access 97, I
believe, so that's probably what you were reading about.

HTH

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

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

I never thought that there was a limitation to the fields or calculations
in a query. Now I get this message and I know I need to change what I
something.
FieldName: Wed
WedSum: (Select Sum(nz([wed],0)) from [tblHoursbyDay] where
[EmployeeId] =a.EmployeeID and [TransID] <=a.TransID)
MaxWed: DMax("Wedsum","7QryOvertime","employeenew=" & [employeenew])
WedTotal: (Select Sum(nz([wed],0)) from [tblHoursbyDay] where
[EmployeeId] =a.EmployeeID)
Besides those fields, I have about 10 more fields identifying the
employee, the foremen, where they work, the job number.
Each week I have about 500 lines of data for about 130 employees,
because each employee works on several jobs.

I am running these 3 parameters for each day of the week.
This is to determine, when overtime start.
The week starts Wednesday, and usually by Saturday (if they work) it
could be that the employee's hours run into overtime. But some
employees, work on 6 to 8 jobs a day. That is where all these
transactions come from and.

After that I have to run a year to date query to calculate how much was
earned so far to calculate the maximum for social security, federal
unemployment and Florida unemployment. After that query, when I put the
2 queries together, then add the regular hours and overtime hourse, and
calculate the burdin, that is when it bombs.

It would prefer to to have to go to make a table, because there are
printout where one can check data and it would be a big problem to keep
recreating the table after every change.

Before I decided to let access calculate where overtime starts,
everything was working well. It would still be working well and I
figured I can just tie into the previous queries, if I didn't run into
this limitation of space or whatever?

Is there a more effient way of calculating where the overtime starts, so
I wouldn't have this problem. Microsoft knowledgebase, says the problem
was solved in access 7. What is Access 7, I use Access 2003?

Any help would be appreciated. I am filling in as payroll person for one
of my clients who is using my program, but doing the entries myself now,
I wanted to make things easier and not have to have to person inputting
the data make the decision as to when overtime starts. Looks like
tomorrow I will still have to use the old program.
Anne

 

[Anne]

Allen,
I don't think that would work because of the inconsistancy of the number of
jobs per day.
I am thinking about 7 queries, one for each day of the week, which will
convert the day of the week to a date using the week ending date. Then union
all seven dates.
Will that get me out of trouble with the running out of space?
Anne

Once you get the structure right, you can work on reducing the keystrokes
for the user.

One way to do that would be to execute an append query statement to add a
record for each employee + each date of the current pay period, showing
zero hours, the employee's normal foreman, and whatever else you can guess
at. This means the data entry person has just to replace the zero with the
hours.

But whatever the interface, the right data structure is crucial. (Sounds
like you understand the significance of that.)

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

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

Yes, I am already thinking about that and I need some help in how to go
about it.
Right now the form is structured this way:
The user selects the employee from a combobox
The form has a default date field (tuesday of current week), which is
the weekending date.
The payrollItem is a combo for PayrollItemID where the default is "hours"
Next field is a combo for the JobID. Now follow the days of the week.

Data entry is very simple and fast, all the user has to enter for the
Hourly work, is the job number and the hours per day.
I could probably calculate backwards to arrive a the actual date for each
day of the week with dateadd?
Perhaps I can do a union query to arrive at a normal table structure?

Of course setting up the original table the correct way would be best, I
am I trying to have as little keystrokes as possible.
Current Table Structure:
TransID
ForemanID
EmployeeID
PayrollItemID
JobsID
the 7 days of the week
Weekdefault- default value =1 (used for linking)
Rate1 (misc $ earnings field earnings)
Retro Hours
CorrectionWeek (date for retropay)
Memo
WeDate: Weekending date

Any feedback is very much appreciated.
Thanks,
Anne
PS: This table is not a permanent table, it gets deleted every week after
the calculated data gets updated to the Year to date table.

Hi Anne. The core of this problem is that the data structure is wrong.

Instead of repeating fields named Mon, Tue, Wed, ... you need a
structure that lets you record the number of hours into one field, e.g.:
EmployeeID who worked
StartDateTime date and time when the person started.
Hours number of hours worked (double)
This would let you query, group by date, crosstab to display your
current view, sum between dates, and so on. You would not need to run
multiple subqueries to get the results.

Presumably what you are wanting to do is sum the hours for each worker
in the pay period, and pay some at the normal rate, and the extras at
the overtime rate.

With the suggested structure, you will finish up with many more records
each week, but a much simpler set of calculations, and the issue of
running into the limit of opening more tables or databases does not
arise.

Sometimes the 'too complex' message indicates Access does not understand
the calculated field (e.g. its data type), but in your case it may be
that the entire query is too complex with the levels of additional
subquerying you are performing because of the bad structure.

Access 7 was known as Access 95. Access 2003 is called version 11, as
you can see if you choose About on the Help menu. Microsoft did extend
the number of databases you could open from 1024 to 2048 in Access 97, I
believe, so that's probably what you were reading about.

HTH

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

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

I never thought that there was a limitation to the fields or
calculations in a query. Now I get this message and I know I need to
change what I something.
FieldName: Wed
WedSum: (Select Sum(nz([wed],0)) from [tblHoursbyDay] where
[EmployeeId] =a.EmployeeID and [TransID] <=a.TransID)
MaxWed: DMax("Wedsum","7QryOvertime","employeenew=" & [employeenew])
WedTotal: (Select Sum(nz([wed],0)) from [tblHoursbyDay] where
[EmployeeId] =a.EmployeeID)
Besides those fields, I have about 10 more fields identifying the
employee, the foremen, where they work, the job number.
Each week I have about 500 lines of data for about 130 employees,
because each employee works on several jobs.

I am running these 3 parameters for each day of the week.
This is to determine, when overtime start.
The week starts Wednesday, and usually by Saturday (if they work) it
could be that the employee's hours run into overtime. But some
employees, work on 6 to 8 jobs a day. That is where all these
transactions come from and.

After that I have to run a year to date query to calculate how much was
earned so far to calculate the maximum for social security, federal
unemployment and Florida unemployment. After that query, when I put the
2 queries together, then add the regular hours and overtime hourse, and
calculate the burdin, that is when it bombs.

It would prefer to to have to go to make a table, because there are
printout where one can check data and it would be a big problem to keep
recreating the table after every change.

Before I decided to let access calculate where overtime starts,
everything was working well. It would still be working well and I
figured I can just tie into the previous queries, if I didn't run into
this limitation of space or whatever?

Is there a more effient way of calculating where the overtime starts,
so I wouldn't have this problem. Microsoft knowledgebase, says the
problem was solved in access 7. What is Access 7, I use Access 2003?

Any help would be appreciated. I am filling in as payroll person for
one of my clients who is using my program, but doing the entries myself
now, I wanted to make things easier and not have to have to person
inputting the data make the decision as to when overtime starts. Looks
like tomorrow I will still have to use the old program.
Anne

 

[Allen Browne]

That sounds terribly inefficient, and yes it will drain resources.

What's the problem with the number of jobs per day?
You can have one record for every time a person logs on if you wish.
For example, if Homer Simpson logs on at 12:01am, and works until 7:00am,
then logs in again at 19:00 and works until 23:00, it copes. Or if Homer
doesn't work at all that day, there's just no entry for him. I don't see the
problem with this "inconsistency" in the number of jobs per day, but perhaps
I'm missing something.

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

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

Allen,
I don't think that would work because of the inconsistancy of the number
of jobs per day.
I am thinking about 7 queries, one for each day of the week, which will
convert the day of the week to a date using the week ending date. Then
union all seven dates.
Will that get me out of trouble with the running out of space?
Anne

Once you get the structure right, you can work on reducing the keystrokes
for the user.

One way to do that would be to execute an append query statement to add a
record for each employee + each date of the current pay period, showing
zero hours, the employee's normal foreman, and whatever else you can
guess at. This means the data entry person has just to replace the zero
with the hours.

But whatever the interface, the right data structure is crucial. (Sounds
like you understand the significance of that.)

Yes, I am already thinking about that and I need some help in how to go
about it.
Right now the form is structured this way:
The user selects the employee from a combobox
The form has a default date field (tuesday of current week), which is
the weekending date.
The payrollItem is a combo for PayrollItemID where the default is
"hours"
Next field is a combo for the JobID. Now follow the days of the week.

Data entry is very simple and fast, all the user has to enter for the
Hourly work, is the job number and the hours per day.
I could probably calculate backwards to arrive a the actual date for
each day of the week with dateadd?
Perhaps I can do a union query to arrive at a normal table structure?

Of course setting up the original table the correct way would be best, I
am I trying to have as little keystrokes as possible.
Current Table Structure:
TransID
ForemanID
EmployeeID
PayrollItemID
JobsID
the 7 days of the week
Weekdefault- default value =1 (used for linking)
Rate1 (misc $ earnings field earnings)
Retro Hours
CorrectionWeek (date for retropay)
Memo
WeDate: Weekending date

Any feedback is very much appreciated.
Thanks,
Anne
PS: This table is not a permanent table, it gets deleted every week
after the calculated data gets updated to the Year to date table.

Hi Anne. The core of this problem is that the data structure is wrong.

Instead of repeating fields named Mon, Tue, Wed, ... you need a
structure that lets you record the number of hours into one field,
e.g.:
EmployeeID who worked
StartDateTime date and time when the person started.
Hours number of hours worked (double)
This would let you query, group by date, crosstab to display your
current view, sum between dates, and so on. You would not need to run
multiple subqueries to get the results.

Presumably what you are wanting to do is sum the hours for each worker
in the pay period, and pay some at the normal rate, and the extras at
the overtime rate.

With the suggested structure, you will finish up with many more records
each week, but a much simpler set of calculations, and the issue of
running into the limit of opening more tables or databases does not
arise.

Sometimes the 'too complex' message indicates Access does not
understand the calculated field (e.g. its data type), but in your case
it may be that the entire query is too complex with the levels of
additional subquerying you are performing because of the bad structure.

Access 7 was known as Access 95. Access 2003 is called version 11, as
you can see if you choose About on the Help menu. Microsoft did extend
the number of databases you could open from 1024 to 2048 in Access 97,
I believe, so that's probably what you were reading about.

HTH

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

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

I never thought that there was a limitation to the fields or
calculations in a query. Now I get this message and I know I need to
change what I something.
FieldName: Wed
WedSum: (Select Sum(nz([wed],0)) from [tblHoursbyDay] where
[EmployeeId] =a.EmployeeID and [TransID] <=a.TransID)
MaxWed: DMax("Wedsum","7QryOvertime","employeenew=" & [employeenew])
WedTotal: (Select Sum(nz([wed],0)) from [tblHoursbyDay] where
[EmployeeId] =a.EmployeeID)
Besides those fields, I have about 10 more fields identifying the
employee, the foremen, where they work, the job number.
Each week I have about 500 lines of data for about 130 employees,
because each employee works on several jobs.

I am running these 3 parameters for each day of the week.
This is to determine, when overtime start.
The week starts Wednesday, and usually by Saturday (if they work) it
could be that the employee's hours run into overtime. But some
employees, work on 6 to 8 jobs a day. That is where all these
transactions come from and.

After that I have to run a year to date query to calculate how much
was earned so far to calculate the maximum for social security,
federal unemployment and Florida unemployment. After that query, when
I put the 2 queries together, then add the regular hours and overtime
hourse, and calculate the burdin, that is when it bombs.

It would prefer to to have to go to make a table, because there are
printout where one can check data and it would be a big problem to
keep recreating the table after every change.

Before I decided to let access calculate where overtime starts,
everything was working well. It would still be working well and I
figured I can just tie into the previous queries, if I didn't run into
this limitation of space or whatever?

Is there a more effient way of calculating where the overtime starts,
so I wouldn't have this problem. Microsoft knowledgebase, says the
problem was solved in access 7. What is Access 7, I use Access 2003?

Any help would be appreciated. I am filling in as payroll person for
one of my clients who is using my program, but doing the entries
myself now, I wanted to make things easier and not have to have to
person inputting the data make the decision as to when overtime
starts. Looks like tomorrow I will still have to use the old program.
Anne

 

[Anne]

I am only inputting hours the employee hours (not from to times ) and it
works as such:
Using Homer:
Wednesday: job 1 - 2 hours
Wednesday: job 2 -1/2 hrs.
Wednesday: job 3 - 4 hours
Wednesday: job 4 - .5 hrs
Thursday: job 5 - 1 hrs
Thursday: job 10 - 2 hrss
Thursday: job 1 - 0.5 hours
Thursday: Job 13 - 1 hours
Thursday: Job 16 - 2 hourws
Thursday: job 4 - 1 hour
Thursday: job 9 - 5 hours
Friday: job 13 - 2 hours
Friday: job 99 - 5 hours
Friday: job 1 - 2 hours
Friday: job 12 -1.5 hours
Saturday : job 14 - 8 hours and so on.
Right now the company is very busy and it is not unusual for the employees
to work 10 to 12 hours per day on 8 jobs in just on day. The field has much
less jobs, but it still can be 5 to 6 jobs per week.
And on any job Saturday or Sunday work is possible right now, because of the
high volumne of job.
As you can see there is no pattern.
An employee working at the carpentry job can work about 10 different jobs
per day and for the week, it can be about 20 jobs per week.
I is a real nightmore putting it into the system and deciding to change the
payroll code to overtime when the hours exceed 40. Right now I have 123
active employees, but the transactions (rows) when all calculations were
done were 562 transcations. The field works work on the average 3 jobs a
week, but it can be more.

Right now, the user has to select when overtime starts. The form gives a
running total of regular time entered, but doing the payroll myself today,
one really has to pay attention and change the earning item to overtime.
Today I missed changing the change to overtime hours on 5 employees. I have
a check built in, so it was easy to find. Entering 562 transactions, which
is realy boring to start which and it is a lot of work and as I am finding
out it is easy to make mistakes.
That is why I think it is important for the program to decide when overtime
starts. This payroll is a nightmare. It would also greatly reduce the number
of transactions, because no longer need two lines to be created for regular
time and overtime.
How can I accomplish this without running into problems with access
limitations.?
I hope you can see my problem. I still hope you can give me some guidance.
This is not an easy problem to solve.
Bye the way, the printouts resulting from all this calculations, create a
report which is submitted to a leasing company. But by precalculations on
what the burden should be has saved the company a lot of money.
They process the payroll and bill the burden including their fee and workers
compensation. My precalculation of what this payroll cost should be has
saved the company has saved a lot of money, because we are catching their
errors.

I hope this is not too lenghty.

Anne

That sounds terribly inefficient, and yes it will drain resources.

What's the problem with the number of jobs per day?
You can have one record for every time a person logs on if you wish.
For example, if Homer Simpson logs on at 12:01am, and works until 7:00am,
then logs in again at 19:00 and works until 23:00, it copes. Or if Homer
doesn't work at all that day, there's just no entry for him. I don't see
the problem with this "inconsistency" in the number of jobs per day, but
perhaps I'm missing something.

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

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

Allen,
I don't think that would work because of the inconsistancy of the number
of jobs per day.
I am thinking about 7 queries, one for each day of the week, which will
convert the day of the week to a date using the week ending date. Then
union all seven dates.
Will that get me out of trouble with the running out of space?
Anne

Once you get the structure right, you can work on reducing the
keystrokes for the user.

One way to do that would be to execute an append query statement to add
a record for each employee + each date of the current pay period,
showing zero hours, the employee's normal foreman, and whatever else you
can guess at. This means the data entry person has just to replace the
zero with the hours.

But whatever the interface, the right data structure is crucial. (Sounds
like you understand the significance of that.)


Yes, I am already thinking about that and I need some help in how to go
about it.
Right now the form is structured this way:
The user selects the employee from a combobox
The form has a default date field (tuesday of current week), which is
the weekending date.
The payrollItem is a combo for PayrollItemID where the default is
"hours"
Next field is a combo for the JobID. Now follow the days of the week.

Data entry is very simple and fast, all the user has to enter for the
Hourly work, is the job number and the hours per day.
I could probably calculate backwards to arrive a the actual date for
each day of the week with dateadd?
Perhaps I can do a union query to arrive at a normal table structure?

Of course setting up the original table the correct way would be best,
I am I trying to have as little keystrokes as possible.
Current Table Structure:
TransID
ForemanID
EmployeeID
PayrollItemID
JobsID
the 7 days of the week
Weekdefault- default value =1 (used for linking)
Rate1 (misc $ earnings field earnings)
Retro Hours
CorrectionWeek (date for retropay)
Memo
WeDate: Weekending date

Any feedback is very much appreciated.
Thanks,
Anne
PS: This table is not a permanent table, it gets deleted every week
after the calculated data gets updated to the Year to date table.

Hi Anne. The core of this problem is that the data structure is wrong.

Instead of repeating fields named Mon, Tue, Wed, ... you need a
structure that lets you record the number of hours into one field,
e.g.:
EmployeeID who worked
StartDateTime date and time when the person started.
Hours number of hours worked (double)
This would let you query, group by date, crosstab to display your
current view, sum between dates, and so on. You would not need to run
multiple subqueries to get the results.

Presumably what you are wanting to do is sum the hours for each worker
in the pay period, and pay some at the normal rate, and the extras at
the overtime rate.

With the suggested structure, you will finish up with many more
records each week, but a much simpler set of calculations, and the
issue of running into the limit of opening more tables or databases
does not arise.

Sometimes the 'too complex' message indicates Access does not
understand the calculated field (e.g. its data type), but in your case
it may be that the entire query is too complex with the levels of
additional subquerying you are performing because of the bad
structure.

Access 7 was known as Access 95. Access 2003 is called version 11, as
you can see if you choose About on the Help menu. Microsoft did extend
the number of databases you could open from 1024 to 2048 in Access 97,
I believe, so that's probably what you were reading about.

HTH

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

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

I never thought that there was a limitation to the fields or
calculations in a query. Now I get this message and I know I need to
change what I something.
FieldName: Wed
WedSum: (Select Sum(nz([wed],0)) from [tblHoursbyDay] where
[EmployeeId] =a.EmployeeID and [TransID] <=a.TransID)
MaxWed: DMax("Wedsum","7QryOvertime","employeenew=" & [employeenew])
WedTotal: (Select Sum(nz([wed],0)) from [tblHoursbyDay] where
[EmployeeId] =a.EmployeeID)
Besides those fields, I have about 10 more fields identifying the
employee, the foremen, where they work, the job number.
Each week I have about 500 lines of data for about 130 employees,
because each employee works on several jobs.

I am running these 3 parameters for each day of the week.
This is to determine, when overtime start.
The week starts Wednesday, and usually by Saturday (if they work) it
could be that the employee's hours run into overtime. But some
employees, work on 6 to 8 jobs a day. That is where all these
transactions come from and.

After that I have to run a year to date query to calculate how much
was earned so far to calculate the maximum for social security,
federal unemployment and Florida unemployment. After that query, when
I put the 2 queries together, then add the regular hours and overtime
hourse, and calculate the burdin, that is when it bombs.

It would prefer to to have to go to make a table, because there are
printout where one can check data and it would be a big problem to
keep recreating the table after every change.

Before I decided to let access calculate where overtime starts,
everything was working well. It would still be working well and I
figured I can just tie into the previous queries, if I didn't run
into this limitation of space or whatever?

Is there a more effient way of calculating where the overtime starts,
so I wouldn't have this problem. Microsoft knowledgebase, says the
problem was solved in access 7. What is Access 7, I use Access 2003?

Any help would be appreciated. I am filling in as payroll person for
one of my clients who is using my program, but doing the entries
myself now, I wanted to make things easier and not have to have to
person inputting the data make the decision as to when overtime
starts. Looks like tomorrow I will still have to use the old program.
Anne

 

[Allen Browne]

Anne, I think I would use a very different approach to this.

The table of hours worked would NOT try to figure out the pay rates, which
could theoretically change part way through a job. If you don't need the
times, then use fields:
EmployeeID foreign key
WorkDate Date/Time
Hours Number (Double)

Then you will have another table where the pays are calculated:
PayID AutoNumber
EmployeeID foreign key
WeekEnding Date/Time
Then the PayDetail table will contain:
PayID foreign key
EmployeeID forein key
Hours Number
PayRate Currency
The PayDetail will contain 1 entry for no overtime, 2 if there is also an
overtime record, and possibly others (e.g. penalty rates).

You would programmatically create the Pay and PayDetail entries each week,
based a query that groups by EmployeeID, sums the hours, where the work date
is in that pay week.

In practice, there is a bit more than that to the task, e.g. ensuring that
the daily records are not changed once pays have been calculated.

HTH.

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

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

I am only inputting hours the employee hours (not from to times ) and it
works as such:
Using Homer:
Wednesday: job 1 - 2 hours
Wednesday: job 2 -1/2 hrs.
Wednesday: job 3 - 4 hours
Wednesday: job 4 - .5 hrs
Thursday: job 5 - 1 hrs
Thursday: job 10 - 2 hrss
Thursday: job 1 - 0.5 hours
Thursday: Job 13 - 1 hours
Thursday: Job 16 - 2 hourws
Thursday: job 4 - 1 hour
Thursday: job 9 - 5 hours
Friday: job 13 - 2 hours
Friday: job 99 - 5 hours
Friday: job 1 - 2 hours
Friday: job 12 -1.5 hours
Saturday : job 14 - 8 hours and so on.
Right now the company is very busy and it is not unusual for the employees
to work 10 to 12 hours per day on 8 jobs in just on day. The field has
much less jobs, but it still can be 5 to 6 jobs per week.
And on any job Saturday or Sunday work is possible right now, because of
the high volumne of job.
As you can see there is no pattern.
An employee working at the carpentry job can work about 10 different jobs
per day and for the week, it can be about 20 jobs per week.
I is a real nightmore putting it into the system and deciding to change
the payroll code to overtime when the hours exceed 40. Right now I have
123 active employees, but the transactions (rows) when all calculations
were done were 562 transcations. The field works work on the average 3
jobs a week, but it can be more.

Right now, the user has to select when overtime starts. The form gives a
running total of regular time entered, but doing the payroll myself today,
one really has to pay attention and change the earning item to overtime.
Today I missed changing the change to overtime hours on 5 employees. I
have a check built in, so it was easy to find. Entering 562 transactions,
which is realy boring to start which and it is a lot of work and as I am
finding out it is easy to make mistakes.
That is why I think it is important for the program to decide when
overtime starts. This payroll is a nightmare. It would also greatly reduce
the number of transactions, because no longer need two lines to be created
for regular time and overtime.
How can I accomplish this without running into problems with access
limitations.?
I hope you can see my problem. I still hope you can give me some guidance.
This is not an easy problem to solve.
Bye the way, the printouts resulting from all this calculations, create a
report which is submitted to a leasing company. But by precalculations on
what the burden should be has saved the company a lot of money.
They process the payroll and bill the burden including their fee and
workers compensation. My precalculation of what this payroll cost should
be has saved the company has saved a lot of money, because we are catching
their errors.

I hope this is not too lenghty.

Anne

That sounds terribly inefficient, and yes it will drain resources.

What's the problem with the number of jobs per day?
You can have one record for every time a person logs on if you wish.
For example, if Homer Simpson logs on at 12:01am, and works until 7:00am,
then logs in again at 19:00 and works until 23:00, it copes. Or if Homer
doesn't work at all that day, there's just no entry for him. I don't see
the problem with this "inconsistency" in the number of jobs per day, but
perhaps I'm missing something.

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

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

Allen,
I don't think that would work because of the inconsistancy of the number
of jobs per day.
I am thinking about 7 queries, one for each day of the week, which will
convert the day of the week to a date using the week ending date. Then
union all seven dates.
Will that get me out of trouble with the running out of space?
Anne


Once you get the structure right, you can work on reducing the
keystrokes for the user.

One way to do that would be to execute an append query statement to add
a record for each employee + each date of the current pay period,
showing zero hours, the employee's normal foreman, and whatever else
you can guess at. This means the data entry person has just to replace
the zero with the hours.

But whatever the interface, the right data structure is crucial.
(Sounds like you understand the significance of that.)


Yes, I am already thinking about that and I need some help in how to
go about it.
Right now the form is structured this way:
The user selects the employee from a combobox
The form has a default date field (tuesday of current week), which is
the weekending date.
The payrollItem is a combo for PayrollItemID where the default is
"hours"
Next field is a combo for the JobID. Now follow the days of the week.

Data entry is very simple and fast, all the user has to enter for the
Hourly work, is the job number and the hours per day.
I could probably calculate backwards to arrive a the actual date for
each day of the week with dateadd?
Perhaps I can do a union query to arrive at a normal table structure?

Of course setting up the original table the correct way would be best,
I am I trying to have as little keystrokes as possible.
Current Table Structure:
TransID
ForemanID
EmployeeID
PayrollItemID
JobsID
the 7 days of the week
Weekdefault- default value =1 (used for linking)
Rate1 (misc $ earnings field earnings)
Retro Hours
CorrectionWeek (date for retropay)
Memo
WeDate: Weekending date

Any feedback is very much appreciated.
Thanks,
Anne
PS: This table is not a permanent table, it gets deleted every week
after the calculated data gets updated to the Year to date table.

Hi Anne. The core of this problem is that the data structure is
wrong.

Instead of repeating fields named Mon, Tue, Wed, ... you need a
structure that lets you record the number of hours into one field,
e.g.:
EmployeeID who worked
StartDateTime date and time when the person started.
Hours number of hours worked (double)
This would let you query, group by date, crosstab to display your
current view, sum between dates, and so on. You would not need to run
multiple subqueries to get the results.

Presumably what you are wanting to do is sum the hours for each
worker in the pay period, and pay some at the normal rate, and the
extras at the overtime rate.

With the suggested structure, you will finish up with many more
records each week, but a much simpler set of calculations, and the
issue of running into the limit of opening more tables or databases
does not arise.

Sometimes the 'too complex' message indicates Access does not
understand the calculated field (e.g. its data type), but in your
case it may be that the entire query is too complex with the levels
of additional subquerying you are performing because of the bad
structure.

Access 7 was known as Access 95. Access 2003 is called version 11, as
you can see if you choose About on the Help menu. Microsoft did
extend the number of databases you could open from 1024 to 2048 in
Access 97, I believe, so that's probably what you were reading about.

HTH

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

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

I never thought that there was a limitation to the fields or
calculations in a query. Now I get this message and I know I need to
change what I something.
FieldName: Wed
WedSum: (Select Sum(nz([wed],0)) from [tblHoursbyDay] where
[EmployeeId] =a.EmployeeID and [TransID] <=a.TransID)
MaxWed: DMax("Wedsum","7QryOvertime","employeenew=" & [employeenew])
WedTotal: (Select Sum(nz([wed],0)) from [tblHoursbyDay] where
[EmployeeId] =a.EmployeeID)
Besides those fields, I have about 10 more fields identifying the
employee, the foremen, where they work, the job number.
Each week I have about 500 lines of data for about 130 employees,
because each employee works on several jobs.

I am running these 3 parameters for each day of the week.
This is to determine, when overtime start.
The week starts Wednesday, and usually by Saturday (if they work) it
could be that the employee's hours run into overtime. But some
employees, work on 6 to 8 jobs a day. That is where all these
transactions come from and.

After that I have to run a year to date query to calculate how much
was earned so far to calculate the maximum for social security,
federal unemployment and Florida unemployment. After that query,
when I put the 2 queries together, then add the regular hours and
overtime hourse, and calculate the burdin, that is when it bombs.

It would prefer to to have to go to make a table, because there are
printout where one can check data and it would be a big problem to
keep recreating the table after every change.

Before I decided to let access calculate where overtime starts,
everything was working well. It would still be working well and I
figured I can just tie into the previous queries, if I didn't run
into this limitation of space or whatever?

Is there a more effient way of calculating where the overtime
starts, so I wouldn't have this problem. Microsoft knowledgebase,
says the problem was solved in access 7. What is Access 7, I use
Access 2003?

Any help would be appreciated. I am filling in as payroll person for
one of my clients who is using my program, but doing the entries
myself now, I wanted to make things easier and not have to have to
person inputting the data make the decision as to when overtime
starts. Looks like tomorrow I will still have to use the old
program.
Anne

 

[Anne]

Actually what I am trying to do with all I have explained is just calculate
the hours and automatically find regular time and overtime.

At this point there are no pay rates involved, that comes in the next step
from either the job or the employee table. There are jobs that have
prevailing wages, where I have to figure the higher of the employee rate or
the job rate, depending on the position of the employee. That I can deal
with.

There are other pay items, like travel, which is also calculated, where the
rate per day is in the job table. After the hours are calculated, I
determine the travel rates based on number of days worked and use an update
query to append to the table.

There are also holiday, vacation and sickpay, as well as retro pay.
Need to just try to work with your suggestions and see if I can get it
together.

Unfortunately, problably won't be able to work on it until tonight. Can't
wait to see if I can make it work.

Thank you,
Anne

Anne, I think I would use a very different approach to this.

The table of hours worked would NOT try to figure out the pay rates, which
could theoretically change part way through a job. If you don't need the
times, then use fields:
EmployeeID foreign key
WorkDate Date/Time
Hours Number (Double)

Then you will have another table where the pays are calculated:
PayID AutoNumber
EmployeeID foreign key
WeekEnding Date/Time
Then the PayDetail table will contain:
PayID foreign key
EmployeeID forein key
Hours Number
PayRate Currency
The PayDetail will contain 1 entry for no overtime, 2 if there is also an
overtime record, and possibly others (e.g. penalty rates).

You would programmatically create the Pay and PayDetail entries each week,
based a query that groups by EmployeeID, sums the hours, where the work
date is in that pay week.

In practice, there is a bit more than that to the task, e.g. ensuring that
the daily records are not changed once pays have been calculated.

HTH.

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

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

I am only inputting hours the employee hours (not from to times ) and it
works as such:
Using Homer:
Wednesday: job 1 - 2 hours
Wednesday: job 2 -1/2 hrs.
Wednesday: job 3 - 4 hours
Wednesday: job 4 - .5 hrs
Thursday: job 5 - 1 hrs
Thursday: job 10 - 2 hrss
Thursday: job 1 - 0.5 hours
Thursday: Job 13 - 1 hours
Thursday: Job 16 - 2 hourws
Thursday: job 4 - 1 hour
Thursday: job 9 - 5 hours
Friday: job 13 - 2 hours
Friday: job 99 - 5 hours
Friday: job 1 - 2 hours
Friday: job 12 -1.5 hours
Saturday : job 14 - 8 hours and so on.
Right now the company is very busy and it is not unusual for the
employees to work 10 to 12 hours per day on 8 jobs in just on day. The
field has much less jobs, but it still can be 5 to 6 jobs per week.
And on any job Saturday or Sunday work is possible right now, because of
the high volumne of job.
As you can see there is no pattern.
An employee working at the carpentry job can work about 10 different jobs
per day and for the week, it can be about 20 jobs per week.
I is a real nightmore putting it into the system and deciding to change
the payroll code to overtime when the hours exceed 40. Right now I have
123 active employees, but the transactions (rows) when all calculations
were done were 562 transcations. The field works work on the average 3
jobs a week, but it can be more.

Right now, the user has to select when overtime starts. The form gives a
running total of regular time entered, but doing the payroll myself
today, one really has to pay attention and change the earning item to
overtime. Today I missed changing the change to overtime hours on 5
employees. I have a check built in, so it was easy to find. Entering 562
transactions, which is realy boring to start which and it is a lot of
work and as I am finding out it is easy to make mistakes.
That is why I think it is important for the program to decide when
overtime starts. This payroll is a nightmare. It would also greatly
reduce the number of transactions, because no longer need two lines to be
created for regular time and overtime.
How can I accomplish this without running into problems with access
limitations.?
I hope you can see my problem. I still hope you can give me some
guidance. This is not an easy problem to solve.
Bye the way, the printouts resulting from all this calculations, create a
report which is submitted to a leasing company. But by precalculations on
what the burden should be has saved the company a lot of money.
They process the payroll and bill the burden including their fee and
workers compensation. My precalculation of what this payroll cost should
be has saved the company has saved a lot of money, because we are
catching their errors.

I hope this is not too lenghty.

Anne

That sounds terribly inefficient, and yes it will drain resources.

What's the problem with the number of jobs per day?
You can have one record for every time a person logs on if you wish.
For example, if Homer Simpson logs on at 12:01am, and works until
7:00am, then logs in again at 19:00 and works until 23:00, it copes. Or
if Homer doesn't work at all that day, there's just no entry for him. I
don't see the problem with this "inconsistency" in the number of jobs
per day, but perhaps I'm missing something.

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

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

Allen,
I don't think that would work because of the inconsistancy of the
number of jobs per day.
I am thinking about 7 queries, one for each day of the week, which will
convert the day of the week to a date using the week ending date. Then
union all seven dates.
Will that get me out of trouble with the running out of space?
Anne


Once you get the structure right, you can work on reducing the
keystrokes for the user.

One way to do that would be to execute an append query statement to
add a record for each employee + each date of the current pay period,
showing zero hours, the employee's normal foreman, and whatever else
you can guess at. This means the data entry person has just to replace
the zero with the hours.

But whatever the interface, the right data structure is crucial.
(Sounds like you understand the significance of that.)


Yes, I am already thinking about that and I need some help in how to
go about it.
Right now the form is structured this way:
The user selects the employee from a combobox
The form has a default date field (tuesday of current week), which
is the weekending date.
The payrollItem is a combo for PayrollItemID where the default is
"hours"
Next field is a combo for the JobID. Now follow the days of the week.

Data entry is very simple and fast, all the user has to enter for the
Hourly work, is the job number and the hours per day.
I could probably calculate backwards to arrive a the actual date for
each day of the week with dateadd?
Perhaps I can do a union query to arrive at a normal table structure?

Of course setting up the original table the correct way would be
best, I am I trying to have as little keystrokes as possible.
Current Table Structure:
TransID
ForemanID
EmployeeID
PayrollItemID
JobsID
the 7 days of the week
Weekdefault- default value =1 (used for linking)
Rate1 (misc $ earnings field earnings)
Retro Hours
CorrectionWeek (date for retropay)
Memo
WeDate: Weekending date

Any feedback is very much appreciated.
Thanks,
Anne
PS: This table is not a permanent table, it gets deleted every week
after the calculated data gets updated to the Year to date table.

Hi Anne. The core of this problem is that the data structure is
wrong.

Instead of repeating fields named Mon, Tue, Wed, ... you need a
structure that lets you record the number of hours into one field,
e.g.:
EmployeeID who worked
StartDateTime date and time when the person started.
Hours number of hours worked (double)
This would let you query, group by date, crosstab to display your
current view, sum between dates, and so on. You would not need to
run multiple subqueries to get the results.

Presumably what you are wanting to do is sum the hours for each
worker in the pay period, and pay some at the normal rate, and the
extras at the overtime rate.

With the suggested structure, you will finish up with many more
records each week, but a much simpler set of calculations, and the
issue of running into the limit of opening more tables or databases
does not arise.

Sometimes the 'too complex' message indicates Access does not
understand the calculated field (e.g. its data type), but in your
case it may be that the entire query is too complex with the levels
of additional subquerying you are performing because of the bad
structure.

Access 7 was known as Access 95. Access 2003 is called version 11,
as you can see if you choose About on the Help menu. Microsoft did
extend the number of databases you could open from 1024 to 2048 in
Access 97, I believe, so that's probably what you were reading
about.

HTH

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

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

I never thought that there was a limitation to the fields or
calculations in a query. Now I get this message and I know I need to
change what I something.
FieldName: Wed
WedSum: (Select Sum(nz([wed],0)) from [tblHoursbyDay] where
[EmployeeId] =a.EmployeeID and [TransID] <=a.TransID)
MaxWed: DMax("Wedsum","7QryOvertime","employeenew=" &
[employeenew])
WedTotal: (Select Sum(nz([wed],0)) from [tblHoursbyDay] where
[EmployeeId] =a.EmployeeID)
Besides those fields, I have about 10 more fields identifying the
employee, the foremen, where they work, the job number.
Each week I have about 500 lines of data for about 130 employees,
because each employee works on several jobs.

I am running these 3 parameters for each day of the week.
This is to determine, when overtime start.
The week starts Wednesday, and usually by Saturday (if they work)
it could be that the employee's hours run into overtime. But some
employees, work on 6 to 8 jobs a day. That is where all these
transactions come from and.

After that I have to run a year to date query to calculate how much
was earned so far to calculate the maximum for social security,
federal unemployment and Florida unemployment. After that query,
when I put the 2 queries together, then add the regular hours and
overtime hourse, and calculate the burdin, that is when it bombs.

It would prefer to to have to go to make a table, because there are
printout where one can check data and it would be a big problem to
keep recreating the table after every change.

Before I decided to let access calculate where overtime starts,
everything was working well. It would still be working well and I
figured I can just tie into the previous queries, if I didn't run
into this limitation of space or whatever?

Is there a more effient way of calculating where the overtime
starts, so I wouldn't have this problem. Microsoft knowledgebase,
says the problem was solved in access 7. What is Access 7, I use
Access 2003?

Any help would be appreciated. I am filling in as payroll person
for one of my clients who is using my program, but doing the
entries myself now, I wanted to make things easier and not have to
have to person inputting the data make the decision as to when
overtime starts. Looks like tomorrow I will still have to use the
old program.
Anne

 

[Anne]

Hi Allen, it me again, Anne
Figured out I can actual do it my way but it is the long way and a real
problem. Very lenghty
What I want to use is your idea of of creating several tables while entering
data, with the right foreign keyes.
Right now I am working on the first step:
The way I am inputting the data is absolutely the easiest way of entering
data.
But I split my tables. I created a week of the day table as follows:
EmployeeId
JobId
EarningItemId
Wed
Thu
Fri
Sat
Sun
Mon
Tue
WeekConnect

Select EarningID, JobId, Hours, date of the week, with the automatic default
week ending date (WeDate)
The user still has to:
from comboon the main table: employeeID
For subtable:
Default EarningsId is "Hours" - which is the only earning ID for the current
week which can be entered in the individual days from the separate table,
If any other earningID is used there are extra field to enter those.
Next field, which now come from the table tblDaysof Week, where the user
enters the hours for each day of the week.
I have dayoftheweek field as a forein key I need to be able to create a new
line, when ever the day of the week changes, by refering to the weekending
date and counting the days of the week. I set up Wednesday to by day 1
because that is the beginning of the week.
How can I built that in. Is it possible or do I have to go to VBA, at which
point I am in trouble.
Anne

Actually what I am trying to do with all I have explained is just
calculate the hours and automatically find regular time and overtime.

At this point there are no pay rates involved, that comes in the next step
from either the job or the employee table. There are jobs that have
prevailing wages, where I have to figure the higher of the employee rate
or the job rate, depending on the position of the employee. That I can
deal with.

There are other pay items, like travel, which is also calculated, where
the rate per day is in the job table. After the hours are calculated, I
determine the travel rates based on number of days worked and use an
update query to append to the table.

There are also holiday, vacation and sickpay, as well as retro pay.
Need to just try to work with your suggestions and see if I can get it
together.

Unfortunately, problably won't be able to work on it until tonight. Can't
wait to see if I can make it work.

Thank you,
Anne

Anne, I think I would use a very different approach to this.

The table of hours worked would NOT try to figure out the pay rates,
which could theoretically change part way through a job. If you don't
need the times, then use fields:
EmployeeID foreign key
WorkDate Date/Time
Hours Number (Double)

Then you will have another table where the pays are calculated:
PayID AutoNumber
EmployeeID foreign key
WeekEnding Date/Time
Then the PayDetail table will contain:
PayID foreign key
EmployeeID forein key
Hours Number
PayRate Currency
The PayDetail will contain 1 entry for no overtime, 2 if there is also an
overtime record, and possibly others (e.g. penalty rates).

You would programmatically create the Pay and PayDetail entries each
week, based a query that groups by EmployeeID, sums the hours, where the
work date is in that pay week.

In practice, there is a bit more than that to the task, e.g. ensuring
that the daily records are not changed once pays have been calculated.

HTH.

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

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

I am only inputting hours the employee hours (not from to times ) and it
works as such:
Using Homer:
Wednesday: job 1 - 2 hours
Wednesday: job 2 -1/2 hrs.
Wednesday: job 3 - 4 hours
Wednesday: job 4 - .5 hrs
Thursday: job 5 - 1 hrs
Thursday: job 10 - 2 hrss
Thursday: job 1 - 0.5 hours
Thursday: Job 13 - 1 hours
Thursday: Job 16 - 2 hourws
Thursday: job 4 - 1 hour
Thursday: job 9 - 5 hours
Friday: job 13 - 2 hours
Friday: job 99 - 5 hours
Friday: job 1 - 2 hours
Friday: job 12 -1.5 hours
Saturday : job 14 - 8 hours and so on.
Right now the company is very busy and it is not unusual for the
employees to work 10 to 12 hours per day on 8 jobs in just on day. The
field has much less jobs, but it still can be 5 to 6 jobs per week.
And on any job Saturday or Sunday work is possible right now, because of
the high volumne of job.
As you can see there is no pattern.
An employee working at the carpentry job can work about 10 different
jobs per day and for the week, it can be about 20 jobs per week.
I is a real nightmore putting it into the system and deciding to change
the payroll code to overtime when the hours exceed 40. Right now I have
123 active employees, but the transactions (rows) when all calculations
were done were 562 transcations. The field works work on the average 3
jobs a week, but it can be more.

Right now, the user has to select when overtime starts. The form gives a
running total of regular time entered, but doing the payroll myself
today, one really has to pay attention and change the earning item to
overtime. Today I missed changing the change to overtime hours on 5
employees. I have a check built in, so it was easy to find. Entering 562
transactions, which is realy boring to start which and it is a lot of
work and as I am finding out it is easy to make mistakes.
That is why I think it is important for the program to decide when
overtime starts. This payroll is a nightmare. It would also greatly
reduce the number of transactions, because no longer need two lines to
be created for regular time and overtime.
How can I accomplish this without running into problems with access
limitations.?
I hope you can see my problem. I still hope you can give me some
guidance. This is not an easy problem to solve.
Bye the way, the printouts resulting from all this calculations, create
a report which is submitted to a leasing company. But by precalculations
on what the burden should be has saved the company a lot of money.
They process the payroll and bill the burden including their fee and
workers compensation. My precalculation of what this payroll cost should
be has saved the company has saved a lot of money, because we are
catching their errors.

I hope this is not too lenghty.

Anne





That sounds terribly inefficient, and yes it will drain resources.

What's the problem with the number of jobs per day?
You can have one record for every time a person logs on if you wish.
For example, if Homer Simpson logs on at 12:01am, and works until
7:00am, then logs in again at 19:00 and works until 23:00, it copes. Or
if Homer doesn't work at all that day, there's just no entry for him. I
don't see the problem with this "inconsistency" in the number of jobs
per day, but perhaps I'm missing something.

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

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

Allen,
I don't think that would work because of the inconsistancy of the
number of jobs per day.
I am thinking about 7 queries, one for each day of the week, which
will convert the day of the week to a date using the week ending date.
Then union all seven dates.
Will that get me out of trouble with the running out of space?
Anne


Once you get the structure right, you can work on reducing the
keystrokes for the user.

One way to do that would be to execute an append query statement to
add a record for each employee + each date of the current pay period,
showing zero hours, the employee's normal foreman, and whatever else
you can guess at. This means the data entry person has just to
replace the zero with the hours.

But whatever the interface, the right data structure is crucial.
(Sounds like you understand the significance of that.)


Yes, I am already thinking about that and I need some help in how to
go about it.
Right now the form is structured this way:
The user selects the employee from a combobox
The form has a default date field (tuesday of current week), which
is the weekending date.
The payrollItem is a combo for PayrollItemID where the default is
"hours"
Next field is a combo for the JobID. Now follow the days of the
week.

Data entry is very simple and fast, all the user has to enter for
the Hourly work, is the job number and the hours per day.
I could probably calculate backwards to arrive a the actual date for
each day of the week with dateadd?
Perhaps I can do a union query to arrive at a normal table
structure?

Of course setting up the original table the correct way would be
best, I am I trying to have as little keystrokes as possible.
Current Table Structure:
TransID
ForemanID
EmployeeID
PayrollItemID
JobsID
the 7 days of the week
Weekdefault- default value =1 (used for linking)
Rate1 (misc $ earnings field earnings)
Retro Hours
CorrectionWeek (date for retropay)
Memo
WeDate: Weekending date

Any feedback is very much appreciated.
Thanks,
Anne
PS: This table is not a permanent table, it gets deleted every week
after the calculated data gets updated to the Year to date table.

Hi Anne. The core of this problem is that the data structure is
wrong.

Instead of repeating fields named Mon, Tue, Wed, ... you need a
structure that lets you record the number of hours into one field,
e.g.:
EmployeeID who worked
StartDateTime date and time when the person started.
Hours number of hours worked (double)
This would let you query, group by date, crosstab to display your
current view, sum between dates, and so on. You would not need to
run multiple subqueries to get the results.

Presumably what you are wanting to do is sum the hours for each
worker in the pay period, and pay some at the normal rate, and the
extras at the overtime rate.

With the suggested structure, you will finish up with many more
records each week, but a much simpler set of calculations, and the
issue of running into the limit of opening more tables or databases
does not arise.

Sometimes the 'too complex' message indicates Access does not
understand the calculated field (e.g. its data type), but in your
case it may be that the entire query is too complex with the levels
of additional subquerying you are performing because of the bad
structure.

Access 7 was known as Access 95. Access 2003 is called version 11,
as you can see if you choose About on the Help menu. Microsoft did
extend the number of databases you could open from 1024 to 2048 in
Access 97, I believe, so that's probably what you were reading
about.

HTH

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

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

I never thought that there was a limitation to the fields or
calculations in a query. Now I get this message and I know I need
to change what I something.
FieldName: Wed
WedSum: (Select Sum(nz([wed],0)) from [tblHoursbyDay] where
[EmployeeId] =a.EmployeeID and [TransID] <=a.TransID)
MaxWed: DMax("Wedsum","7QryOvertime","employeenew=" &
[employeenew])
WedTotal: (Select Sum(nz([wed],0)) from [tblHoursbyDay] where
[EmployeeId] =a.EmployeeID)
Besides those fields, I have about 10 more fields identifying the
employee, the foremen, where they work, the job number.
Each week I have about 500 lines of data for about 130 employees,
because each employee works on several jobs.

I am running these 3 parameters for each day of the week.
This is to determine, when overtime start.
The week starts Wednesday, and usually by Saturday (if they work)
it could be that the employee's hours run into overtime. But some
employees, work on 6 to 8 jobs a day. That is where all these
transactions come from and.

After that I have to run a year to date query to calculate how
much was earned so far to calculate the maximum for social
security, federal unemployment and Florida unemployment. After
that query, when I put the 2 queries together, then add the
regular hours and overtime hourse, and calculate the burdin, that
is when it bombs.

It would prefer to to have to go to make a table, because there
are printout where one can check data and it would be a big
problem to keep recreating the table after every change.

Before I decided to let access calculate where overtime starts,
everything was working well. It would still be working well and I
figured I can just tie into the previous queries, if I didn't run
into this limitation of space or whatever?

Is there a more effient way of calculating where the overtime
starts, so I wouldn't have this problem. Microsoft knowledgebase,
says the problem was solved in access 7. What is Access 7, I use
Access 2003?

Any help would be appreciated. I am filling in as payroll person
for one of my clients who is using my program, but doing the
entries myself now, I wanted to make things easier and not have to
have to person inputting the data make the decision as to when
overtime starts. Looks like tomorrow I will still have to use the
old program.
Anne

 

[Anne]

My question was accidentially sent before I was really finished.
Do you understand what I am trying to do?
Anne

Hi Allen, it me again, Anne
Figured out I can actual do it my way but it is the long way and a real
problem. Very lenghty
What I want to use is your idea of of creating several tables while
entering data, with the right foreign keyes.
Right now I am working on the first step:
The way I am inputting the data is absolutely the easiest way of entering
data.
But I split my tables. I created a week of the day table as follows:
EmployeeId
JobId
EarningItemId
Wed
Thu
Fri
Sat
Sun
Mon
Tue
WeekConnect

Select EarningID, JobId, Hours, date of the week, with the automatic
default week ending date (WeDate)
The user still has to:
from comboon the main table: employeeID
For subtable:
Default EarningsId is "Hours" - which is the only earning ID for the
current week which can be entered in the individual days from the separate
table,
If any other earningID is used there are extra field to enter those.
Next field, which now come from the table tblDaysof Week, where the user
enters the hours for each day of the week.
I have dayoftheweek field as a forein key I need to be able to create a
new line, when ever the day of the week changes, by refering to the
weekending date and counting the days of the week. I set up Wednesday to
by day 1 because that is the beginning of the week.
How can I built that in. Is it possible or do I have to go to VBA, at
which point I am in trouble.
Anne

Actually what I am trying to do with all I have explained is just
calculate the hours and automatically find regular time and overtime.

At this point there are no pay rates involved, that comes in the next
step from either the job or the employee table. There are jobs that have
prevailing wages, where I have to figure the higher of the employee rate
or the job rate, depending on the position of the employee. That I can
deal with.

There are other pay items, like travel, which is also calculated, where
the rate per day is in the job table. After the hours are calculated, I
determine the travel rates based on number of days worked and use an
update query to append to the table.

There are also holiday, vacation and sickpay, as well as retro pay.
Need to just try to work with your suggestions and see if I can get it
together.

Unfortunately, problably won't be able to work on it until tonight. Can't
wait to see if I can make it work.

Thank you,
Anne

Anne, I think I would use a very different approach to this.

The table of hours worked would NOT try to figure out the pay rates,
which could theoretically change part way through a job. If you don't
need the times, then use fields:
EmployeeID foreign key
WorkDate Date/Time
Hours Number (Double)

Then you will have another table where the pays are calculated:
PayID AutoNumber
EmployeeID foreign key
WeekEnding Date/Time
Then the PayDetail table will contain:
PayID foreign key
EmployeeID forein key
Hours Number
PayRate Currency
The PayDetail will contain 1 entry for no overtime, 2 if there is also
an overtime record, and possibly others (e.g. penalty rates).

You would programmatically create the Pay and PayDetail entries each
week, based a query that groups by EmployeeID, sums the hours, where the
work date is in that pay week.

In practice, there is a bit more than that to the task, e.g. ensuring
that the daily records are not changed once pays have been calculated.

HTH.

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

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

I am only inputting hours the employee hours (not from to times ) and
it works as such:
Using Homer:
Wednesday: job 1 - 2 hours
Wednesday: job 2 -1/2 hrs.
Wednesday: job 3 - 4 hours
Wednesday: job 4 - .5 hrs
Thursday: job 5 - 1 hrs
Thursday: job 10 - 2 hrss
Thursday: job 1 - 0.5 hours
Thursday: Job 13 - 1 hours
Thursday: Job 16 - 2 hourws
Thursday: job 4 - 1 hour
Thursday: job 9 - 5 hours
Friday: job 13 - 2 hours
Friday: job 99 - 5 hours
Friday: job 1 - 2 hours
Friday: job 12 -1.5 hours
Saturday : job 14 - 8 hours and so on.
Right now the company is very busy and it is not unusual for the
employees to work 10 to 12 hours per day on 8 jobs in just on day. The
field has much less jobs, but it still can be 5 to 6 jobs per week.
And on any job Saturday or Sunday work is possible right now, because
of the high volumne of job.
As you can see there is no pattern.
An employee working at the carpentry job can work about 10 different
jobs per day and for the week, it can be about 20 jobs per week.
I is a real nightmore putting it into the system and deciding to change
the payroll code to overtime when the hours exceed 40. Right now I have
123 active employees, but the transactions (rows) when all calculations
were done were 562 transcations. The field works work on the average 3
jobs a week, but it can be more.

Right now, the user has to select when overtime starts. The form gives
a running total of regular time entered, but doing the payroll myself
today, one really has to pay attention and change the earning item to
overtime. Today I missed changing the change to overtime hours on 5
employees. I have a check built in, so it was easy to find. Entering
562 transactions, which is realy boring to start which and it is a lot
of work and as I am finding out it is easy to make mistakes.
That is why I think it is important for the program to decide when
overtime starts. This payroll is a nightmare. It would also greatly
reduce the number of transactions, because no longer need two lines to
be created for regular time and overtime.
How can I accomplish this without running into problems with access
limitations.?
I hope you can see my problem. I still hope you can give me some
guidance. This is not an easy problem to solve.
Bye the way, the printouts resulting from all this calculations, create
a report which is submitted to a leasing company. But by
precalculations on what the burden should be has saved the company a
lot of money.
They process the payroll and bill the burden including their fee and
workers compensation. My precalculation of what this payroll cost
should be has saved the company has saved a lot of money, because we
are catching their errors.

I hope this is not too lenghty.

Anne





That sounds terribly inefficient, and yes it will drain resources.

What's the problem with the number of jobs per day?
You can have one record for every time a person logs on if you wish.
For example, if Homer Simpson logs on at 12:01am, and works until
7:00am, then logs in again at 19:00 and works until 23:00, it copes.
Or if Homer doesn't work at all that day, there's just no entry for
him. I don't see the problem with this "inconsistency" in the number
of jobs per day, but perhaps I'm missing something.

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

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

Allen,
I don't think that would work because of the inconsistancy of the
number of jobs per day.
I am thinking about 7 queries, one for each day of the week, which
will convert the day of the week to a date using the week ending
date. Then union all seven dates.
Will that get me out of trouble with the running out of space?
Anne


Once you get the structure right, you can work on reducing the
keystrokes for the user.

One way to do that would be to execute an append query statement to
add a record for each employee + each date of the current pay
period, showing zero hours, the employee's normal foreman, and
whatever else you can guess at. This means the data entry person has
just to replace the zero with the hours.

But whatever the interface, the right data structure is crucial.
(Sounds like you understand the significance of that.)


Yes, I am already thinking about that and I need some help in how
to go about it.
Right now the form is structured this way:
The user selects the employee from a combobox
The form has a default date field (tuesday of current week), which
is the weekending date.
The payrollItem is a combo for PayrollItemID where the default is
"hours"
Next field is a combo for the JobID. Now follow the days of the
week.

Data entry is very simple and fast, all the user has to enter for
the Hourly work, is the job number and the hours per day.
I could probably calculate backwards to arrive a the actual date
for each day of the week with dateadd?
Perhaps I can do a union query to arrive at a normal table
structure?

Of course setting up the original table the correct way would be
best, I am I trying to have as little keystrokes as possible.
Current Table Structure:
TransID
ForemanID
EmployeeID
PayrollItemID
JobsID
the 7 days of the week
Weekdefault- default value =1 (used for linking)
Rate1 (misc $ earnings field earnings)
Retro Hours
CorrectionWeek (date for retropay)
Memo
WeDate: Weekending date

Any feedback is very much appreciated.
Thanks,
Anne
PS: This table is not a permanent table, it gets deleted every week
after the calculated data gets updated to the Year to date table.

Hi Anne. The core of this problem is that the data structure is
wrong.

Instead of repeating fields named Mon, Tue, Wed, ... you need a
structure that lets you record the number of hours into one field,
e.g.:
EmployeeID who worked
StartDateTime date and time when the person started.
Hours number of hours worked (double)
This would let you query, group by date, crosstab to display your
current view, sum between dates, and so on. You would not need to
run multiple subqueries to get the results.

Presumably what you are wanting to do is sum the hours for each
worker in the pay period, and pay some at the normal rate, and the
extras at the overtime rate.

With the suggested structure, you will finish up with many more
records each week, but a much simpler set of calculations, and the
issue of running into the limit of opening more tables or
databases does not arise.

Sometimes the 'too complex' message indicates Access does not
understand the calculated field (e.g. its data type), but in your
case it may be that the entire query is too complex with the
levels of additional subquerying you are performing because of the
bad structure.

Access 7 was known as Access 95. Access 2003 is called version 11,
as you can see if you choose About on the Help menu. Microsoft did
extend the number of databases you could open from 1024 to 2048 in
Access 97, I believe, so that's probably what you were reading
about.

HTH

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

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

I never thought that there was a limitation to the fields or
calculations in a query. Now I get this message and I know I need
to change what I something.
FieldName: Wed
WedSum: (Select Sum(nz([wed],0)) from [tblHoursbyDay] where
[EmployeeId] =a.EmployeeID and [TransID] <=a.TransID)
MaxWed: DMax("Wedsum","7QryOvertime","employeenew=" &
[employeenew])
WedTotal: (Select Sum(nz([wed],0)) from [tblHoursbyDay] where
[EmployeeId] =a.EmployeeID)
Besides those fields, I have about 10 more fields identifying the
employee, the foremen, where they work, the job number.
Each week I have about 500 lines of data for about 130 employees,
because each employee works on several jobs.

I am running these 3 parameters for each day of the week.
This is to determine, when overtime start.
The week starts Wednesday, and usually by Saturday (if they work)
it could be that the employee's hours run into overtime. But some
employees, work on 6 to 8 jobs a day. That is where all these
transactions come from and.

After that I have to run a year to date query to calculate how
much was earned so far to calculate the maximum for social
security, federal unemployment and Florida unemployment. After
that query, when I put the 2 queries together, then add the
regular hours and overtime hourse, and calculate the burdin, that
is when it bombs.

It would prefer to to have to go to make a table, because there
are printout where one can check data and it would be a big
problem to keep recreating the table after every change.

Before I decided to let access calculate where overtime starts,
everything was working well. It would still be working well and I
figured I can just tie into the previous queries, if I didn't run
into this limitation of space or whatever?

Is there a more effient way of calculating where the overtime
starts, so I wouldn't have this problem. Microsoft knowledgebase,
says the problem was solved in access 7. What is Access 7, I use
Access 2003?

Any help would be appreciated. I am filling in as payroll person
for one of my clients who is using my program, but doing the
entries myself now, I wanted to make things easier and not have
to have to person inputting the data make the decision as to when
overtime starts. Looks like tomorrow I will still have to use the
old program.
Anne

 

[Allen Browne]

Hi Anne. The first answer I gave you began:
Hi Anne. The core of this problem is that the data structure is wrong.
Instead of repeating fields named Mon, Tue, Wed, ...

I would not construct a table as you suggest, and am not willing to spend
time explaining how to do it wrongly.