Updating one of 3 flds based on detail

[bjwiz]

I need to update one of three fields in an Access master file based on content of a detail field with a detail amount field. Will use Access SQL or QBE screen.

Also is there a way to vary the field name within an SQL stmt with the contents of a field?

Thanks for the HELP!!

 

[JerryDennison]

Are you saying you want to store some type of calculated result from one table/field to another table/field?

As for using variable field names using SQL. This can be done as well. Simply don't enclose the variable in quotes. Whatever value is stored in the variable will be used.

 

[bjwiz]

Very sorry I wasn't clearer!! Details contain a type field and an amount field. The master has 12 bucket fields. Depending upon the detail type the amount is posted/updated to the correct bucket. How do I write an SQL update statement to do that? THANKS

 

[JerryDennison]

What do you mean by 'update to the correct bucket'? What are you updating? Are you merely transferring the value of a field in one table to a field in another table?

 

[bjwiz]

A visual is worth a thousand words:

DETAILS: Type Amt
rec 1 A 10.00
rec 2 B 5.00
rec 3 C 12.00
MASTER: Fld-A Fld-B Fld-C
rec 1 10.00 5.00 12.00

Common keyfield for Details & Master would be a Cust#. Read three details moving the amt to the correct Master field. Write Master with change in Cust#.

 

[JerryDennison]

There is no reason whatsoever to duplicate data within your db. What you have is called anomalous dependency. This can cause all manner of problems and is not necessary. Since you already have all the information necessary to define the fields you don't need to restore them in your so-called master table. This violates the Second Normal Form of the Forms of Data Normalization. If you wish to display the information in the format it would have been stored in the master table you need only use a Crosstab query. I strongly recommend that you do not pursue this course of action.