Query to find duplicate records

[hawg1]

Hi,

I have imported an excel table into access succcefully. The records contain the following fields: NIIN, FSC, SD, NET QTY, NRTS QTY, Credits, Net, and Noun. Each record has a very unique NIIN. I have written a query to find all duplicate NIIN records and sum up fields NET QTY, NRTS QTY, Credits, Net respectively then append the summed up records to a different table. The query works fine at first.

After analyzing the results, I noticed that there are some NIIN records in which all the data is a duplicate except for the Noun field. Based on the NIIN, the Noun fields should be identical, but due to typos when entered into excel (data received from external source), they are not and thus those records arenot considered duplicates. In reality they are duplicate records.

Is there a way, by using a query, to locate duplicate records by the NIIN field (but not include the Noun field), sum up fields NET QTY, NRTS QTY, Credits, Net and append the summed up record to a new table that does includes the Noun field?

 

[vbaInet]

Wouldn't it be much wiser to find those typos and get rid of them? Unless you aren't 100% sure they are typos.

 

[hawg1]

Wouldn't it be much wiser to find those typos and get rid of them? Unless you aren't 100% sure they are typos.

I don't control how the data is entered into the excel worksheets. They come from a myriad of external sources. Here is what I am alluding to; both of these entries, based on the NIIN field, are identical items (the dashes represent column breaks); the Noun field in one record is truncated for some reason:

FSC --- NIIN ------- SD --- Net QTY -- NRTS QTY -- Credits -- Net ------ Noun
1650 -- 10281115 -- MSD -- 6 --------- 6 ---------- $0 ------- $90,247 -- MANIFOLD ASSEMBLY,H
1650 -- 10281115 -- MSD -- 3 --------- 3 ---------- $15,041 -- $45,123 -- MANIFOLD ASSEMBLY,HYDRAULIC

What I wish to do is combine these two records into one, with the Net QTY, NRTS QTY, Credits and Net columns summed up but also insert one of the two Noun fields so that it would look like this:

FSC --- NIIN ------- SD --- Net QTY -- NRTS QTY -- Credits --- Net ------- Noun
1650 -- 10281115 -- MSD -- 9 --------- 9 ---------- $15,041 -- $135,370 -- MANIFOLD ASSEMBLY,HYDRAULIC

Any ideas?

 

[Voltron]

I'm not sure how to do it in Access, but you could export all of the original information into Excel again and fix the typos there or combine the information as you see fit. You could then import the data back into Access.

 

[vbaInet]

It's probably truncating because the text is too long or (possibly) has carriage returns within the text in Excel. How many characters are there in the Notes cell in Excel? Also what's the Field Size property set as in the Notes field in the table in Access?

You can certainly get that final result. What you need is a subquery to generate each of the merged fields. Here are some links on using subqueries:

http://allenbrowne.com/subquery-01.html
http://www.techonthenet.com/oracle/subqueries.php

The idea is:

1. You create a query to return DISTINCT NIIN records.
2. The query should be GROUPED BY FSC followed by NIIN and ORDER BY those two fields.
3. You would now use the subquery as aliased fields to get NetQTy, NRTS_Qty, Credits and Net.
4. For the Notes field, you would still use a subquery but you need to sort the records in ASC by FSC, NIIN and then in DESC by Net. Then use TOP 1 to get the last Note.

OR

4. Use a DLookup() function to get the last Note for that NIIN:

http://www.techonthenet.com/access/functions/domain/dlookup.php

 

[hawg1]

It's probably truncating because the text is too long or (possibly) has carriage returns within the text in Excel. How many characters are there in the Notes cell in Excel? Also what's the Field Size property set as in the Notes field in the table in Access?

You can certainly get that final result. What you need is a subquery to generate each of the merged fields. Here are some links on using subqueries:

http://allenbrowne.com/subquery-01.html
http://www.techonthenet.com/oracle/subqueries.php

The idea is:

1. You create a query to return DISTINCT NIIN records.
2. The query should be GROUPED BY FSC followed by NIIN and ORDER BY those two fields.
3. You would now use the subquery as aliased fields to get NetQTy, NRTS_Qty, Credits and Net.
4. For the Notes field, you would still use a subquery but you need to sort the records in ASC by FSC, NIIN and then in DESC by Net. Then use TOP 1 to get the last Note.

OR

4. Use a DLookup() function to get the last Note for that NIIN:

http://www.techonthenet.com/access/functions/domain/dlookup.php

VbaInet,

Thanks for the info. I'll look into the links you supplied as well. Thanks for the help!