Merging a table and a query

[roopakv]

I have a table CREP and a query [CREP DETAILS].
Basically i want to update the data on the table CREP based on the data in the query [CREP DETAILS].
Both the table and the query have a common column, that is date.
Crep has a mins and crep details has a totmins column.

What I want to do is update the value of mins in CREP to value of totmins in CREP DETAILS where CREP.DATE=[CREP DETAILS].DATE

I have been working onthis query for quite some while now and inner joins dont seem to work and i get the error use an updateable query.

I am a newbie to Access so i require help with this one.

[hyperpau]

I think you almost got the answer already.

From the looks of it, you are trying to update the query joining the table and the query itself.

My suggestion is to try to create an Update query and bind this to the table using the Date as the Criteria and the Mins column to be the one updated.

You can use Access help to find out how to create Update queries.

[Stewart Ross]

Hi. Try HyperPau's suggestion and see how you get on. However, I think you will find that the result is still not updatable, as a result of the totalling. Access is very limited in what it considers updatable in such circumstances - see this Microsoft Knowledgebase article for details. Using aggregation - totals and so on - renders a query non-updatable, and even if you join that to another table the same thing applies - which is very frustrating. The Knowledgebase article suggests using domain aggregate functions - DCount, DMax and so on - but these can be very slow for large queries.

I get round this by taking a two-stage approach. First, use the aggregating query (the one doing the totalling) as a Make Table query to write a temporary table to the database, then create an update query by joining the temporary table and the table you want to update. Tables joined to tables without aggregation are always updatable.

If this works out for you, and you need to do this kind of update often, you can always set up an Access Macro to run the two actions sequentially. That way there is no possibility of forgetting one or other step.

-Stewart