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]

Okay, thank you. It might take me awhile to implement it, but I'll let you know if I have any problems.

 

[thepasmiths]

I'm having a problem on step 2, finding the duplicates.

Can you walk me through the process?

 

[thepasmiths]

New Data:

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	3	548​	12.99	4/6/2023	2
ABC Company	152946	10	548​	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	26529	1	24C-032	7.99	4/22/2023	1.55
HGI, Inc	26530	1	32C-024	8.99	4/22/2023	1.75

 

[Joe4]

Do you how to do a matched query in Access?
You need to join the two table on the field(s) that identify the unique records (may be one of more fields).
You could then try to identify other field values that might have changed through a series of criteria of each field (i.e. Field1 from Table1 not equal to Field1 from Table2), or simply just overwrite the whole record with the new values.

 

[thepasmiths]

No, I am having problems with the matched query.
You combine both tables (append one to the other), and then do a find duplicates query?
Is it going to be a problem when the invoice number is repeated?

 

[Joe4]

You combine both tables (append one to the other), and then do a find duplicates query?

No, that is not how you do a find duplicates query.
You compare Table A to Table B, joining them on the fields which define a unique record.

In a real simple example, if you had Table A that had two fields, ID and Value, and Table B that also had two fields, ID and Value, and you wanted to find all records where the Value was different, you would:
1. Create a new query
2. Add TableA to the query
3. Add TableB to the query
4. Join TableA to TableB on the ID field
5. Add a criteria condition where [TableA]![Value] <> [TableB]![Value]

This would return all the records where the Value field changed for Users.

If the data on the new record is always correct and complete, you don't even need to add a criteria.
You can simply do a matched query following steps 1-4 above.
Then make it an Update Query, updating all the fields to the values from the new record.
Then the new data will overwrite all old data for matching records.

 

[thepasmiths]

what if there isn't a field that defines a unique record?
But, rather a combination of fields? Invoice Number is repeated from one record to the next, but a secondary field (Line Number), that would then differentiate this record from the last.
Problem being that the secondary field is not unique unto itself, but relies on the primary field to differentiate.

Invoice Number 1 Line 1
Invoice Number 1 Line 2
Invoice Number 1 Line 3
Invoice Number 2 Line 1
Invoice Number 2 Line 2

 

[Joe4]

Like I mentioned in my previous posts, you can join on multiple fields. However many it takes to create a unique record!

The only time that is a problem is if they insert or switch Line numbers between files (i.e. something like if Invoice Number 1 Line 2 on one file really lines up with Invoice Number 1 Line 3 on the other file).
If that can happen, that is a problem (there is only so much you can do with bad data!)