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

templeowls

Registered User.
Local time
Yesterday, 19:45
Joined
Jun 11, 2019
Messages
109
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: 36

plog

Banishment Pending
Local time
Yesterday, 21:45
Joined
May 11, 2011
Messages
10,479
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
Yesterday, 18:45
Joined
Mar 9, 2014
Messages
3,509
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:

templeowls

Registered User.
Local time
Yesterday, 19:45
Joined
Jun 11, 2019
Messages
109
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?
 

templeowls

Registered User.
Local time
Yesterday, 19:45
Joined
Jun 11, 2019
Messages
109
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

once i caught a fish alive...
Local time
Today, 10:45
Joined
May 7, 2009
Messages
13,205
why not filter by AutoNumber field.
 

June7

AWF VIP
Local time
Yesterday, 18:45
Joined
Mar 9, 2014
Messages
3,509
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