Beginner VBA help with multiple tables

[Claire]

I am just starting a foray into VBA, and am stuck right at the beginning.

My goal is to total some fields in one table, for each employee, and to add
these totals to a different table, with some information from previous
records in the second table incorporated. How can I get access to select all
of the records in table one, the most recent for each employee in table two,
and then combine them? (some will write as is, others add, others subtract,
etc.)

Is there a way to loop through each record from a select statement? I'm at
a loss for what the syntax is, or where to find examples of the syntax.

If more details would be helpful, here's the basics of the tables:
tblHours
EmployeeID----Date----Hrs----Type
A-----------1/1/09-----6--------Regular
A-----------1/1/09-----2--------Vacation
A-----------1/2/09-----8--------Overtime
B-----------1/1/09-----8--------Regular

tblEmployees
EmployeeID--------Dept---------VacRate
A-------------------sales-----------2
B-------------------admin----------3

tblHistor
EmployeeID---Dept---PayrollEnding-------RegularHrs------OvertimeHrs-----VacationHrs------LifetimeHrs------VacationEarned---------VacationBalanc
A--------------sales-------1/2/09-------------6------------------8-------------------2------------------150--------------2-----------------------1
B--------------admin------1/2/09-------------8------------------0-------------------0-------------------100-------------1-----------------------12


I need to total each employees' hours in tblHours for a week, put these in
tblHistory fields, add them to LifetimeHrs, multiply some of them by their
VacRate(from tblEmployees) to get VacationEarned, and add VacationEarned and
subtract VacationHrs to get the VacationBalance.

I have a good idea of how to write the code for each element, ie which field
is getting multiplied by each and which is getting entered where, but I do
not know how to set up the skeleton. The whole part of selecting these
records and doing something with each of them (or, you know, with the sum of
them).

Thanks for any help you have. Anything described here or a link to some
basics on this kind of VBA would be GREATLY appreciated!

~Claire

 

[dymondjack]

Hi Claire... welcome aboard


There's various different ways to do what you are looking for, and I'll give
one example here. It may not always be the *best* way, but a the Recordset
object is very universal when working with specific data. It may not be
quite as fast as different queries to do the these tasks, but Recordsets have
always been 'old reliables' for me, and they have a very broad scope of what
can be done with them. You will need to know some basic SQL (Select
statements) to pull the data that you want to work with.

'===============
Sub GetTotal()

Dim db As Database 'To reference what db we're in
Dim rs as DAO.Recordset 'This holds the records to loop
Dim strSQL As String 'Your SQL statement
Dim lCount As Long 'This will tally the hours

'Objects like databases & records need to be "set"
Set db = CurrentDB
Set rs = db.OpenRecordset(strSQL)
'Now rs is equal to your select statement

'Make sure there's at least one record
If rs.RecordCount <> 0 Then

'Move to the first record in the set
rs.MoveFirst

'Loop the recordset until it hits the end
While rs.EOF = False
'Add the record's Hrs field value to lCount...
lCount = lCount + rs.Fields("Hrs")

'Move to the next record
rs.MoveNext

Wend

End If

'Close the Recordset
rs.Close

'Unreference the objects
Set rs = Nothing
Set db = Nothing
'====================


There you go...

If you want to add a record, use this:

With rs
.AddNew
.Fields("fieldname") = Value
.Fields("fieldname") = Value
.Update
End With


Or if you want to edit the current record:

With rs
.Edit
.Fields("fieldname") = Value
.Update
End With


Or delete the current record

rs.Delete



Anyway, you get the idea. Use the Recordset Object... theres all sorts of
more in depth tutorials if you need more detailed info...

hth

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[Claire]

Thanks very much for your help! There's one more element I'm unclear on.
How do I refer to another table that is related to the first? I'm going to
be utilizing at least three tables: One with hour information, one with
information about the employees, and one where the new data is going to be
added. I'm quite comfortable with the SQL aspects, but how do you join
tables (or do you do something else?) is VBA?

 

[dymondjack]

When working with Recordsets, you will only work with one table per
recordset. Use your SQL to get the related records


strSQL = "SELECT * FROM table WHERE [forgienkey] = " & foreignkeyID

Set rs = CurrentDB.OpenRecordset(strSQL)

So if you wanted hours for a specific employee, build it in your SQL, and
the Recordset will open only those hours.

You can work with multiple recordset objects though:

Dim rsEmployees As DAO.Recordset
Dim rsHours As DAO.Recordset

Set rsEmployees = CurrentDB.OpenRecordset("tblEmployees")
Set rsHours = CurrentDB.OpenRecordset(strSQL)

You can then reference either/or recordset in vba, and intertwine loops for
each recordset. A common example of using two recordsets for me is to add
data to a separate table based on the frist recordset.

While not rsEmployees.EOF
with rsHours
.Addnew
.Fields("field") = whatever
.Update
End With
rsEmployees.MoveNext
Wend

So in an example here, you loop your Employees recordset, and for each, and
add a record to the Hours recordset for each employee. Feel free to use any
conditional statements to further control your code within the loop, but also
be wary of performance issues when using loops like this: the more it has to
check for each record, the longer it will take.

Keep in mind also that there are queries that can do this as well, usually
more efficiently. Unfortunately I am not well enough versed with them to
feel comfortable offering advice to use them, and I gave the Recordset
example because it offers much more flexability and can be used more
universally (IMO).

If you want to see about accomplishing some of this using queries, theres a
number of sites that offer good information on them.

www.allenbrowne.com/tips.html

http://www.accessmvp.com/strive4peace/

http://www.mvps.org/access/


One final note on performance with recordsets: although it does take longer
than queries, I tend to use recordsets a lot more (it's what I first
learned), and I have a number of applications that run over a
frontend/backend on a network with a few thousand records, and have never
experienced and *major* performance issues. So, while queries may be more
efficient, it may not be enough to make a noticable difference.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[Steve Sanford]

Hi Claire,

I have a couple of comments and observations in addition to what Jack
posted....

In the table "tblHours", there are fields named "date" and "type". these are
reserved words in Access (and SQL). Using them as field names will cause you
major headaches down the road. In addition "date" is not very descriptive...
"date" of what??? "type" of what??? "WorkHrs" and "HrsType" might be better
names for the fields. Allen Browne has a list of reserved words at

http://allenbrowne.com/AppIssueBadWord.html


I see in your history table you are storing the results (totals/ sums) of
hours. This is generally considered bad practice. Here is an example: Suppose
employee "A" worked all of January. On 1/25 he reported that the hours he
reported was wrong for 1/12 because he had to take 2 hours personal time. He
reported 8 hours worked... now you need to subtract 2 hours from the 1/12
entry. But you also need to subtract 2 hours from the LifetimeHrs field on
1/13. And the same for 1/14.... and for 1/15... and ..... what a chore!

And what happens is you have to start tracking Sick Days earned/used??? You
will have to redesign the table, queries, forms and reports.

I would think about adding the department field to the table "tblHours".
Then to get the lifetime hours, use a totals query to sum the regular and
overtime hours.

Same with the Vacation Balance. You would have an entry of -2 hours for
"VacationUsed" and have an positive entry for "VacationEarned".

my 2 pennies....

HTH

 

[laavista]

Dymondjack: I also need help with looping multiple tables.

I have two tables; t_HK and t_Reserv. Using a "do while not EOF", I want to
go to the first record in HK_active and the first record in Reserv_active and
update each table from a value in the other table.
goto 1st record in t_HK
goto 1st record in t_Reserv
use field "HKNum" in t_HK and update a field in t_reserv
use field "TripsReservID" and update a field in t_HK
goto the next record in t_HK
goto the next record in t_Reserv
use field "HKNum" in t_HK and update a field in t_reserv
use field "TripsReservID" and update a field in t_HK
etc.

Right now, I'm just trying to move to the next record in each form. When
the procedure starts, I am on record 1 for both tables. Now I need to move
to the 2nd record for both tables.

DoCmd.OpenForm "f_HK"
DoCmd.OpenForm "f_Reserv"


Forms!f_HK!HKNum.SetFocus
longintHKNum = Forms.f_HK.HKNum
' at record 1 for t_HK

Forms!f_Reserv!TripsReservID.SetFocus
longintTripsReservID = Forms.f_Reserv.TripsReservID
' at record 1 for t_Reserv


Forms!f_HK!HKNum.SetFocus
DoCmd.GoToRecord , , acNext
longintHKNum = Forms.f_HK.HKNum
' stayed on record 1

Forms!f_Reserv!TripsReservID.SetFocus
DoCmd.GoToRecord , , acNext
longintTripsReservID = Forms.f_Reserv.TripsReservID
' Now at record 3 ???

Your help would be GREATLY appreciated.

When working with Recordsets, you will only work with one table per
recordset. Use your SQL to get the related records


strSQL = "SELECT * FROM table WHERE [forgienkey] = " & foreignkeyID

Set rs = CurrentDB.OpenRecordset(strSQL)

So if you wanted hours for a specific employee, build it in your SQL, and
the Recordset will open only those hours.

You can work with multiple recordset objects though:

Dim rsEmployees As DAO.Recordset
Dim rsHours As DAO.Recordset

Set rsEmployees = CurrentDB.OpenRecordset("tblEmployees")
Set rsHours = CurrentDB.OpenRecordset(strSQL)

You can then reference either/or recordset in vba, and intertwine loops for
each recordset. A common example of using two recordsets for me is to add
data to a separate table based on the frist recordset.

While not rsEmployees.EOF
with rsHours
.Addnew
.Fields("field") = whatever
.Update
End With
rsEmployees.MoveNext
Wend

So in an example here, you loop your Employees recordset, and for each, and
add a record to the Hours recordset for each employee. Feel free to use any
conditional statements to further control your code within the loop, but also
be wary of performance issues when using loops like this: the more it has to
check for each record, the longer it will take.

Keep in mind also that there are queries that can do this as well, usually
more efficiently. Unfortunately I am not well enough versed with them to
feel comfortable offering advice to use them, and I gave the Recordset
example because it offers much more flexability and can be used more
universally (IMO).

If you want to see about accomplishing some of this using queries, theres a
number of sites that offer good information on them.

www.allenbrowne.com/tips.html

http://www.accessmvp.com/strive4peace/

http://www.mvps.org/access/


One final note on performance with recordsets: although it does take longer
than queries, I tend to use recordsets a lot more (it's what I first
learned), and I have a number of applications that run over a
frontend/backend on a network with a few thousand records, and have never
experienced and *major* performance issues. So, while queries may be more
efficient, it may not be enough to make a noticable difference.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

Thanks very much for your help! There's one more element I'm unclear on.
How do I refer to another table that is related to the first? I'm going to
be utilizing at least three tables: One with hour information, one with
information about the employees, and one where the new data is going to be
added. I'm quite comfortable with the SQL aspects, but how do you join
tables (or do you do something else?) is VBA?

 

[dymondjack]

Are you trying to add a new record to t_Reserve, or are the records already
there and you are trying to populate that particular field?

If the records are already there and you are trying to populate, there will
have to be the exact same amount of records, in the same order for both
tables.

I think you should be able to do this with an append query, if you are
adding a new record to t_Reserve. Anyway, I'm on the way out the door now
with a busy day ahead of me, but if you don't have something figured out
later tonight (about 12 hours anyway regardless of location) I'll give an
example of how to do it w/ a recordset.


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

Dymondjack: I also need help with looping multiple tables.

I have two tables; t_HK and t_Reserv. Using a "do while not EOF", I want to
go to the first record in HK_active and the first record in Reserv_active and
update each table from a value in the other table.
goto 1st record in t_HK
goto 1st record in t_Reserv
use field "HKNum" in t_HK and update a field in t_reserv
use field "TripsReservID" and update a field in t_HK
goto the next record in t_HK
goto the next record in t_Reserv
use field "HKNum" in t_HK and update a field in t_reserv
use field "TripsReservID" and update a field in t_HK
etc.

Right now, I'm just trying to move to the next record in each form. When
the procedure starts, I am on record 1 for both tables. Now I need to move
to the 2nd record for both tables.

DoCmd.OpenForm "f_HK"
DoCmd.OpenForm "f_Reserv"


Forms!f_HK!HKNum.SetFocus
longintHKNum = Forms.f_HK.HKNum
' at record 1 for t_HK

Forms!f_Reserv!TripsReservID.SetFocus
longintTripsReservID = Forms.f_Reserv.TripsReservID
' at record 1 for t_Reserv


Forms!f_HK!HKNum.SetFocus
DoCmd.GoToRecord , , acNext
longintHKNum = Forms.f_HK.HKNum
' stayed on record 1

Forms!f_Reserv!TripsReservID.SetFocus
DoCmd.GoToRecord , , acNext
longintTripsReservID = Forms.f_Reserv.TripsReservID
' Now at record 3 ???

Your help would be GREATLY appreciated.

When working with Recordsets, you will only work with one table per
recordset. Use your SQL to get the related records


strSQL = "SELECT * FROM table WHERE [forgienkey] = " & foreignkeyID

Set rs = CurrentDB.OpenRecordset(strSQL)

So if you wanted hours for a specific employee, build it in your SQL, and
the Recordset will open only those hours.

You can work with multiple recordset objects though:

Dim rsEmployees As DAO.Recordset
Dim rsHours As DAO.Recordset

Set rsEmployees = CurrentDB.OpenRecordset("tblEmployees")
Set rsHours = CurrentDB.OpenRecordset(strSQL)

You can then reference either/or recordset in vba, and intertwine loops for
each recordset. A common example of using two recordsets for me is to add
data to a separate table based on the frist recordset.

While not rsEmployees.EOF
with rsHours
.Addnew
.Fields("field") = whatever
.Update
End With
rsEmployees.MoveNext
Wend

So in an example here, you loop your Employees recordset, and for each, and
add a record to the Hours recordset for each employee. Feel free to use any
conditional statements to further control your code within the loop, but also
be wary of performance issues when using loops like this: the more it has to
check for each record, the longer it will take.

Keep in mind also that there are queries that can do this as well, usually
more efficiently. Unfortunately I am not well enough versed with them to
feel comfortable offering advice to use them, and I gave the Recordset
example because it offers much more flexability and can be used more
universally (IMO).

If you want to see about accomplishing some of this using queries, theres a
number of sites that offer good information on them.

www.allenbrowne.com/tips.html

http://www.accessmvp.com/strive4peace/

http://www.mvps.org/access/


One final note on performance with recordsets: although it does take longer
than queries, I tend to use recordsets a lot more (it's what I first
learned), and I have a number of applications that run over a
frontend/backend on a network with a few thousand records, and have never
experienced and *major* performance issues. So, while queries may be more
efficient, it may not be enough to make a noticable difference.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

Thanks very much for your help! There's one more element I'm unclear on.
How do I refer to another table that is related to the first? I'm going to
be utilizing at least three tables: One with hour information, one with
information about the employees, and one where the new data is going to be
added. I'm quite comfortable with the SQL aspects, but how do you join
tables (or do you do something else?) is VBA?



:

Hi Claire... welcome aboard


There's various different ways to do what you are looking for, and I'll give
one example here. It may not always be the *best* way, but a the Recordset
object is very universal when working with specific data. It may not be
quite as fast as different queries to do the these tasks, but Recordsets have
always been 'old reliables' for me, and they have a very broad scope of what
can be done with them. You will need to know some basic SQL (Select
statements) to pull the data that you want to work with.

'===============
Sub GetTotal()

Dim db As Database 'To reference what db we're in
Dim rs as DAO.Recordset 'This holds the records to loop
Dim strSQL As String 'Your SQL statement
Dim lCount As Long 'This will tally the hours

'Objects like databases & records need to be "set"
Set db = CurrentDB
Set rs = db.OpenRecordset(strSQL)
'Now rs is equal to your select statement

'Make sure there's at least one record
If rs.RecordCount <> 0 Then

'Move to the first record in the set
rs.MoveFirst

'Loop the recordset until it hits the end
While rs.EOF = False
'Add the record's Hrs field value to lCount...
lCount = lCount + rs.Fields("Hrs")

'Move to the next record
rs.MoveNext

Wend

End If

'Close the Recordset
rs.Close

'Unreference the objects
Set rs = Nothing
Set db = Nothing
'====================


There you go...

If you want to add a record, use this:

With rs
.AddNew
.Fields("fieldname") = Value
.Fields("fieldname") = Value
.Update
End With


Or if you want to edit the current record:

With rs
.Edit
.Fields("fieldname") = Value
.Update
End With


Or delete the current record

rs.Delete



Anyway, you get the idea. Use the Recordset Object... theres all sorts of
more in depth tutorials if you need more detailed info...

hth

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


:

I am just starting a foray into VBA, and am stuck right at the beginning.

My goal is to total some fields in one table, for each employee, and to add
these totals to a different table, with some information from previous
records in the second table incorporated. How can I get access to select all
of the records in table one, the most recent for each employee in table two,
and then combine them? (some will write as is, others add, others subtract,
etc.)

Is there a way to loop through each record from a select statement? I'm at
a loss for what the syntax is, or where to find examples of the syntax.

If more details would be helpful, here's the basics of the tables:
tblHours
EmployeeID----Date----Hrs----Type
A-----------1/1/09-----6--------Regular
A-----------1/1/09-----2--------Vacation
A-----------1/2/09-----8--------Overtime
B-----------1/1/09-----8--------Regular

tblEmployees
EmployeeID--------Dept---------VacRate
A-------------------sales-----------2
B-------------------admin----------3

tblHistory
EmployeeID---Dept---PayrollEnding-------RegularHrs------OvertimeHrs-----VacationHrs------LifetimeHrs------VacationEarned---------VacationBalance
A--------------sales-------1/2/09-------------6------------------8-------------------2------------------150--------------2-----------------------15
B--------------admin------1/2/09-------------8------------------0-------------------0-------------------100-------------1-----------------------12


I need to total each employees' hours in tblHours for a week, put these in
tblHistory fields, add them to LifetimeHrs, multiply some of them by their
VacRate(from tblEmployees) to get VacationEarned, and add VacationEarned and
subtract VacationHrs to get the VacationBalance.

I have a good idea of how to write the code for each element, ie which field
is getting multiplied by each and which is getting entered where, but I do
not know how to set up the skeleton. The whole part of selecting these
records and doing something with each of them (or, you know, with the sum of
them).

Thanks for any help you have. Anything described here or a link to some
basics on this kind of VBA would be GREATLY appreciated!

~Claire

 

[Douglas J. Steele]

Can you not just use an Update query?

It's seldom efficient to use VBA when updating every record in a table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Dymondjack: I also need help with looping multiple tables.

I have two tables; t_HK and t_Reserv. Using a "do while not EOF", I want
to
go to the first record in HK_active and the first record in Reserv_active
and
update each table from a value in the other table.
goto 1st record in t_HK
goto 1st record in t_Reserv
use field "HKNum" in t_HK and update a field in t_reserv
use field "TripsReservID" and update a field in t_HK
goto the next record in t_HK
goto the next record in t_Reserv
use field "HKNum" in t_HK and update a field in t_reserv
use field "TripsReservID" and update a field in t_HK
etc.

Right now, I'm just trying to move to the next record in each form. When
the procedure starts, I am on record 1 for both tables. Now I need to
move
to the 2nd record for both tables.

DoCmd.OpenForm "f_HK"
DoCmd.OpenForm "f_Reserv"


Forms!f_HK!HKNum.SetFocus
longintHKNum = Forms.f_HK.HKNum
' at record 1 for t_HK

Forms!f_Reserv!TripsReservID.SetFocus
longintTripsReservID = Forms.f_Reserv.TripsReservID
' at record 1 for t_Reserv


Forms!f_HK!HKNum.SetFocus
DoCmd.GoToRecord , , acNext
longintHKNum = Forms.f_HK.HKNum
' stayed on record 1

Forms!f_Reserv!TripsReservID.SetFocus
DoCmd.GoToRecord , , acNext
longintTripsReservID = Forms.f_Reserv.TripsReservID
' Now at record 3 ???

Your help would be GREATLY appreciated.

When working with Recordsets, you will only work with one table per
recordset. Use your SQL to get the related records


strSQL = "SELECT * FROM table WHERE [forgienkey] = " & foreignkeyID

Set rs = CurrentDB.OpenRecordset(strSQL)

So if you wanted hours for a specific employee, build it in your SQL, and
the Recordset will open only those hours.

You can work with multiple recordset objects though:

Dim rsEmployees As DAO.Recordset
Dim rsHours As DAO.Recordset

Set rsEmployees = CurrentDB.OpenRecordset("tblEmployees")
Set rsHours = CurrentDB.OpenRecordset(strSQL)

You can then reference either/or recordset in vba, and intertwine loops
for
each recordset. A common example of using two recordsets for me is to
add
data to a separate table based on the frist recordset.

While not rsEmployees.EOF
with rsHours
.Addnew
.Fields("field") = whatever
.Update
End With
rsEmployees.MoveNext
Wend

So in an example here, you loop your Employees recordset, and for each,
and
add a record to the Hours recordset for each employee. Feel free to use
any
conditional statements to further control your code within the loop, but
also
be wary of performance issues when using loops like this: the more it has
to
check for each record, the longer it will take.

Keep in mind also that there are queries that can do this as well,
usually
more efficiently. Unfortunately I am not well enough versed with them to
feel comfortable offering advice to use them, and I gave the Recordset
example because it offers much more flexability and can be used more
universally (IMO).

If you want to see about accomplishing some of this using queries, theres
a
number of sites that offer good information on them.

www.allenbrowne.com/tips.html

http://www.accessmvp.com/strive4peace/

http://www.mvps.org/access/


One final note on performance with recordsets: although it does take
longer
than queries, I tend to use recordsets a lot more (it's what I first
learned), and I have a number of applications that run over a
frontend/backend on a network with a few thousand records, and have never
experienced and *major* performance issues. So, while queries may be
more
efficient, it may not be enough to make a noticable difference.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your
leisure."
- Mark Twain

Thanks very much for your help! There's one more element I'm unclear
on.
How do I refer to another table that is related to the first? I'm
going to
be utilizing at least three tables: One with hour information, one with
information about the employees, and one where the new data is going to
be
added. I'm quite comfortable with the SQL aspects, but how do you join
tables (or do you do something else?) is VBA?



:

Hi Claire... welcome aboard


There's various different ways to do what you are looking for, and
I'll give
one example here. It may not always be the *best* way, but a the
Recordset
object is very universal when working with specific data. It may not
be
quite as fast as different queries to do the these tasks, but
Recordsets have
always been 'old reliables' for me, and they have a very broad scope
of what
can be done with them. You will need to know some basic SQL (Select
statements) to pull the data that you want to work with.

'===============
Sub GetTotal()

Dim db As Database 'To reference what db we're in
Dim rs as DAO.Recordset 'This holds the records to loop
Dim strSQL As String 'Your SQL statement
Dim lCount As Long 'This will tally the hours

'Objects like databases & records need to be "set"
Set db = CurrentDB
Set rs = db.OpenRecordset(strSQL)
'Now rs is equal to your select statement

'Make sure there's at least one record
If rs.RecordCount <> 0 Then

'Move to the first record in the set
rs.MoveFirst

'Loop the recordset until it hits the end
While rs.EOF = False
'Add the record's Hrs field value to lCount...
lCount = lCount + rs.Fields("Hrs")

'Move to the next record
rs.MoveNext

Wend

End If

'Close the Recordset
rs.Close

'Unreference the objects
Set rs = Nothing
Set db = Nothing
'====================


There you go...

If you want to add a record, use this:

With rs
.AddNew
.Fields("fieldname") = Value
.Fields("fieldname") = Value
.Update
End With


Or if you want to edit the current record:

With rs
.Edit
.Fields("fieldname") = Value
.Update
End With


Or delete the current record

rs.Delete



Anyway, you get the idea. Use the Recordset Object... theres all
sorts of
more in depth tutorials if you need more detailed info...

hth

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your
leisure."
- Mark Twain


:

I am just starting a foray into VBA, and am stuck right at the
beginning.

My goal is to total some fields in one table, for each employee,
and to add
these totals to a different table, with some information from
previous
records in the second table incorporated. How can I get access to
select all
of the records in table one, the most recent for each employee in
table two,
and then combine them? (some will write as is, others add, others
subtract,
etc.)

Is there a way to loop through each record from a select statement?
I'm at
a loss for what the syntax is, or where to find examples of the
syntax.

If more details would be helpful, here's the basics of the tables:
tblHours
EmployeeID----Date----Hrs----Type
A-----------1/1/09-----6--------Regular
A-----------1/1/09-----2--------Vacation
A-----------1/2/09-----8--------Overtime
B-----------1/1/09-----8--------Regular

tblEmployees
EmployeeID--------Dept---------VacRate
A-------------------sales-----------2
B-------------------admin----------3

tblHistory
EmployeeID---Dept---PayrollEnding-------RegularHrs------OvertimeHrs-----VacationHrs------LifetimeHrs------VacationEarned---------VacationBalance
A--------------sales-------1/2/09-------------6------------------8-------------------2------------------150--------------2-----------------------15
B--------------admin------1/2/09-------------8------------------0-------------------0-------------------100-------------1-----------------------12


I need to total each employees' hours in tblHours for a week, put
these in
tblHistory fields, add them to LifetimeHrs, multiply some of them
by their
VacRate(from tblEmployees) to get VacationEarned, and add
VacationEarned and
subtract VacationHrs to get the VacationBalance.

I have a good idea of how to write the code for each element, ie
which field
is getting multiplied by each and which is getting entered where,
but I do
not know how to set up the skeleton. The whole part of selecting
these
records and doing something with each of them (or, you know, with
the sum of
them).

Thanks for any help you have. Anything described here or a link to
some
basics on this kind of VBA would be GREATLY appreciated!

~Claire

 

[laavista]

Dymondjack:

I need to update records already in the table.

No, the two tables will not have the same number of records. There would be
30 records maximum in either of the tables, though.

Thanks in advance for any help you can provide.

Are you trying to add a new record to t_Reserve, or are the records already
there and you are trying to populate that particular field?

If the records are already there and you are trying to populate, there will
have to be the exact same amount of records, in the same order for both
tables.

I think you should be able to do this with an append query, if you are
adding a new record to t_Reserve. Anyway, I'm on the way out the door now
with a busy day ahead of me, but if you don't have something figured out
later tonight (about 12 hours anyway regardless of location) I'll give an
example of how to do it w/ a recordset.


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

Dymondjack: I also need help with looping multiple tables.

I have two tables; t_HK and t_Reserv. Using a "do while not EOF", I want to
go to the first record in HK_active and the first record in Reserv_active and
update each table from a value in the other table.
goto 1st record in t_HK
goto 1st record in t_Reserv
use field "HKNum" in t_HK and update a field in t_reserv
use field "TripsReservID" and update a field in t_HK
goto the next record in t_HK
goto the next record in t_Reserv
use field "HKNum" in t_HK and update a field in t_reserv
use field "TripsReservID" and update a field in t_HK
etc.

Right now, I'm just trying to move to the next record in each form. When
the procedure starts, I am on record 1 for both tables. Now I need to move
to the 2nd record for both tables.

DoCmd.OpenForm "f_HK"
DoCmd.OpenForm "f_Reserv"


Forms!f_HK!HKNum.SetFocus
longintHKNum = Forms.f_HK.HKNum
' at record 1 for t_HK

Forms!f_Reserv!TripsReservID.SetFocus
longintTripsReservID = Forms.f_Reserv.TripsReservID
' at record 1 for t_Reserv


Forms!f_HK!HKNum.SetFocus
DoCmd.GoToRecord , , acNext
longintHKNum = Forms.f_HK.HKNum
' stayed on record 1

Forms!f_Reserv!TripsReservID.SetFocus
DoCmd.GoToRecord , , acNext
longintTripsReservID = Forms.f_Reserv.TripsReservID
' Now at record 3 ???

Your help would be GREATLY appreciated.

When working with Recordsets, you will only work with one table per
recordset. Use your SQL to get the related records


strSQL = "SELECT * FROM table WHERE [forgienkey] = " & foreignkeyID

Set rs = CurrentDB.OpenRecordset(strSQL)

So if you wanted hours for a specific employee, build it in your SQL, and
the Recordset will open only those hours.

You can work with multiple recordset objects though:

Dim rsEmployees As DAO.Recordset
Dim rsHours As DAO.Recordset

Set rsEmployees = CurrentDB.OpenRecordset("tblEmployees")
Set rsHours = CurrentDB.OpenRecordset(strSQL)

You can then reference either/or recordset in vba, and intertwine loops for
each recordset. A common example of using two recordsets for me is to add
data to a separate table based on the frist recordset.

While not rsEmployees.EOF
with rsHours
.Addnew
.Fields("field") = whatever
.Update
End With
rsEmployees.MoveNext
Wend

So in an example here, you loop your Employees recordset, and for each, and
add a record to the Hours recordset for each employee. Feel free to use any
conditional statements to further control your code within the loop, but also
be wary of performance issues when using loops like this: the more it has to
check for each record, the longer it will take.

Keep in mind also that there are queries that can do this as well, usually
more efficiently. Unfortunately I am not well enough versed with them to
feel comfortable offering advice to use them, and I gave the Recordset
example because it offers much more flexability and can be used more
universally (IMO).

If you want to see about accomplishing some of this using queries, theres a
number of sites that offer good information on them.

www.allenbrowne.com/tips.html

http://www.accessmvp.com/strive4peace/

http://www.mvps.org/access/


One final note on performance with recordsets: although it does take longer
than queries, I tend to use recordsets a lot more (it's what I first
learned), and I have a number of applications that run over a
frontend/backend on a network with a few thousand records, and have never
experienced and *major* performance issues. So, while queries may be more
efficient, it may not be enough to make a noticable difference.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


:

Thanks very much for your help! There's one more element I'm unclear on.
How do I refer to another table that is related to the first? I'm going to
be utilizing at least three tables: One with hour information, one with
information about the employees, and one where the new data is going to be
added. I'm quite comfortable with the SQL aspects, but how do you join
tables (or do you do something else?) is VBA?



:

Hi Claire... welcome aboard


There's various different ways to do what you are looking for, and I'll give
one example here. It may not always be the *best* way, but a the Recordset
object is very universal when working with specific data. It may not be
quite as fast as different queries to do the these tasks, but Recordsets have
always been 'old reliables' for me, and they have a very broad scope of what
can be done with them. You will need to know some basic SQL (Select
statements) to pull the data that you want to work with.

'===============
Sub GetTotal()

Dim db As Database 'To reference what db we're in
Dim rs as DAO.Recordset 'This holds the records to loop
Dim strSQL As String 'Your SQL statement
Dim lCount As Long 'This will tally the hours

'Objects like databases & records need to be "set"
Set db = CurrentDB
Set rs = db.OpenRecordset(strSQL)
'Now rs is equal to your select statement

'Make sure there's at least one record
If rs.RecordCount <> 0 Then

'Move to the first record in the set
rs.MoveFirst

'Loop the recordset until it hits the end
While rs.EOF = False
'Add the record's Hrs field value to lCount...
lCount = lCount + rs.Fields("Hrs")

'Move to the next record
rs.MoveNext

Wend

End If

'Close the Recordset
rs.Close

'Unreference the objects
Set rs = Nothing
Set db = Nothing
'====================


There you go...

If you want to add a record, use this:

With rs
.AddNew
.Fields("fieldname") = Value
.Fields("fieldname") = Value
.Update
End With


Or if you want to edit the current record:

With rs
.Edit
.Fields("fieldname") = Value
.Update
End With


Or delete the current record

rs.Delete



Anyway, you get the idea. Use the Recordset Object... theres all sorts of
more in depth tutorials if you need more detailed info...

hth

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


:

I am just starting a foray into VBA, and am stuck right at the beginning.

My goal is to total some fields in one table, for each employee, and to add
these totals to a different table, with some information from previous
records in the second table incorporated. How can I get access to select all
of the records in table one, the most recent for each employee in table two,
and then combine them? (some will write as is, others add, others subtract,
etc.)

Is there a way to loop through each record from a select statement? I'm at
a loss for what the syntax is, or where to find examples of the syntax.

If more details would be helpful, here's the basics of the tables:
tblHours
EmployeeID----Date----Hrs----Type
A-----------1/1/09-----6--------Regular
A-----------1/1/09-----2--------Vacation
A-----------1/2/09-----8--------Overtime
B-----------1/1/09-----8--------Regular

tblEmployees
EmployeeID--------Dept---------VacRate
A-------------------sales-----------2
B-------------------admin----------3

tblHistory
EmployeeID---Dept---PayrollEnding-------RegularHrs------OvertimeHrs-----VacationHrs------LifetimeHrs------VacationEarned---------VacationBalance
A--------------sales-------1/2/09-------------6------------------8-------------------2------------------150--------------2-----------------------15
B--------------admin------1/2/09-------------8------------------0-------------------0-------------------100-------------1-----------------------12


I need to total each employees' hours in tblHours for a week, put these in
tblHistory fields, add them to LifetimeHrs, multiply some of them by their
VacRate(from tblEmployees) to get VacationEarned, and add VacationEarned and
subtract VacationHrs to get the VacationBalance.

I have a good idea of how to write the code for each element, ie which field
is getting multiplied by each and which is getting entered where, but I do
not know how to set up the skeleton. The whole part of selecting these
records and doing something with each of them (or, you know, with the sum of
them).

Thanks for any help you have. Anything described here or a link to some
basics on this kind of VBA would be GREATLY appreciated!

~Claire

 

[laavista]

I'm not sure how to use the update query in this case...

Can you not just use an Update query?

It's seldom efficient to use VBA when updating every record in a table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Dymondjack: I also need help with looping multiple tables.

I have two tables; t_HK and t_Reserv. Using a "do while not EOF", I want
to
go to the first record in HK_active and the first record in Reserv_active
and
update each table from a value in the other table.
goto 1st record in t_HK
goto 1st record in t_Reserv
use field "HKNum" in t_HK and update a field in t_reserv
use field "TripsReservID" and update a field in t_HK
goto the next record in t_HK
goto the next record in t_Reserv
use field "HKNum" in t_HK and update a field in t_reserv
use field "TripsReservID" and update a field in t_HK
etc.

Right now, I'm just trying to move to the next record in each form. When
the procedure starts, I am on record 1 for both tables. Now I need to
move
to the 2nd record for both tables.

DoCmd.OpenForm "f_HK"
DoCmd.OpenForm "f_Reserv"


Forms!f_HK!HKNum.SetFocus
longintHKNum = Forms.f_HK.HKNum
' at record 1 for t_HK

Forms!f_Reserv!TripsReservID.SetFocus
longintTripsReservID = Forms.f_Reserv.TripsReservID
' at record 1 for t_Reserv


Forms!f_HK!HKNum.SetFocus
DoCmd.GoToRecord , , acNext
longintHKNum = Forms.f_HK.HKNum
' stayed on record 1

Forms!f_Reserv!TripsReservID.SetFocus
DoCmd.GoToRecord , , acNext
longintTripsReservID = Forms.f_Reserv.TripsReservID
' Now at record 3 ???

Your help would be GREATLY appreciated.

When working with Recordsets, you will only work with one table per
recordset. Use your SQL to get the related records


strSQL = "SELECT * FROM table WHERE [forgienkey] = " & foreignkeyID

Set rs = CurrentDB.OpenRecordset(strSQL)

So if you wanted hours for a specific employee, build it in your SQL, and
the Recordset will open only those hours.

You can work with multiple recordset objects though:

Dim rsEmployees As DAO.Recordset
Dim rsHours As DAO.Recordset

Set rsEmployees = CurrentDB.OpenRecordset("tblEmployees")
Set rsHours = CurrentDB.OpenRecordset(strSQL)

You can then reference either/or recordset in vba, and intertwine loops
for
each recordset. A common example of using two recordsets for me is to
add
data to a separate table based on the frist recordset.

While not rsEmployees.EOF
with rsHours
.Addnew
.Fields("field") = whatever
.Update
End With
rsEmployees.MoveNext
Wend

So in an example here, you loop your Employees recordset, and for each,
and
add a record to the Hours recordset for each employee. Feel free to use
any
conditional statements to further control your code within the loop, but
also
be wary of performance issues when using loops like this: the more it has
to
check for each record, the longer it will take.

Keep in mind also that there are queries that can do this as well,
usually
more efficiently. Unfortunately I am not well enough versed with them to
feel comfortable offering advice to use them, and I gave the Recordset
example because it offers much more flexability and can be used more
universally (IMO).

If you want to see about accomplishing some of this using queries, theres
a
number of sites that offer good information on them.

www.allenbrowne.com/tips.html

http://www.accessmvp.com/strive4peace/

http://www.mvps.org/access/


One final note on performance with recordsets: although it does take
longer
than queries, I tend to use recordsets a lot more (it's what I first
learned), and I have a number of applications that run over a
frontend/backend on a network with a few thousand records, and have never
experienced and *major* performance issues. So, while queries may be
more
efficient, it may not be enough to make a noticable difference.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your
leisure."
- Mark Twain


:

Thanks very much for your help! There's one more element I'm unclear
on.
How do I refer to another table that is related to the first? I'm
going to
be utilizing at least three tables: One with hour information, one with
information about the employees, and one where the new data is going to
be
added. I'm quite comfortable with the SQL aspects, but how do you join
tables (or do you do something else?) is VBA?



:

Hi Claire... welcome aboard


There's various different ways to do what you are looking for, and
I'll give
one example here. It may not always be the *best* way, but a the
Recordset
object is very universal when working with specific data. It may not
be
quite as fast as different queries to do the these tasks, but
Recordsets have
always been 'old reliables' for me, and they have a very broad scope
of what
can be done with them. You will need to know some basic SQL (Select
statements) to pull the data that you want to work with.

'===============
Sub GetTotal()

Dim db As Database 'To reference what db we're in
Dim rs as DAO.Recordset 'This holds the records to loop
Dim strSQL As String 'Your SQL statement
Dim lCount As Long 'This will tally the hours

'Objects like databases & records need to be "set"
Set db = CurrentDB
Set rs = db.OpenRecordset(strSQL)
'Now rs is equal to your select statement

'Make sure there's at least one record
If rs.RecordCount <> 0 Then

'Move to the first record in the set
rs.MoveFirst

'Loop the recordset until it hits the end
While rs.EOF = False
'Add the record's Hrs field value to lCount...
lCount = lCount + rs.Fields("Hrs")

'Move to the next record
rs.MoveNext

Wend

End If

'Close the Recordset
rs.Close

'Unreference the objects
Set rs = Nothing
Set db = Nothing
'====================


There you go...

If you want to add a record, use this:

With rs
.AddNew
.Fields("fieldname") = Value
.Fields("fieldname") = Value
.Update
End With


Or if you want to edit the current record:

With rs
.Edit
.Fields("fieldname") = Value
.Update
End With


Or delete the current record

rs.Delete



Anyway, you get the idea. Use the Recordset Object... theres all
sorts of
more in depth tutorials if you need more detailed info...

hth

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your
leisure."
- Mark Twain


:

I am just starting a foray into VBA, and am stuck right at the
beginning.

My goal is to total some fields in one table, for each employee,
and to add
these totals to a different table, with some information from
previous
records in the second table incorporated. How can I get access to
select all
of the records in table one, the most recent for each employee in
table two,
and then combine them? (some will write as is, others add, others
subtract,
etc.)

Is there a way to loop through each record from a select statement?
I'm at
a loss for what the syntax is, or where to find examples of the
syntax.

If more details would be helpful, here's the basics of the tables:
tblHours
EmployeeID----Date----Hrs----Type
A-----------1/1/09-----6--------Regular
A-----------1/1/09-----2--------Vacation
A-----------1/2/09-----8--------Overtime
B-----------1/1/09-----8--------Regular

tblEmployees
EmployeeID--------Dept---------VacRate
A-------------------sales-----------2
B-------------------admin----------3

tblHistory
EmployeeID---Dept---PayrollEnding-------RegularHrs------OvertimeHrs-----VacationHrs------LifetimeHrs------VacationEarned---------VacationBalance
A--------------sales-------1/2/09-------------6------------------8-------------------2------------------150--------------2-----------------------15
B--------------admin------1/2/09-------------8------------------0-------------------0-------------------100-------------1-----------------------12


I need to total each employees' hours in tblHours for a week, put
these in
tblHistory fields, add them to LifetimeHrs, multiply some of them

 

[dymondjack]

Well, I said I'd be back to post, but I think I'm going to have to leave this
to someone else.

By the sounds of it, if this is a one-time thing, you should probably do it
manually. If this is something you are trying to set up for use on a regular
basis, I think you might want to check the data structure. I don't really
have a good grasp on what you have right now (I always have trouble
understanding people's layouts, no matter how well they try and explain).

If you really do want to try and use a recordset to accomplish this, the
previous posts in this thread give the basics on how to navigate through it.

Sorry I couldn't help more.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

Dymondjack:

I need to update records already in the table.

No, the two tables will not have the same number of records. There would be
30 records maximum in either of the tables, though.

Thanks in advance for any help you can provide.

Are you trying to add a new record to t_Reserve, or are the records already
there and you are trying to populate that particular field?

If the records are already there and you are trying to populate, there will
have to be the exact same amount of records, in the same order for both
tables.

I think you should be able to do this with an append query, if you are
adding a new record to t_Reserve. Anyway, I'm on the way out the door now
with a busy day ahead of me, but if you don't have something figured out
later tonight (about 12 hours anyway regardless of location) I'll give an
example of how to do it w/ a recordset.


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

Dymondjack: I also need help with looping multiple tables.

I have two tables; t_HK and t_Reserv. Using a "do while not EOF", I want to
go to the first record in HK_active and the first record in Reserv_active and
update each table from a value in the other table.
goto 1st record in t_HK
goto 1st record in t_Reserv
use field "HKNum" in t_HK and update a field in t_reserv
use field "TripsReservID" and update a field in t_HK
goto the next record in t_HK
goto the next record in t_Reserv
use field "HKNum" in t_HK and update a field in t_reserv
use field "TripsReservID" and update a field in t_HK
etc.

Right now, I'm just trying to move to the next record in each form. When
the procedure starts, I am on record 1 for both tables. Now I need to move
to the 2nd record for both tables.

DoCmd.OpenForm "f_HK"
DoCmd.OpenForm "f_Reserv"


Forms!f_HK!HKNum.SetFocus
longintHKNum = Forms.f_HK.HKNum
' at record 1 for t_HK

Forms!f_Reserv!TripsReservID.SetFocus
longintTripsReservID = Forms.f_Reserv.TripsReservID
' at record 1 for t_Reserv


Forms!f_HK!HKNum.SetFocus
DoCmd.GoToRecord , , acNext
longintHKNum = Forms.f_HK.HKNum
' stayed on record 1

Forms!f_Reserv!TripsReservID.SetFocus
DoCmd.GoToRecord , , acNext
longintTripsReservID = Forms.f_Reserv.TripsReservID
' Now at record 3 ???

Your help would be GREATLY appreciated.


:

When working with Recordsets, you will only work with one table per
recordset. Use your SQL to get the related records


strSQL = "SELECT * FROM table WHERE [forgienkey] = " & foreignkeyID

Set rs = CurrentDB.OpenRecordset(strSQL)

So if you wanted hours for a specific employee, build it in your SQL, and
the Recordset will open only those hours.

You can work with multiple recordset objects though:

Dim rsEmployees As DAO.Recordset
Dim rsHours As DAO.Recordset

Set rsEmployees = CurrentDB.OpenRecordset("tblEmployees")
Set rsHours = CurrentDB.OpenRecordset(strSQL)

You can then reference either/or recordset in vba, and intertwine loops for
each recordset. A common example of using two recordsets for me is to add
data to a separate table based on the frist recordset.

While not rsEmployees.EOF
with rsHours
.Addnew
.Fields("field") = whatever
.Update
End With
rsEmployees.MoveNext
Wend

So in an example here, you loop your Employees recordset, and for each, and
add a record to the Hours recordset for each employee. Feel free to use any
conditional statements to further control your code within the loop, but also
be wary of performance issues when using loops like this: the more it has to
check for each record, the longer it will take.

Keep in mind also that there are queries that can do this as well, usually
more efficiently. Unfortunately I am not well enough versed with them to
feel comfortable offering advice to use them, and I gave the Recordset
example because it offers much more flexability and can be used more
universally (IMO).

If you want to see about accomplishing some of this using queries, theres a
number of sites that offer good information on them.

www.allenbrowne.com/tips.html

http://www.accessmvp.com/strive4peace/

http://www.mvps.org/access/


One final note on performance with recordsets: although it does take longer
than queries, I tend to use recordsets a lot more (it's what I first
learned), and I have a number of applications that run over a
frontend/backend on a network with a few thousand records, and have never
experienced and *major* performance issues. So, while queries may be more
efficient, it may not be enough to make a noticable difference.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


:

Thanks very much for your help! There's one more element I'm unclear on.
How do I refer to another table that is related to the first? I'm going to
be utilizing at least three tables: One with hour information, one with
information about the employees, and one where the new data is going to be
added. I'm quite comfortable with the SQL aspects, but how do you join
tables (or do you do something else?) is VBA?



:

Hi Claire... welcome aboard


There's various different ways to do what you are looking for, and I'll give
one example here. It may not always be the *best* way, but a the Recordset
object is very universal when working with specific data. It may not be
quite as fast as different queries to do the these tasks, but Recordsets have
always been 'old reliables' for me, and they have a very broad scope of what
can be done with them. You will need to know some basic SQL (Select
statements) to pull the data that you want to work with.

'===============
Sub GetTotal()

Dim db As Database 'To reference what db we're in
Dim rs as DAO.Recordset 'This holds the records to loop
Dim strSQL As String 'Your SQL statement
Dim lCount As Long 'This will tally the hours

'Objects like databases & records need to be "set"
Set db = CurrentDB
Set rs = db.OpenRecordset(strSQL)
'Now rs is equal to your select statement

'Make sure there's at least one record
If rs.RecordCount <> 0 Then

'Move to the first record in the set
rs.MoveFirst

'Loop the recordset until it hits the end
While rs.EOF = False
'Add the record's Hrs field value to lCount...
lCount = lCount + rs.Fields("Hrs")

'Move to the next record
rs.MoveNext

Wend

End If

'Close the Recordset
rs.Close

'Unreference the objects
Set rs = Nothing
Set db = Nothing
'====================


There you go...

If you want to add a record, use this:

With rs
.AddNew
.Fields("fieldname") = Value
.Fields("fieldname") = Value
.Update
End With


Or if you want to edit the current record:

With rs
.Edit
.Fields("fieldname") = Value
.Update
End With


Or delete the current record

rs.Delete



Anyway, you get the idea. Use the Recordset Object... theres all sorts of
more in depth tutorials if you need more detailed info...

hth

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


:

I am just starting a foray into VBA, and am stuck right at the beginning.

My goal is to total some fields in one table, for each employee, and to add
these totals to a different table, with some information from previous
records in the second table incorporated. How can I get access to select all
of the records in table one, the most recent for each employee in table two,
and then combine them? (some will write as is, others add, others subtract,
etc.)

Is there a way to loop through each record from a select statement? I'm at
a loss for what the syntax is, or where to find examples of the syntax.

If more details would be helpful, here's the basics of the tables:
tblHours
EmployeeID----Date----Hrs----Type
A-----------1/1/09-----6--------Regular
A-----------1/1/09-----2--------Vacation
A-----------1/2/09-----8--------Overtime
B-----------1/1/09-----8--------Regular

tblEmployees
EmployeeID--------Dept---------VacRate
A-------------------sales-----------2
B-------------------admin----------3

tblHistory
EmployeeID---Dept---PayrollEnding-------RegularHrs------OvertimeHrs-----VacationHrs------LifetimeHrs------VacationEarned---------VacationBalance
A--------------sales-------1/2/09-------------6------------------8-------------------2------------------150--------------2-----------------------15
B--------------admin------1/2/09-------------8------------------0-------------------0-------------------100-------------1-----------------------12


I need to total each employees' hours in tblHours for a week, put these in
tblHistory fields, add them to LifetimeHrs, multiply some of them by their
VacRate(from tblEmployees) to get VacationEarned, and add VacationEarned and
subtract VacationHrs to get the VacationBalance.

I have a good idea of how to write the code for each element, ie which field
is getting multiplied by each and which is getting entered where, but I do
not know how to set up the skeleton. The whole part of selecting these
records and doing something with each of them (or, you know, with the sum of
them).

 

[Claire]

Thanks for your thoughts, Steve,
I'm actually not using "date" and "type" for the field names, but typed them
in for the simplified example here. Next time I'll make sure to write them
out a bit more so that it's more clear that I've passed the hurdle of banned
words.

As for the possible changes, this whole process is further complicated by
the fact that the information is being imported from a company wide database
that can not be custom queried or reported (let me tell you how much that
makes me happy...), and it is formatted so that no changes can be made to
prior weeks. Changes only take place with current dates, even if they are
fixes to previous information. At least it performs the referential
integrity...

And as for the possible addition of other fields, I would love to do this so
that other fields could be added easier, but things like changes in vacation
rates and required hours were convoluting the query to something not
manageable. After posting about that crazy scenario to the query forum, I
received this suggestion, which I think is going to simplify the process. Of
course, now I need to be able to actually write the code, rather than just
explaining it to people.

Now, off to stop reading about VBA and just try it. The joys of the fun
projects I can get to on Fridays! Hopefully I'll have some success...