Counting with Criteria

[Shawnf]

I have a table like this

PN	COUNT DATE
ABC	1/18/2016
ABC	1/20/2016
ABC	5/5/2016
XYZ	1/12/2016
XYZ	1/18/2016
XYZ	5/10/2016
HIJ	1/8/2016
HIJ	1/12/2016

All PNs have to be counted 4 times a year
the issue I have is there is a criteria that if a PN is counted within 62 days of each count that it does not count as an actual count.

I need to have this information sorted to one of these two options and I cannot figure it out. Notice that each of the second count dates from above are not included in the output as there was not 62 days that have lapsed since the first count

PN	First Count	Second Count	Third Count	Forth Count
ABC	1/18/2016	5/5/2016
XYZ	1/12/2016	5/10/2016
HIJ	1/8/2016

OR

PN	Times Counted
ABC	2
XYZ	2
HIJ	1

Any help here would be greatly appreciated.

thank you

[pbaldy]

The second is pretty easy with a totals query:

SELECT PN, Count(*) AS TimesCounted
FROM TableName
GROUP BY PN

[Shawnf]

That gives me a total of how many times it has been counted. the issue is with the 62 day requirement it isn't the total number of times counted.
in the example above the 2nd count for each PN does not count as a count. so just a Count() operation will not work.

[pbaldy]

Ah, sorry, I didn't read well enough. I wonder if you can get the previous record on the same line:

http://allenbrowne.com/subquery-01.html#AnotherRecord

do the math, and add a criteria to exclude the appropriate records. Might take two queries.

[Shawnf]

This may be headed in the right direction. Thank you. I am still very new to access, and don't fully understand SQL, so please forgive me for sounding totally newb. Any further help with the actual coding would be very helpful.

[CJ_London]

duplicate post here

http://www.access-programmers.co.uk/...=1#post1490212

[Shawnf]

Apparently it is not right to post the same issue on multiple forums without linking to the other, so here it is...
http://www.access-programmers.co.uk/...d.php?t=287076

same issue different forum.

[CJ_London]

try

SELECT PN, Count(*) AS TimesCounted
FROM pnTable
WHERE not exists(SELECT * FROM pnTable T WHERE CountDate between pnTable.CountDate and pnTable.CountDate-62)
GROUP BY PN