Update or append query?

[Bryan]

I have a table with 3 fields that is updated by a User Form:
Class (scheduled or unscheduled)
Code (mechanical, electrical, production)
Duration (1 hour)

I would like to run an update query that will copy the Duration into another
table and place the duration into the appropriate categories:
Scheduled Mechanical
Scheduled Electrical
Scheduled Production
Unscheduled Mechanical
Unscheduled Electrical
Unscheduled Production

so that I can maintain a running total of Durations based on Class and Code.

Any help would be greatly appreciated.

 

[Phil Smith]

You are looking at an append query.

You will need to create the new table with two fields, ClassCode and
Duration.

Using the Query Grid, build your append query appending Duration to
Duration, and appending:

ClassCode:

&" "&[Class]
to Class Code.

This will create a single field that contains what you are looking for.

Of course, I don't know why you need to do this.  You could easily
arrange it so that your user form automatically went to the end of the
existing table and added a new record, so that original table would keep
your running data, and you could get the data out in the format you wnat
using that same field,

ClassCode:[Code]&" "&[Class]

As a field in a query against it.

Phil

 

[John Spencer]

It is usually bad idea to put calculated information into a table. A
simple query should do this.

SELECT Class, Code, Sum(Duration) as TotalDuration
FROM YourTable
GROUP BY Class, Code

Add your table to a new query
Add Class, Code, and Duration to the field
Select View: Totals from the menu
Change Group By to Sum under duration.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================