Count unique dates

[Pastor Del]

I have 2 linked tables, tblPN & tblReceivedDate. tblPN has field PN and tblReceivedDate has field [Received Date]. The tables are used to record the receipt of different part numbers and the date they received. I want to use a query to count how many times a part number is received. The catch is that I only want to count a part once even if it is received more than once on the same date. With the data in the attached DB the count for PN 123 would be 5.

Can someone show me how to configure the query to do what I need to do?

 

[JHB]

If you can't get it to work, create a query on the first query.

SELECT Query1.PN, Count(Query1.[Received Date]) AS [CountOfReceived Date]
FROM Query1
GROUP BY Query1.PN;

 

[namliam]

in Oracle and a lot of other database you have the ability of: Count(Distinct Somefield)
Unfortunately access does not have such an ability, doing as JHB suggests is the way to go.

 

[Cronk]

The problem arises because the database is not normalised. If tblPN had only one occurrence for each part, there would be no issue.

 

[Pastor Del]

Thanks for the code JHB. It was just what I asked for. But now I'm wondering if there is a way to get the count using DCount.

 

[CJ_London]

in Oracle and a lot of other database you have the ability of: Count(Distinct Somefield)
Unfortunately access does not have such an ability, doing as JHB suggests is the way to go.

But there is a workaround

SELECT T.PN, Count(T.[Received Date]) AS [CountOfReceived Date]
FROM (SELECT DISTINCT tblPN.PN, tblReceivedDate.[Received Date]
FROM tblPN INNER JOIN tblReceivedDate ON tblPN.PN_ID = tblReceivedDate.PN_ID)  AS T
GROUP BY T.PN
HAVING (((T.PN)="123"))

returns 5

 

[namliam]

which is nothing but doing a subselect, i.e. the way that JHB suggested

 

[CJ_London]

which is nothing but doing a subselect, i.e. the way that JHB suggested

if you like

 

[JHB]

if you like

I better like you solution, (the whole combined in one query)!

 

[Brianwarnock]

I wonder why so many prefer subqueries to stacked queries, which are easier to write and debug. Plus a discussion between MVPs on here several years ago agreed that stacked queries would never be less efficient and often more efficient than subqueries, even whilst praising subqueries Allene Brown states in performance considerations.

Use stacked queries instead of subqueries. Create a separate saved query for JET to execute first, and use it as an input "table" for your main query. This pre-processing is usually (but not always) faster than a subquery. Likewise, try performing aggregation in one query, and then create another query that operates on the aggregated results. This post-processing can be orders of magnitude faster than a query that tries to do everything in a single query with subqueries.

Brian