Update Query, where to begin?

[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

 

[Fred Wilson]

I am not sure what happen here. It did not post right.

 

[Fred Wilson]

trying to post again

I am not sure what happen here. It did not post right.

 

[Steve Schapel]

Fred,

I would expect an Append Query rather than an Update Query. Can you
give details of how you are doing it at the moment, i.e. SQL of the
query used, or code or whatever, to explain what you mean by "insert all
the classrooms that have had use", and "add the rest of the Classroom"?
Thanks.