update a table using query - please help

[djsmarties]

HI,

i have a query that calculates some numbers. now in order to use this query i need to get the data into a table in access.
the query will be run everytime i start a report (because the report uses the table as source).

so if i use an append query, i get all the new data, but it won't update the existing data in the table. if i use an update query, my it won't work at all, because access says i can't use an update query if it has any calculated fields in it.

Is there any solution to this problem, maybe work around it somehow?
i hope i explained it good enough, if not, let me know. thanks

 

[Anne Troy]

As I understand it:

1. You've got a report that is based on table(s).

2. You need to create calculations in a query.

Why update the table? Make it a select query, that's all. Then, copy the report (if you still need to use it on tables) and change the data source of the report copy to the query instead of the table.

If you're getting an error when you do that, then something else is wrong.

 

[djsmarties]

okay i guess i have to do more explaining, this is quite complicated.

i have several reports that use a select query as source. thies query is huge and has many parameters. also, it is based on many tables. this works fine.
I also have some queries that calculate things. they have to be combined with the huge "masterquery". now whenever i add the calculating queries to my masterquery, access totally freezes. i have no idea why.
so i decided to have the calculating query transfer its calculations to a table and then use the table in my masterqery. it worked no problem.

so now i have the problem, that the calculating query is an append query that only append new datarows but doesn not update old data. i would like to update the table. using an update query doenst work, as described earlier.

i absolutely have to use the masterquery for the reports, and also this is a database that will be used by people who just want to press buttons, so verything has to be as automatic as possible.

hope this makes things clearer

 

[Bariloche]

Sounds to me like you just want to have your "calculation" query be a Make Table query. This way the table is created every time. The down side of this is that the database gets bloated with old stuff.

You may want to compact your database even now. That certainly won't hurt things.

Have you picked up a book on programming in Access? A lot of these techniques will be more easily followed by working from a book.


enjoy

 

[Corticus]

Hi,

A couple of things. You should be able to update a table with an update query that contains calculations. I've done this many times and I don't know of another way to perform calcs on a table that already has data in it. I believe thats the point of an update query. I suspect maybe there's a problem with the query. Update queries seem kind of backwards to me(the whole 'update to' being where the data comes from) so you might try playing with the query a bit.
If you're still having trouble, I like the idea of using a make table query as posted above. I don't think 'bloating' will be too big an issue, as the query overwrites the table everytime.
And yes, an append query is only for adding data to a table so that would not be what you want.
If you want to send a copy to me(take out the data if its sensitive), I'd be happy yo look at it, but since you've described it as a huge query, I don't know how much help I'll be!

cchilldon@mfaaa.org

Good luck,

 

[djsmarties]

Okay i'll give it a shot and report back later. thanks.