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

 

[Guus2005]

yes, there is an easy way to do this.
it is called database normalisation.

apparently you have a normalisation issue. you need to rethink your database structure and perhaps add some relationships. that would be the best way to fix your problem.

to generate such a query you can use vba to walk thru all the table fields and create the query you need.

some pseudo code:

for each fld in currentb.tabledefs("table1")
    strReturnValue = strReturnValue & fld.name ...
next fld

HTH

 

[Deutz]

Thanks Guus2005,

I'll take your normalisation advice on board and thanks for the code.


Deutz