Updating one table with another 1

[mraetrudeaujr]

I have two databases where there is redundant information. In other words, instead of just creating new/additional fields, the user created a new database with a table containing several fields from the previous Main table, and the new 'additional' fields.

Since the tables contain many of the same fields that identify the same 'person', I need to copy (only) the information that is coming from this smaller table, into the Main database/table. I have already created the new fields in the Main table EXACTLY like they are in the other table. I tried to create an "Update" query to copy this information into the new fields Main table, but it's not working. I need a way to pull the information from the smaller table that pertains to the appropriate person, into the larger Main table. How do I go about this?

 

[Golom]

The general approach is

UPDATE OldTable INNER JOIN NewTable
       ON OldTable.KeyField = NewTable.KeyField

SET    OldTable.Field1 = NewTable.Field1,
       OldTable.Field2 = NewTable.Field2,
       ... etc. ...

 

[mraetrudeaujr]

So, you're saying that I will have to plug in my table names (bracketed...because there are spaces?) and ALL of the fields from both tables? Or just the pertinent fields that will be integrated into the Main table?

 

[Golom]

In an UPDATE statement you must explicitly specify each field that you want to change. If you intend to, in essence, replace each record in your old table with the corresponding record from the new one then it may be easier to delete matching records from the old table

DELETE * From OldTable
Where KeyField IN (Select KeyField From New Table)

And then append everything in the new table.

INSERT INTO OldTable (fld1, fld2, fld3, ...)
Select * From NewTable

 

[Lilliabeth]

I think you want an Append query. Look it up in Help.

 

[Lilliabeth]

My bad; I misunderstood the issue.

that pertains to the appropriate person

 

[mraetrudeaujr]

Lilliabeth,

no, an APPEND query would just 'add' records to the end of the table, right? I only want to add the information contained in the fields from one table, into newly-created fields in the Main table...you know, filling in the blanks. For example, let's say my Primary Field is "PRSLOG", well, I don't want to create a new "PRSLOG" number for the same event/person, right? Therefore I need to integrate just the fields that I don't have, in the Main table, and bring THAT information into these new fields. The current records contained in the Main table should only be added to -- not changed.

Golom,

Here are my table names and fields:

Main table - "2005 LOG" (this is the table I want to integrate the fields and accompanying information INTO)

'old' table - "2005 CRIMINAL LOG" (this is the table that contains the fields and information that I'm copying FROM)

The new fields for the Main table are:

ProsecutionCharge2
CourtDisposition
DocketNumber
SentencingDate
SentenceInMonths
SupervisedRelease
CourtRemarks
Last3DigitsOfANumber


What is puzzling is that I had another table that contained the same basic information as these two (e.g. PRSLOG, ANumber, etc.) and I was able to use an update query to copy this information into the Main table. I tried to go back and change the table names/fields and though it stated that it updated these new fields in the Main table, it didn't.

 

[Golom]

OK. Then

UPDATE [2005 LOG] As Old INNER JOIN [2005 CRIMINAL LOG] As New
       ON Old.PRSLOG = New.PRSLOG

SET    Old.ProsecutionCharge2   = New.ProsecutionCharge2,
       Old.CourtDisposition     = New.CourtDisposition,
       Old.DocketNumber         = New.DocketNumber,
       Old.SentencingDate       = New.SentencingDate,
       Old.SentenceInMonths     = New.SentenceInMonths,
       Old.SupervisedRelease    = New.SupervisedRelease,
       Old.CourtRemarks         = New.CourtRemarks,
       Old.Last3DigitsOfANumber = New.Last3DigitsOfANumber

There may be reasons for fields not being updated including
- Mismatched data types
- Target fields having unique indexes
- Relationships being defined requiring the value be present in some other table

 

[mraetrudeaujr]

Golom,

Thanks. Before I was able to copy and paste your SQL code, I manually tried to do it (again) in the Query design grid. For whatever reason, it worked! However, you can bet that I took your build of my information and saved it as a 'How to' document. I've learned to 'journalize' all of the code information that I have received from my Posts. I'm sure that many of the experts here in these forums loathe 'newbies' reposting questions over and over. I personally search the forums looking for something that might work for my needs, BEFORE posting the question. Again, thanks for the assistance. Here's your star!

 

[Golom]

Thanks for the star ... and if we really did "loathe" it you probably wouldn't get any answers.

It does occasionally get a bit tiresome when the same person repeatedly asks the same question but anyone who's putting in the effort to search for solutions and study the responses as you are, qualifies as a fellow professional that we like to help.