By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,336 Members | 2,307 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,336 IT Pros & Developers. It's quick & easy.

Merging a table and a query

P: 1
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.
Aug 19 '08 #1
Share this Question
Share on Google+
2 Replies


hyperpau
Expert 100+
P: 184
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.
Aug 19 '08 #2

Expert Mod 2.5K+
P: 2,545
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
Aug 19 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.