Append specific fields only?

[eforce]

Issue: I have 3 fields which all together make up the primary key

Lets say I have fields: [A] [C] & [D]

fields AB&C make up the primary key. Now, I wish to update the data in field [D] and this record already exist.

Is there a way and if so, how would I only update the data that has only changed. leaving the primary keys intact.

I realize that I could probably remove the primary key from [A] & [C] to allow these keys to accept duplicates but I can't have duplicates because [A] is Month, is Year, & [C] is [Unit_ID_Code].

If I have Month, Year & UIC already stored and the Company identified by UIC was wants to simply change the data in field [D] for a specific Month & Year.

How can I accomplish this.

 

[eforce]

Re: update query

How would I accomplish this with an update query. I tried to use that route. I get the updates from my clients via an export from their table in excel format. I wish to only update specific fields.

 

[Jon K]

Since you are not changing any value in the primary key fields, you don't need to worry about the primary key. Just tell Access which records to update and to replace which fields with what.


Assuming you have received from UIC A001 the following spreadsheet:-
Month -- Year --- NewValue
Jan ---- 2002 --- xxx
Feb ---- 2002 --- yyy

and have imported it into Access as table "UIC_A001" and have also made sure their field types are the same as those in the Master table,

you can now open a new query, add the tables Master and UIC_A001,
drag the fields Month and Year from Master to UIC_A001,
choose menu Query, Update Query.

Switch to SQL View and you will see that the tables have been properly joined by Month and Year:-

UPDATE Master INNER JOIN UIC_A001 ON (Master.Year = UIC_A001.Year) AND (Master.Month = UIC_A001.Month) SET;


Now delete the semi-colon and add:-
Master.D = UIC_A001.NewValue WHERE Master.UIC="A001"

so that the whole Update Statement reads like this:-

UPDATE Master INNER JOIN UIC_A001 ON (Master.Year = UIC_A001.Year) AND (Master.Month = UIC_A001.Month) SET Master.D = UIC_A001.NewValue WHERE Master.UIC="A001"


When the query is run, the values in field [D] of Jan and Feb 2002 for UIC A001 in the Master table will be replaced with the NewValues from table UIC_A001.

If you need to update more than one fields, just add them to the SET clause, separating each with a comma like this:-

SET Master.D = UIC_A001.NewValue, Master.E = UIC_A001.NewValue2


Hope it helps.