Update or Make Table Query??? 1

[kvest]

Hello,
I have a database with a predominate table that started with about 6 fields . I now have 7000 records in it and additional data related to the existing records has become available from another database via import from Excel.

How do I "update" the existing records with the new fields (about 12) from the Excel file? The primary key on the table exists in the spreadsheet as a distincitive number. I have been able to apphend my database records with records that previosly didnt exist, but getting these "new" fields on existing records to update is beyond me. The excel data now exists as another table, but merging them together is bmy problem.

This is a function that I (or someone else) will need to do on a weekly or biweekly basis, since my data entry is still limited to the original 6 fields on the form.

If this info exists already on this forum, please advise the correct terms to search for as my Query knowledge is limited.

Thanks

 

[PHV]

The excel data now exists as another table
Say this table is named ExcelTable.
Create an update query like this:
UPDATE PredominateTable A INNER JOIN ExcelTable B ON A.PrimaryKey = B.DistinctiveNumber
SET A.NewField1 = B.SomeField1, A.NewField2 = B.SomeField2, ..., A.NewField12 = B.SomeField12

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kvest]

Thanks...

I have gotten this far and am getting syntax error in the ON line. Either the book I have doesnt cover this well or I am missing something. Below is what I have...

UPDATE Incident Approval
INNER JOIN [Import]
ON Incident Approval.Call Number = [Import].Call Number
SET [Incident Approval].Inc Time = [Import].Inc Time, [Incident Approval].Address = [Import].Address, [Incident Approval].Day of Week = [Import].Day of Week, [Incident Approval].Written = [Import].Written, [Incident Approval].Stat = [Import].Stat

 

[PHV]

Which version of access ?
I have been able to apphend my database records with records that previosly didnt exist
How you did that ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kvest]

Access 2002

 

[Golom]

Table names containing spaces require brackets around them

UPDATE  [COLOR=red][[/color]Incident Approval[COLOR=red]][/color]
INNER JOIN  [Import]
ON [COLOR=red][[/color]Incident Approval[COLOR=red]][/color].Call Number =  [Import].Call Number
SET [Incident Approval].Inc Time = [Import].Inc Time, [Incident Approval].Address = [Import].Address, [Incident Approval].Day of Week = [Import].Day of Week, [Incident Approval].Written = [Import].Written, [Incident Approval].Stat = [Import].Stat

 

[kvest]

PHV- I didnt see your second question In regard to apphending the database with other records. I created an Apphend Query which brought in and created the records that wernt there before.

In general, the original database tracked only written reports of incidents. I am trying to change that so that it tracks ALL (Written and Non Written) records. All records (written and non written) are exported from another datbase into excel. I have been importing that data into a table then apphending my primary table (Incident Approval) which brings in all the non-written records. The problem I am having is getting the new fields on the already entered Written records to update with the data that is available from the other database.

 

[kvest]

Golom and PHV,
Thanks a ton...I got it working now....I guess that is another reason to not put spaces in....Thanks again.