UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Find Duplicate Field Combinations, Any Version    
 
   
The6Js
post Oct 17 2019, 09:52 PM
Post#1



Posts: 31
Joined: 19-March 12



I have a table with an InvoiceNumber and a PartNumber (among others), including ID which is an autonumbered index.
Many records can have a duplicate InvoiceNumber due to each order having several lines. These lines are indicated by a field called InvoiceSuffix. Only 1 PartNumber should exist for each unique InvoiceNumber. Recently, my data history is showing that more than 1 PartNumber is being used for a given InvoiceNumber. I would like to be able to see the scope of this issue by finding out how many InvoiceNumbers have more than 1 PartNumber associated with them. I am not having any success in building a query to do this.

Can anyone point me in the correct direction?

Thanks in advance
Go to the top of the page
 
RJD
post Oct 17 2019, 10:16 PM
Post#2


UtterAccess VIP
Posts: 10,137
Joined: 25-October 10
From: Gulf South USA


Hi: Create a totals query (Group By), grouping on invoice number and counting part numbers, then limit the part count to >1.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
projecttoday
post Oct 17 2019, 10:45 PM
Post#3


UtterAccess VIP
Posts: 11,210
Joined: 10-February 04
From: South Charleston, WV


For further information:


"https://www.w3resource.com/SQL/aggregate-functions/count-having.php"

--------------------
Robert Crouser
Go to the top of the page
 
projecttoday
post Oct 17 2019, 10:50 PM
Post#4


UtterAccess VIP
Posts: 11,210
Joined: 10-February 04
From: South Charleston, WV


For some reason, the link just won't work.

--------------------
Robert Crouser
Go to the top of the page
 
projecttoday
post Oct 17 2019, 11:10 PM
Post#5


UtterAccess VIP
Posts: 11,210
Joined: 10-February 04
From: South Charleston, WV


You can try posting the link in post #3 (minus the quotes) and then change "SQL" to lower case.

--------------------
Robert Crouser
Go to the top of the page
 
The6Js
post Oct 17 2019, 11:15 PM
Post#6



Posts: 31
Joined: 19-March 12



Thanks for the input. But that is not finding what I am looking for. I want something to find:

InvoiceNumber InviceSuffix PartNumber
100 1 abcd123456
100 2 abcd123456
100 3 wxyz987654

Any other thoughts?
Go to the top of the page
 
projecttoday
post Oct 17 2019, 11:25 PM
Post#7


UtterAccess VIP
Posts: 11,210
Joined: 10-February 04
From: South Charleston, WV


What did you try?

--------------------
Robert Crouser
Go to the top of the page
 
The6Js
post Oct 18 2019, 01:44 AM
Post#8



Posts: 31
Joined: 19-March 12



Suggestion by RJD. Sorry that I put the reply in the wrong place.
Go to the top of the page
 
RJD
post Oct 18 2019, 01:48 AM
Post#9


UtterAccess VIP
Posts: 10,137
Joined: 25-October 10
From: Gulf South USA


Hi: Try something like this ...

SELECT InvoiceNumber, PartNumber, Count(PartNumber) AS CountOfPartNumber
FROM tblMyRecords
GROUP BY InvoiceNumber, PartNumber
HAVING Count(PartNumber)>1;

See the demo attached.

I think I left out the PartNumber in my original grouping response.

HTH
Joe
Attached File(s)
Attached File  DuplicatePartNumbersPerInvoice.zip ( 19.89K )Number of downloads: 1
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
projecttoday
post Oct 18 2019, 01:59 AM
Post#10


UtterAccess VIP
Posts: 11,210
Joined: 10-February 04
From: South Charleston, WV


Are you aware that if you create an index on invoice number and part number and set it as unique that such duplicate entries cannot be made?

--------------------
Robert Crouser
Go to the top of the page
 
The6Js
post Oct 18 2019, 01:59 AM
Post#11



Posts: 31
Joined: 19-March 12



Thank you!

ID InvoiceNumber InvoiceSuffix PartNumber
1 100 1 abcd123456
2 100 2 abcd123456
3 100 3 wxyz987654
4 101 1 123456789
5 101 2 123456789
6 102 1 987654321
7 102 2 987654321
8 102 3 987654321
9 103 1 poimnbc
10 103 2 qwerty

In this example data table, the result that I would want is to tell me that InvoiceNumber(s) 100 and 103 have two different PartNumber(s) associated with them. No need for me to see InvoiceNumber(s) 101 and 102.

Does this change what you are thinking?

Nick
Go to the top of the page
 
projecttoday
post Oct 18 2019, 02:17 AM
Post#12


UtterAccess VIP
Posts: 11,210
Joined: 10-February 04
From: South Charleston, WV


In that case I think you need to make a SELECT DISTINCT query and then do a count on that. (And forget about the indexing.)

--------------------
Robert Crouser
Go to the top of the page
 
RJD
post Oct 18 2019, 02:18 AM
Post#13


UtterAccess VIP
Posts: 10,137
Joined: 25-October 10
From: Gulf South USA


Hi Nick: Well, that was a bit different from my original interpretation. Sorry. Thanks for the data and clarification. See the revised demo attached. See if this is what you are looking for.

See qryGrouping2 for the final result.

I added theDBguy's SimpleCSV function to put all the unique part numbers on the same line with the Invoice.

HTH
Joe
Attached File(s)
Attached File  DuplicatePartNumbersPerInvoice_Rev1.zip ( 21.51K )Number of downloads: 4
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
The6Js
post Oct 18 2019, 02:28 AM
Post#14



Posts: 31
Joined: 19-March 12



Absolutely perfect! Thank you so very much!
Go to the top of the page
 
RJD
post Oct 18 2019, 02:30 AM
Post#15


UtterAccess VIP
Posts: 10,137
Joined: 25-October 10
From: Gulf South USA


You are very welcome, Nick. Glad that worked for you. thumbup.gif

Continued success ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
The6Js
post Oct 18 2019, 03:14 AM
Post#16



Posts: 31
Joined: 19-March 12



Hi.

In my table format, the InvoiceNumber is a long number. I think that this might be the reason why the query is failing at the SimpleCSV module. What and where do I change what to correct this, if I am thinking correctly?

NVM I figured it out! Thanks again. The query found 40 instances with this issue!
This post has been edited by The6Js: Oct 18 2019, 03:27 AM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd November 2019 - 08:33 AM