Update Query Incorrectly Modifying Data (1 Viewer)

[ersocia]

the table that this Update Query is supposed to modify ("Automobile_Info") contains the three fields "Suite", "FirstName_1", and "LastName" (among others).

there is a linked table ("tblautoinfo") which the Update Query is getting it's modifying data from, which also contains those fields. i only list those three fields because they're the only ones relevant to my problem.

when running the Update Query, all data from "tblautoinfo" is transferred from "tblautoinfo" to "Automobile_Info"), except for one very strange occurrence:

in all 162 records being updated in "Automobile_Info", the "FirstName_1" and "LastName" fields are being populated with the suite number from the "Suite" field. i can't, for the life of me, figure out why it's doing this. i have checked, re-checked, and triple-checked the Update Query in design view, and it is absolutely laid out correctly, all fields from "tblautoinfo" being correctly mapped to their respective fields in "Automobile_Info", yet the Suite number is still replacing whatever is in "FirstName_1" and "LastName". i've tried copying/pasting the data from the "FirstName_1" and "LastName" fields directly from "tblautoinfo" to "Automobile_Info", but no changes are made when doing so. manually changing the data works fine, but once the Update Query is run, the Suite number is again populating those fields.

this is probably the strangest thing i've run into so far in my time with Access 2007.

if you need any extra information, please let me know and i'll supply it to the best of my ability, but i'm completely at a loss on this one. any help is most definitely appreciated.

 

[plog]

Can you post the database?

 

[ersocia]

Can you post the database?

unfortunately, that would require me stripping out all sensitive data about our residents from all of the tables. not to mention the fact that the linked table involved wouldn't be able to be included, and it's integral to the issue at hand.

all that aside, i'm actually at work right now, and don't really have the time to do all of that. is there any other way i can give the information necessary?

 

[plog]

Post the SQL of the update query.

 

[ersocia]

Post the SQL of the update query.

put in QUOTE tags because in CODE tags it shows as one continuous line requiring some scrolling to read it. below is almost exactly as it appears for me in SQL View.

(and as an FYI: the linked table was not created by me, nor do i maintain it, so the bad naming convention is not on my conscience )

UPDATE Automobile_Info INNER JOIN tblautoinfo ON Automobile_Info.Suite = tblautoinfo.Suite SET Automobile_Info.Building = [tblautoinfo].[Bldg], Automobile_Info.Suite = [tblautoinfo].[Suite], Automobile_Info.FirstName_1 = [tblautoinfo].[First Name 1], Automobile_Info.LastName = [tblautoinfo].[LastName], Automobile_Info.Make = [tblautoinfo].[Make], Automobile_Info.Model = [tblautoinfo].[Model], Automobile_Info.Color = [tblautoinfo].[Color], Automobile_Info.State = [tblautoinfo].[State], Automobile_Info.Registration = [tblautoinfo].[Registration], Automobile_Info.Keys_Auto_1 = [tblautoinfo].[Keys Auto 1], Automobile_Info.Comments = [tblautoinfo].[Comments], Automobile_Info.Make_2 = [tblautoinfo].[Make(2)], Automobile_Info.Model_2 = [tblautoinfo].[Model(2)], Automobile_Info.Color_2 = [tblautoinfo].[Color(2)], Automobile_Info.State_2 = [tblautoinfo].[State(2)], Automobile_Info.Registration_2 = [tblautoinfo].[Regist(2)], Automobile_Info.Keys_Auto_2 = [tblautoinfo].[Keys Auto 2], Automobile_Info.Garage = [tblautoinfo].[Garage], Automobile_Info.Date_Reserved = [tblautoinfo].[Date Reserved];

 

[plog]

I'm sorry, I can't help. I've looked at the SQL and it should be setting the FirstName_1 field correctly. The only other thing I can suggest is look at tblautoinfo for those records to verify that the First Name 1 and LastName fields do not actually contain the Suite field's value. Maybe the data itself is messed up in tblautoinfo.

 

[ersocia]

I'm sorry, I can't help. I've looked at the SQL and it should be setting the FirstName_1 field correctly. The only other thing I can suggest is look at tblautoinfo for those records to verify that the First Name 1 and LastName fields do not actually contain the Suite field's value. Maybe the data itself is messed up in tblautoinfo.

i certainly appreciate the effort

i did check tblautoinfo, and the information in there is correct.

however, i did also notice something about tblautoinfo: the [First Name 1] and [LastName] columns are lookup columns. that's the only difference/inconsistency i can see. maybe the update query can't pull that info correctly?

 

[jdraw]

I think you have found the issue,

See this for the rationale of NOT USING LOOKUP FIELD IN TABLES.
http://access.mvps.org/access/lookupfields.htm

 

[ersocia]

I think you have found the issue,

See this for the rationale of NOT USING LOOKUP FIELD IN TABLES.
http://access.mvps.org/access/lookupfields.htm

thanks for the link. however, as i previously stated:

and as an FYI: the linked table was not created by me, nor do i maintain it

i didn't create the DB that the linked tables are stored in. i'm basically recreating it from scratch elsewhere, fixing the unbelievable amount of poorly designed forms/queries/reports/tables, not to mention weeding through a mass of 'one-off' queries and reports that users were creating to fulfill a need once, and not deleting them afterwards.

seriously, it's a big, big, big mess.