Importing Data

[thepasmiths]

I get a spreadsheet each week that has multiple invoice numbers and each invoice number might have multiple line numbers.
I have imported the data into a table.
Is there a way to import and update the data each week? prices and dates, etc might change week to week.
I have other fields in the record that I don't want to change.

 

[thepasmiths]

I have a Query showing the most recent table's invoice number and line number while showing the fields from a previous table, but it doesn't show all of the records from the current table. It only shows the records that have entries from the previous table.

 

[Joe4]

Welcome to the Board!

Why not import all the records into the SAME table?
You can use queries to filter our what you need, for any purpose.

 

[thepasmiths]

I would like to import them into the same table, but I haven't figured out how to overwrite the existing data.

 

[Joe4]

I would like to import them into the same table, but I haven't figured out how to overwrite the existing data.

If you want to get rid of the old data first, you would typically run a Delete query first, to clear out the table.

If there is a combination of old records being updated, and new records being added, I would typically import to an empty temporary table first, and then run two actions queries off it:
- one query that matches between your temporary table and your final table, and does an Update Query to update existing records
- one query that does an unmatched query between your temporary table and your final table, and does an Append Query to add the new records

If you aren't opposed to a little bit of coding, you may be able to do both in a single action known as an "Upsert Query".
See: MS Access - Upsert Query Inserting All Data (Including Duplicate)

 

[thepasmiths]

I have old records being updated, new records being added, and some old records being taken off the list.

 

[Joe4]

I have old records being updated, new records being added, and some old records being taken off the list.

OK, then see my second suggestion.

Regarding removing records off of the list, how are those identified? If it is just old records in the table that aren't on the new import file, you could do an unmatched query between the data table and temp table and do a Delete Query on those.

However, if that is the case, it sounds to me that maybe the new file may have all the data you need, in which case that would be very easy. Just wipe the data table clean before importing the new data.
If that is not the case, then what exactly are you keeping from the old records?

It is kind of difficult to advise you without understanding the full scope here. So it may be helpful to show us a simple example of your existing table with old data and your new file, and explain/show examples of:
- what is being kept
- what is being added
- what is being changed
- what is being deleted

 

[thepasmiths]

Company	Invoice Number	Line Number	Part Number	Price	Due Date	Cost	Notes
ABC Company	152946​	1​	HE1903	1.99​	3/31/2023​	0.52​	Attach to part 31644
ABC Company	152946​	2​	31644	14.99​	3/31/2023​	2.56​	Shipped 3/21/23
ABC Company	152946​	3​	00548	13.99​	4/6/2023​	2.25​
ABC Company	152946​	10​	00548	6.99​	4/22/2023​	1.7​
DEF Company	29468​	1​	M25564-124	8.99​	4/16/2023​	1.9​
DEF Company	26492​	6​	H54225	8.25​	4/1/2023​	1.6​
HGI, Inc	26492​	1​	24C-032	7.99​	3/31/2023​	1.55​	Shipped 3/21/23

 

[thepasmiths]

Here is a mock sample. But, each week the price, due date and cost might change, while the other fields would remain. Cost and Notes would be our entries and not the customers. Next week some records may drop off, while others are added. Regarding removing records off of the list, yes, it is just old records in the table that aren't on the new import file. The new data would not have certain fields (Notes, Cost, etc) that are my information only.

 

[Joe4]

OK, then I would do with the three pronged approach I talked about.
Have a blank copy of your data table that you import new data into (we will call this our "Temp Table").
Then:
1. Do an unmatched query to show all records in your "Temp Table" that do not have matching records in your data table. Then, set this up to be an Append Query to add these records to your data table.
2. Do a matched query from your Temp Table to your data table, and update key fields that may change by making this an Update Query.
3. Do an unmatched query to show all records in your data table that are not in your Temp Table. Then set this up to be a Delete Query to remove those records from your data table.
4. When finished, clear our your Temp Table (with a Delete Query) to prepare for the next run.

Once you have these 4 queries set-up, you can run them all with a single click by adding them to a Macro that runs each one.