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
> Find Unmatched In A Single Table, Access 2010    
 
   
davidac
post Oct 12 2018, 02:36 AM
Post#1



Posts: 183
Joined: 13-August 04



morning,
I have a table of 1.4 million records which contains duplicate enteries (this is as expected).
What I need to do is to show only the records where there is a difference in fileds. so if the table looks like this

Call ID Work Category
000010062812 Confirmed
000010062812 Deployment
000010063591 Deployment
000010063591 Deployment

it would only return

000010062812 Confirmed
000010062812 Deployment

as this is the record that is different
Go to the top of the page
 
cheekybuddha
post Oct 12 2018, 04:50 AM
Post#2


UtterAccess VIP
Posts: 10,658
Joined: 6-December 03
From: Telegraph Hill


Untested, but a simple aggregate query should do what you want:
CODE
SELECT [Call ID], [Work Category]
FROM YourTable
GROUP BY [Call ID], [Work Category]
HAVING COUNT(*) = 1;


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Oct 12 2018, 05:00 AM
Post#3


UtterAccess VIP
Posts: 10,658
Joined: 6-December 03
From: Telegraph Hill


No, HAVING COUNT(*) > 1 would just return
CODE
000010063591    Deployment


Th OP wants to see:
CODE
000010062812    Confirmed
000010062812    Deployment


(at least I *think* so, didn't have time to test - maybe you can check for me, I've got to go out now! tongue.gif )

d

--------------------


Regards,

David Marten
Go to the top of the page
 
projecttoday
post Oct 12 2018, 05:02 AM
Post#4


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


You're too fast. I deleted that post!

--------------------
Robert Crouser
Go to the top of the page
 
projecttoday
post Oct 12 2018, 05:09 AM
Post#5


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


You are correct. (Attached if anybody wants to see it.)
Attached File(s)
Attached File  DupeGroupDb.zip ( 19.2K )Number of downloads: 0
 

--------------------
Robert Crouser
Go to the top of the page
 
cheekybuddha
post Oct 12 2018, 06:15 AM
Post#6


UtterAccess VIP
Posts: 10,658
Joined: 6-December 03
From: Telegraph Hill


thumbup.gif Thanks for testing, Robert.

d

--------------------


Regards,

David Marten
Go to the top of the page
 
BruceM
post Oct 12 2018, 08:14 AM
Post#7


UtterAccess VIP
Posts: 7,758
Joined: 24-May 10
From: Downeast Maine


David, wouldn't your query return:

000010062812 Confirmed
000010062812 Deployment
000010063591 Deployment

I'm not sure I understand the OP's requirement since the sample data showed only the first two.

Or am I missing something?

Edit: I just realized I missed the part about the Count. But I still don't quite grasp the OP's intent.
Go to the top of the page
 
cheekybuddha
post Oct 12 2018, 08:33 AM
Post#8


UtterAccess VIP
Posts: 10,658
Joined: 6-December 03
From: Telegraph Hill


>> But I still don't quite grasp the OP's intent. <<

Me neither, but who are we to question why? wink.gif

It seems as if it doesn't matter if there are multiple duplicates which are the same, but duplicate Call ID's with different Work Categories need to be highlighted.

--------------------


Regards,

David Marten
Go to the top of the page
 
BruceM
post Oct 12 2018, 09:31 AM
Post#9


UtterAccess VIP
Posts: 7,758
Joined: 24-May 10
From: Downeast Maine


It's more about wondering what than questioning why. Perhaps the OP will weigh in again.
Go to the top of the page
 
projecttoday
post Oct 12 2018, 10:59 AM
Post#10


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


000010063591 Deployment
000010063591 Deployment

are the same.

The post says:

QUOTE
What I need to do is to show only the records where there is a difference in fileds.


so it doesn't appear in the results.


--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th December 2018 - 06:15 PM