Updating a table

[uglydork]

1. My database has table 27.
2. Our company updates table 27 in an different database once a month not linked to my database.
3. My table 27 has some added fields such as dates and other items we use.
4. I need to update my table 27 once a month with the new data from my companies table 27.

How do I add only new information from their table 27 to my table 27 while retaining all my original information?

Thank you.

 

[MarkK]

How I do things is mostly determined by my skill level. What do you know how to do in respect to updating data in tables? Can you write queries? Open recordsets? Both of those might do.
Why can't you just copy their whole table 27? Why do all these tricky updates?

 

[PaulO]

One solution, as suggested above, is simply to copy across the whole table? But I guess this would potentially eliminate the additional data you've added in the extra fields?

So, another approach is:-

1. Write a SELECT Query comparing Companies table to Your table and finding records in Companies table that do not exist in Your table
   • This is achieved by setting a left outer join on the key matching fields (e.g. Record Reference) and setting IS NULL criteria on the Key Field in Your table
   • You'll then be able to preview/audit/sensetest any new records using this query
2. Once you are happy you can use the same SELECT query as the data source to create an APPEND query to Your table

 

[uglydork]

Thank you for the reply. I built this database last year and self taught. It has worked fine until I needed to update these records.
I can write queries but I do not know how to program. I am smart enough to copy and paste code if I have needed it.
I can not just drop the table on the table I created as we are still working data off my table and I do not want to lose that information. The new table also doesnt have the fields I need that I have added.

I just need the updated information. I am unfamiliar with an left outer join?

 

[PaulO]

You can successfully achieve your goal using standard Access Queries (no VB programming required).

The key to building the query in the first place is making sure that the two databases are linked in the first place (File > Get External Data > Link Tables) so if this isn't already done then do this first.

Using a left outer join, a query includes all of the rows from the first table in the SQL statement FROM clause, and only those rows from the other table where the joining field contains values common to both tables. Using the IS NULL criteria on the key field in the joined table effectively restricts the query output to NEW/DISTINCT records in the other.

In practical terms, if you double-click the JOIN select Option 2 from the Join Properties this will show an arrow from one table to the other.

 

[uglydork]

Thank you Mr.PaulO-

I now have my tables linked. Great,that is something I needed anyway.
So I will assume I build an append query and link all my common fields?

You can successfully achieve your goal using standard Access Queries (no VB programming required).

The key to building the query in the first place is making sure that the two databases are linked in the first place (File > Get External Data > Link Tables) so if this isn't already done then do this first.

Using a left outer join, a query includes all of the rows from the first table in the SQL statement FROM clause, and only those rows from the other table where the joining field contains values common to both tables. Using the IS NULL criteria on the key field in the joined table effectively restricts the query output to NEW/DISTINCT records in the other.

In practical terms, if you double-click the JOIN select Option 2 from the Join Properties this will show an arrow from one table to the other.

 

[PaulO]

1. First, write a standard SELECT Query comparing Companies table to Your table and finding records in Companies table that do not exist in Your table
   • This is achieved by setting key/unique matching field(s) with a left outer join (e.g. Record Reference)
   • Select all fields from Companies table plus bring in the Key Field(s) from YOUR table
   • Use IS NULL criteria on the Key Field(s) from Your table
   • You'll then be able to preview/audit/sensetest any new records using this query
2. Once you are happy you can save and cannibalise the same SELECT query as the data source to create an APPEND query to Your table

 

[uglydork]

I believe this fixed it! Thank you so much for your help.

1. First, write a standard SELECT Query comparing Companies table to Your table and finding records in Companies table that do not exist in Your table
   • This is achieved by setting key/unique matching field(s) with a left outer join (e.g. Record Reference)
   • Select all fields from Companies table plus bring in the Key Field(s) from YOUR table
   • Use IS NULL criteria on the Key Field(s) from Your table
   • You'll then be able to preview/audit/sensetest any new records using this query
2. Once you are happy you can save and cannibalise the same SELECT query as the data source to create an APPEND query to Your table

 

[PaulO]

My pleasure! I've had so much help given to me personally on this site so it's nice to help someone in some small way!

 

[uglydork]

I spoke too soon. When I run the append, my original table has the same amount of records as before the append. It appears that when I ran the append to table 27 it didnt add the new records?

 

[PaulO]

When you run an APPEND query you get an interactive message box telling you that so many new records will be created and asking you whether you wish to proceed. Thereafter, if some records fail to append you would be advised, else it will confirm how many records were appended.

So, I reckon perhaps that at this stage you have correctly created the SELECT query but not created and saved the additional APPEND query?