Subquery to count records in a group

[akaRose]

Hello,

I had previously asked a question in how to filter records based on date and was able to get it to work. However, it now leaves some groups with only one member, and I don't want to include groups of less than 1. I've tried using count, but can't seem to get it working.

The field that the groups are based on is numerical (SM24_No)

Here is the code that I'm using now to filter for the date

SELECT ENTRYTABLE.SM24_No, ENTRYTABLE.KEY, ENTRYTABLE.iPHIS_NO, ENTRYTABLE.RECDATE, ENTRYTABLE.FirstName, ENTRYTABLE.Surname, ENTRYTABLE.PatientCity, ENTRYTABLE.DOB, ENTRYTABLE.HEALTHCARD_No, ENTRYTABLE.OctalCode, ENTRYTABLE.Region_Designation, ENTRYTABLE.Strain, ENTRYTABLE.MIRU_24, ENTRYTABLE.SM24Date
FROM ENTRYTABLE
WHERE (((ENTRYTABLE.SM24_No)=True) AND ((ENTRYTABLE.RECDATE)>DateAdd("yyyy",-2,Date())))
GROUP BY ENTRYTABLE.SM24_No, ENTRYTABLE.KEY, ENTRYTABLE.iPHIS_NO, ENTRYTABLE.RECDATE, ENTRYTABLE.FirstName, ENTRYTABLE.Surname, ENTRYTABLE.PatientCity, ENTRYTABLE.DOB, ENTRYTABLE.HEALTHCARD_No, ENTRYTABLE.OctalCode, ENTRYTABLE.Region_Designation, ENTRYTABLE.Strain, ENTRYTABLE.MIRU_24, ENTRYTABLE.SM24Date;


The output (minus some fields) looks something like this.
*The last group SM24-4 is what I would like to eliminate.

SM24_No RECDATE FirstName Surname Key
SM24-2 2009-01-03 Bob Smith 001
SM24-2 2009-06-24 Carol Rae 002

SM24-3 2007-01-16 John Doe 156
SM24-3 2008-10-14 Billy Bob 346
SM24-3 2009-12-06 Jane Doe 198

SM24-4 2008-01-25 Jarod Rod 468

Thanks so much in advance

 

[Golom]

Since you are Grouping on every field in the select ... each record is a "Group" ... baring the unlikely event that two different people have the same names, same date of birth, same Health Card Number, etc.

Presumably the "Group" that you are looking for is the field "SM24_No" and you don't want to see those if there is only one.

Try something like

SELECT SM24_No, KEY, iPHIS_NO, RECDATE, FirstName, 
       Surname, PatientCity, DOB, HEALTHCARD_No, OctalCode, 
       Region_Designation, Strain, MIRU_24, SM24Date

FROM ENTRYTABLE 

WHERE RECDATE > DateAdd("yyyy",-2,Date())
  AND SM24_No IN (Select SM24_No From ENTRYTABLE 
                  Group By SM24_No 
                  Having Count(*) > 1)

The "SM24_No = True" condition seemed to be irrelevant since SM24_No appears to be a text field ... not a Boolean.

 

[akaRose]

Works wonderfully! Thank you so much for the help, I had tried something similar but I can see where I went wrong now.

So very much appreciated

 

[akaRose]

I'm sorry but I tried using the code again, and I must have missed something because it doesn't seem to work as I originally thought.

Its still giving me single records for groups of SM24_No not sure why since to me the code looks right.

Any suggestions would be welcome.

Thanks so much

 

[Golom]

I suspect that it is because there is more than one record for an SM24_No value but all but one of them are being filtered out by the Date Range restriction. Change your SQL to

SELECT SM24_No, KEY, iPHIS_NO, RECDATE, FirstName, 
       Surname, PatientCity, DOB, HEALTHCARD_No, OctalCode, 
       Region_Designation, Strain, MIRU_24, SM24Date

FROM EntryTable 

WHERE SM24_No IN (Select SM24_No 
                  From EntryTable 
                  Where RecDate > DateAdd("yyyy",-2,Date())
                  Group By SM24_No 
                  Having Count(*) > 1)

 

[akaRose]

Thank you so much for the code.
This is helping with eliminating single records, however within the groups (by SM24_No) records that have RECDATE >2years will appear. If everyone in the group has a RECDATE >2years ago the entire group won't show (which is what I want) but within a group some people maybe be recent (RECDATE<2years) and some might be older records and I don't want to see those. I have no clue if I'm making any sense. Sorry.

Looking at the table below the only output I would like to see is the SM24-3 group, and Bob and Carol in the SM24-2 group.

This is because the RECDATE for Carey in the SM24-2 group is >2years ago (so I don't want to see this records). And in the SM24-4 group Jarod has a RECDATE >2years ago, which would leave a group of 1 (and I only want to see groups of more than 1 person).


SM24_No RECDATE FirstName Surname Key
SM24-2 2009-01-03 Bob Smith 001
SM24-2 2009-06-24 Carol Rae 002
SM24-2 2005-06-24 Carey Bens 016


SM24-3 2008-01-16 John Doe 156
SM24-3 2008-10-14 Billy Bob 346
SM24-3 2009-12-06 Jane Doe 198

SM24-4 1999-01-25 Jarod Rod 468
SM24-4 2009-01-18 Jerry Burns 475

Thanks for all your help!

 

[Golom]

OK

SELECT SM24_No, KEY, iPHIS_NO, RECDATE, FirstName, 
       Surname, PatientCity, DOB, HEALTHCARD_No, OctalCode, 
       Region_Designation, Strain, MIRU_24, SM24Date

FROM EntryTable 

WHERE RecDate > DateAdd("yyyy",-2,Date())
  AND SM24_No IN (Select SM24_No 
                  From EntryTable 
                  Where RecDate > DateAdd("yyyy",-2,Date())
                  Group By SM24_No 
                  Having Count(*) > 1)

That code will ...

- Pick only those SM24_No values that have at least two
records in the required date range

- then further filter out any records for those SM24_No
values that are outside that range.

The one point of confusion that I still have is these two records
[tt]
SM24-4 1999-01-25 Jarod Rod 468
SM24-4 2009-01-18 Jerry Burns 475
[/tt]
Since only one of them is in the specified date range it should have been eliminated by the subquery.

Are you saying that it is still appearing even with the altered code?

 

[akaRose]

Hello

The most recent code you posted works to eliminate the case of Jarod and Jerry, since you added a WHERE outside of the second select statement....so all is well.

But in playing with the code I've realized I have one more issue I didn't think of in planning this out

This query is for a weekly report, meaning I need to eliminate records that have already been reported and someone has been contacted with respect to the listed people.

To do this I've added a field in my Entrytable, called PHU_contacted. I'm not sure if I should make this a yes/no or a text field, since I don't have much experience with yes/no fields. Essentially each week after the report is generated and passed on, the PHU will be contacted and then the database will be updated to reflect that Yes the PHU has been contacted.

So here's where I'm not sure how to include this in my query. I think I would put a statement which reads:

Count(Entrytable.PHU_Contacted) <> Count(Entrytable.Key)

but in trying to incorporate it into the current coding, I'm getting aggregate errors and my skills aren't strong enough to sort it all out.

This statement means that if ALL members of a SM24_No group have been contacted then this group won't show on the report. But if only some or none of the members of the SM24_No group have been contacted then it will still show on the report.

Again I'm rambling....probably making little sense. I wished I had realized this previously that my reports will keep generating the same information.

Thank you so so much for all your help!

 

[Golom]

A question or two ...

If some, but not all members of an SM24_No group have been contacted then

- Do you want to see the whole group ... both those who have been contacted and those who have not?

OR

- Do you want to see ONLY those who have not been contacted?

Do you still want to observe the restriction that, if only one record in a SM24_No group meets the criteria, you don't want to see it (contacted or not)?

 

[akaRose]

I still want to observe all the same restrictions as before with respect to single's in an SM24_No group.

Yes if some, but not all members have been contacted I'd like to see the whole group (contacted and not) so I know who's in the group. The only time I don't need to see a group is if they've all been contacted. Otherwise my reports each week will always be the same...just longer as more people are added to the database.

 

[Golom]

I will assume that PHU_Contacted is a Yes/No field. If it is text containing "Yes" or "No"
then change "NOT PHU_Contacted" (below) to "PHU_Contacted = 'No'"

This will show everybody in the group (if there's more than one of them), and not all of them have been contacted.

WHERE RecDate > DateAdd("yyyy", -2, Date())

  AND SM24_No IN (Select SM24_No From EntryTable 
                  Where RecDate > DateAdd("yyyy", -2, Date())
                    AND NOT PHU_Contacted
                  Group By SM24_No 
                  Having Count(*) > 1)

Things like

Count(Entrytable.PHU_Contacted) <> Count(Entrytable.Key)

can have some problems. Count(*) counts every record while Count(SomeFieldName) ignores NULLS so
you could get different (i.e. <>) counts because one of your fields contains different numbers of NULLs
than the other ... even though there are the same number of records.

 

[akaRose]

Everything works perfectly! Thank you so much for all the help, it is so very very much appreciated, I've been going in circles on this one for awhile.