UPDATE query with many fields

[Deutz]

Hi and thanks in advance,



I am using Access 2003.

I have an UPDATE query (which crashes) where I am trying to update a table with about 50 columns from the result of a query. The field names in the query match the fieldnames in the table but the table also has a lot of other fields as well.

Is it possible to write some SQL where you do not need to explictly type in all 50 field names in the SET part of the UPDATE (since the field names match) ...

I came up with this below but of course it is not quite right:

UPDATE Revenue INNER JOIN qryRevenue ON Revenue.pk_PlanID = qryRevenue.pk_PlanID
SET (Select * FROM Revenue) = (SELECT * FROM qryRevenue) "




Thanks
Deutz

[orange]

A few things:

A table with 50 columns is a little rare. Perhaps the table is not normalized.

In an Update query you do NOT have to SET every field. You only set those fields where the values are to be changed.

What exactly is qryRevenue? How does it relate to the Revenue table?

[rpeare]

I'm just guessing here and it seems to me like you're trying to update the REVENUE field in the REVENUE table with a value from a query, but you have no established link between the query and the table. They must share a key field in order to do this. Secondly, if I'm right, why would you store a calculated value? calculated values can be reproduced in queries or reports and doing this sort of update is a prime reason why you want to normalize your database, updating summary information every time new information hits your database is a royal pain in the butt.

[Deutz]

The revenue table is just a temp table that I use along the way to getting data into the correct format for a number of reports. The reason for the temp table is that the processes is quite complex and involves many discreet pieces of data that have to be calculated and the queries are too complex to be executed in one go, so I break it up midstream with the temp table.
I have no problems with this method, my only question is whether there is a way to not have to specify each and every fieldname in the SET part of the UPDATE query.

The revenue table and the query have a unique field in common: pk_PlanID.

thanks
Deutz

[rpeare]

See orange's response, the only thing you have to SET in an update query or the fields you're going to change. Note this is different than an INSERT INTO query. If you've got your intermediary query showing you what you want you shouldn't have to do anything fancy, just make the query an append query and add it to your intermediary table. (after purging it of data first I assume)

I will agree with orange though if your calculations are that complex it's likely a sign that your database is not normalized. I've got databases that have tracked 10 years worth of financial data with grant tracking and general ledger accounting information and never run into a problem with a query that was too complex. Might be a good idea to look at normalization rules and see if you can benefit.

[Deutz]

Thanks for your replies; your points are well taken.

It may seem that the db is not normalised as there are many fields in the one table, which I don't like either, but this is dictated by the nature of our dept's reporting requirements. So I have to carry out similar calculations on each and every field and end up with a number of queries that have to be combined to get the data into a format suitable for our reports ... hence the temp table.

The first query to get the data into the temp table is an INSERT but the others that follow must by necessity be UPDATE queries, hence my question about an easy way to refer to the salient fields.

I know you can do this in an INSERT query but it looks like this is not the case with an UPDATE query so I will just have to do it the long way.

Once again, thanks for your suggestions.

Deutz

[rpeare]

If your intermediary table has data in it, it should also have a foreign key that you can use to identify updates that you want to perform. For instance let's say you're calculating bills for a client, in your intermediary table you'd have a clientID and the associated information, then when you update their information as you go along you should also have the clientID in the query you're using to update, if you do it should be a simple matter to update the intermediary table by linking it to your query through the client ID and taking information from the update query and placing it in the intermediary table.

[Deutz]

Thanks rpeare