Sort into query based on # of entries in table (1 Viewer)

gojets1721

Registered User.
Local time
Today, 01:01
Joined
Jun 11, 2019
Messages
429
I've got a bit of an odd project. See the attached example. Basically I'm looking to have a query pull in entries from the table ONLY when there's two entries that have the same field data. Basically, if two or more entries have the same customer ID and the same document date, then those entries should be pull it into the "2+ entry query.

Then I'm looking to create a separate query where if an entry's customer ID and document date match no other entry, it gets pulled into the "1 entry query"

So from my example, both John Sullivan entries and both Peter Sullivan entries would pull into a query together. Whereas the remaining two would pull into the "1 entry query"

I know its a weird request. I'm happy to explain further.
 

Attachments

  • Example.accdb
    672 KB · Views: 240

plog

Banishment Pending
Local time
Today, 03:01
Joined
May 11, 2011
Messages
11,613
I cannot follow your explanation. The good news is I don't have to--what I need from you is the results you want from the data you provided.

I have your table, now show me what data you expect your queriy(ies) to show based on that data. Again, no explanation needed, just demonstrate it with data.
 

June7

AWF VIP
Local time
Today, 00:01
Joined
Mar 9, 2014
Messages
5,424
Consider:

SELECT tblDocuments.* FROM tblDocuments INNER JOIN (
SELECT tblDocuments.CustomerID, DocumentDate, Count(*) AS CntCust
FROM tblDocuments
GROUP BY CustomerID, DocumentDate) AS Q
ON tblDocuments.CustomerID = Q.CustomerID AND tblDocuments.DocumentDate=Q.DocumentDate
WHERE CntCust >1;

Just change the > operator to = for the 1 count output.
 
Last edited:

gojets1721

Registered User.
Local time
Today, 01:01
Joined
Jun 11, 2019
Messages
429
That worked!!

One other question, say I wanted the query to only show each customer's most two recent entries based on the document date, any idea how I could do that?
 

gojets1721

Registered User.
Local time
Today, 01:01
Joined
Jun 11, 2019
Messages
429
I used the guide but can't seem to figure out what I'm doing wrong. It's not filtering out the older entries. Here's my code( qryRAWdocuments is the query I made using your code above):
Code:
SELECT qryRAWdocuments.CustomerID, qryRAWdocuments.DocumentDate
FROM qryRAWdocuments
WHERE qryRAWdocuments.DocumentDate IN
   (SELECT TOP 2 DocumentDate                          
   FROM qryRAWdocuments AS Dupe                            
   WHERE Dupe.CustomerID = qryRAWdocuments.CustomerID      
   ORDER BY Dupe.DocumentDate DESC)
ORDER BY qryRAWdocuments.CustomerID, qryRAWdocuments.DocumentDate;

Any idea?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:01
Joined
May 7, 2009
Messages
19,169
why not filter by AutoNumber field.
 

June7

AWF VIP
Local time
Today, 00:01
Joined
Mar 9, 2014
Messages
5,424
What is qryRAWdocuments - the first query I provided?

The query is returning TOP 2 records. Perhaps I need a larger dataset. Your db had only 6 records.

As arnelgp suggested, maybe you need TOP 2 ID instead of TOP 2 DocumentDate but still sort by DocumentDate.
 
Last edited:

Users who are viewing this thread

Top Bottom