Loops

[Twotone]

I need to create a loop that will cycle through a series of date, each time
updating a different table. The process begins with a user entering a begin
date and end date for a date range. Once the user enters the dates I have a
query that returns that pay periods that are within that date range. For
each pay period date I need to calculate the total number of hours an
employee worked by earning code.

I was thinking of using an array, but I'm honestly not sure if thats the
best path to take. I'm hoping someone might be able to steer me in the right
direction to make this happen.

Thanks

 

[Klatuu]

I would use an update or append query depending on whether you are adding
records are updating info in the records. Even if you are updating multiple
tables, make a query for each table then execute the queries in order. It is
a lot less work than writing recordset processing and usually faster to
execute.

 

[Twotone]

Well, the pay period end dates are contained within one table, e.g.

01/05/2007
01/12/2007
01/19/2007

I have one query that uses a date from the table above in order to return
the total hours an employee worked by pay code. How do I make it so the app
uses the first date performs the query, updates the proper table with the
results, and then moves to the next date, repeating the process until all the
dates in the table have been processed?

Thank you

 

[Klatuu]

Actually, with a litte intermediate SQL, you could do that, but rereading
your original post, you are updating multiple tables. Why? Normally your
payroll information would be in a single table. If you have to transfer this
to a GL account, that is a separate issue.
Can you give some detail on the process?

 

[Twotone]

Thank you for your reply. My apologies for the confusion. The payroll data
is stored in multiple tables. The query results are actually updating one
table with multiple columns 5 for each pay period, for e.g.

I run my query for the pay period ending on 01/05/07
The results are than posted to a table which is eventually exported to excel
and sent to a user.
The table contains multiple columns one for each period...this is what the
users have requested..
So for the 1st period (01/05/2007) the appropriate columns (REG, EMP, SHD,
SSP, OVT) are updated with the total number of hours an employee worked
grouped by pay code.
Essentially the table looks like this
EE ID, F_NAME, L_NAME, PERIOD_END_DT, REG1, EMP1, SHD1, SSP1, OVT1,
PERIOD_END_DT2, REG2, EMP2, SHD2, SSP2, OVT2
1234, BOB, SMITH, 01/05/2007, 80, 0, 0, 0, 1, 01/12/2007, 80,0,0,0,5, etc.

Does this help?
If you have any suggestions I'm all ears.

Thanks

 

[Klatuu]

Why are all those field repeated in the same table?
You don't seem to have a relational database, rather an hard to manage
spreadsheet.
I can't even suggest how you might resolve this. How can you manage this
when you don't know how many employees you have. I'm sorry, and I don't mean
to be rude, but this is a totally unmanageable situation that needs redesign
from the bottom up.
Don't consider how it needs to look in Excel when you desing the tables.
Design a normalised relational database first. Then worry about how you will
export it to Excel.

 

[Twotone]

That's what the user needs. I have to meet the requirement set by the user.
How would I ever know how many employees I have when the number varies
between pay periods, can't predict turnover. Not my database, its an
enterprise application. Can't change it.

 

[Klatuu]

I'm sorry, Twotone. I sympathize with your situation, but I don't believe I
can help with this. Unless you are very experienced in VBA and automation in
Access with the Excel object model, I have serious doubts you can do it.
It is not that I don't want to help, but without the database and specific
knowledge of the objective, I don't have enough information to assist you.