F
Fred Wilson
Hello all.
I have toyed and played with the following without success. I know I can
do what I want to do with a recordset, but it seems like this is
something a query can do.
Primary Table of data: UsageHistory (UseDate, CL_RM, QTY_Uses)
So in this table we have a running history of the days a classroom is
used and the number of users, students and/or attenders if you will. A
classroom may have more than one use in a date.
Table: ClassroomDesc (CL_RM, BLDG, QtySeats, Projector)
CL_RM is the PK with all the classrooms listed
Table: tempTBL_UseCombined (FromDate, ToDate, NumDaysUsed,TotalQtyUsed)
Via VBA I run some scripts to empty this table, then I insert all the
classrooms that have had use, total days in use, the date from and to.
Then I add the rest of the Classroom that are not already there with the
qty of zero for days used.
Here is where I need help.
From the UsageHistory table I total the QTY_Uses between the date
ranges. All is well to this point.
Now I want to enter the total data into
tempTBL_UseCombined(totalQtyUsed) for the correct classroom and the ones
without a match I want to be zero.
I know I can cycle through each record in a VBA recordset but isn't this
something an update query should be able to do?
Thank you,
Frederick Wilson
I have toyed and played with the following without success. I know I can
do what I want to do with a recordset, but it seems like this is
something a query can do.
Primary Table of data: UsageHistory (UseDate, CL_RM, QTY_Uses)
So in this table we have a running history of the days a classroom is
used and the number of users, students and/or attenders if you will. A
classroom may have more than one use in a date.
Table: ClassroomDesc (CL_RM, BLDG, QtySeats, Projector)
CL_RM is the PK with all the classrooms listed
Table: tempTBL_UseCombined (FromDate, ToDate, NumDaysUsed,TotalQtyUsed)
Via VBA I run some scripts to empty this table, then I insert all the
classrooms that have had use, total days in use, the date from and to.
Then I add the rest of the Classroom that are not already there with the
qty of zero for days used.
Here is where I need help.
From the UsageHistory table I total the QTY_Uses between the date
ranges. All is well to this point.
Now I want to enter the total data into
tempTBL_UseCombined(totalQtyUsed) for the correct classroom and the ones
without a match I want to be zero.
I know I can cycle through each record in a VBA recordset but isn't this
something an update query should be able to do?
Thank you,
Frederick Wilson