Finding non duplicates, keeping one with amount

[PondWaters]

Completely new to Access, so please bear with me.

I have 2 different files each of around 120k lines (240k total lines). These files are sent twice a month, and of the 240k total lines, around 225k are duplicates. Is there a way for me to erase one set of the duplicate data, and then the rest keep only the latest line of data?

The bear is that only one piece of data is different. So for example, here is what it comes in as:


Sheet1

*	A	B	C	D	E	F	G	H	I
1	Field1	Field2	Field3	Field4	Field5	Field6	Field7	Field8	Field9
2	D	123456789	123456789-1	Smith	Bob	20150501	20150531	$12.00	A
3	D	987654321	963258741-1	Smith	Carol	20150501	20150531	$14.00	A
4	D	147258369	878965412-1	Jones	Fred	20150501	20150531	0	B
5	D	369258147	8795412-2	Jones	Erica	20150501	20150531	$8.00	C
6	D	321654987	3251148-1	James	Tom	20150501	20150531	$25.00	C
7	D	357147159	32584415-6	James	Robert	20150501	20150531	$19.50	B
8	D	123456789	123456789-1	Smith	Bob	20150501	20150531	$12.00	A
9	D	987654321	963258741-1	Smith	Carol	20150501	20150531	$14.00	A
10	D	147258369	878965412-1	Jones	Fred	20150501	20150531	$33.00	B
11	D	369258147	8795412-2	Jones	Erica	20150501	20150531	$8.00	C
12	D	321654987	3251148-1	James	Tom	20150501	20150531	$25.00	C
13	D	357147159	32584415-6	James	Robert	20150501	20150531	$19.50	B

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:70px;"><col style="width:82px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


I would want to see something like this, where the duplicates are removed, and if something came in twice and did not match, only the last piece of data would show.

Sheet1

*	A	B	C	D	E	F	G	H	I
34	Field1	Field2	Field3	Field4	Field5	Field6	Field7	Field8	Field9
35	D	123456789	123456789-1	Smith	Bob	20150501	20150531	$12.00	A
36	D	987654321	963258741-1	Smith	Carol	20150501	20150531	$14.00	A
37	D	147258369	878965412-1	Jones	Fred	20150501	20150531	$33.00	B
38	D	369258147	8795412-2	Jones	Erica	20150501	20150531	$8.00	C
39	D	321654987	3251148-1	James	Tom	20150501	20150531	$25.00	C
40	D	357147159	32584415-6	James	Robert	20150501	20150531	$19.50	B

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:70px;"><col style="width:82px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4



Any help would be appreciated, I am trying to learn Access, but I am running into a wall on this one.

 

[Joe4]

You would probably use an Aggregate (Totals) Query to do that. If you are not familiar with those, try a Google Search. There are lots of tutorials out there.

if something came in twice and did not match, only the last piece of data would show.

You just need to determine how "last" is identified. Remember, the order of the records in the table really has no meaning. Someone once gave an accurate description to think of an Access table as a "bag or marbles"; the records are all jumbled around and have no meaning.

Now, if you use an Autonumber field in your table that increments, then you could easily identify the "last" record by taking the "Max" of value of this Autonumber field for each Grouping.

 

[PondWaters]

Thank you, I'll take a look at the aggregate query. Saying last is not necessarily the best way to describe it, if something comes across as 0 on the first file, on the second file it is most likely going to be >0. If it is 0, that would just be a duplicate anyway.

 

[Joe4]

Sounds like then in your Aggregate Query, you want to Group By Fields 1-7 and Field 9, and then take the Max of Field 8.
I think that will return what you are looking for.