Query for Report, with groups

[akaRose]

I'm having trouble figuring out something I think should be fairly basic. I have a form, from which there are a few parameters that can be chosen which will open different reports. The reports are based on queries which is where I'm having a problem.

I have data that has previously been assigned to groups, and would like to show all members of the group if at least one of them has a Date Added within the last two years. Essentially if there is nobody in the group added within the last two years I don't need to see that group in the report.

Any suggestions are appreciated. Thank you.

 

[dhookom]

Do you have anything else to share? How about Record Sources, tables, fields, data types, form and control names, ...?

Is "the last two years" ever going to change to "the last year" or "the last 30 months"?

I'm not sure how anyone can see your MDB file and render decent assistance without making guesses and assumptions.

Duane
Hook'D on Access
MS Access MVP

 

[akaRose]

The query is based on an Entrytable, which contains about 50 fields. About a dozen are included in the report, but the relevant ones for filtering the data are the group which is a field called SM24_No and this is a text field, and the date added is a field called RECDATE - which is a date field.


Form name is frm050Q021MatchReport
To open the report from the form I have a check box, to see all records that have been grouped you tick the ALL check box, and to open the report that has all groups containing at least one person with a RECDATE <= 2 years (this won't change) then click the RECENT check box. As prev. mentioned I can't figure out how have the query not show in the report, groups containing people that have a RECDATE <=2 years. The only way I know to do something like this would be to set the criteria for the RECDATE but this will take out individual people that are less than 2 years rather than looking at the groups.

eg. The first two groups should be in the report because they have at least 1 person with a RECDATE of <=2 years, the last group SM24-4 I don't want to show in the report.

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

SM24-3 2005-01-16 John Doe
SM24-3 2003-10-14 Billy Bob
SM24-3 2009-12-06 Jane Doe

SM24-4 2001-01-25 Jarod Rod
SM24-4 2002-04-16 Mia Marks

Not sure if I'm making any sense.

Thanks

 

[dhookom]

I would probably create a query that identifies the Max of RecDate for each SM24_No. You can try SQL like:

SELECT SM24_No, Max(RECDATE) as MaxRecDate
FROM EntryTable
GROUP BY SM24_No
HAVING Max(RECDATE) > IIf(Forms!frm050Q021MatchReport!RECENT, DateAdd("yyyy",-2,Date()), #1/1/1900#);

Duane
Hook'D on Access
MS Access MVP

 

[akaRose]

Ok so I tried this and the output it gave was the individual in each group that had the most recent RECDATE, which isn't quite what I need to get to.

I'm lost on this one, I just can't seem to wrap my head around it.

 

[dhookom]

The SQL I suggested has only the group and most recent date. There is no individual. If you have another query, you should provide the SQL since we can't see what you have done.

Duane
Hook'D on Access
MS Access MVP

 

[akaRose]

Yes I just realized this and put the other fields I need to see back in. Still not quite the right result - perhaps I've done something wrong.

The output it is giving me is individuals that have a RECDATE within 2 years, however I need to see the individuals that have a RECDATE >2years if they are within a group based on their SM24_No that has at least one member with a RECDATE within 2 years. Here's the code if this makes any sense.

SELECT EntryTable.SM24_No, Max(EntryTable.RECDATE) AS MaxRecDate, EntryTable.KEY, EntryTable.FirstName, EntryTable.Surname, EntryTable.RECDATE, EntryTable.DOB, EntryTable.HEALTHCARD_No, EntryTable.Responsible_PHU, EntryTable.iPHIS_NO, EntryTable.Region_Designation
FROM EntryTable
GROUP BY EntryTable.SM24_No, EntryTable.KEY, EntryTable.FirstName, EntryTable.Surname, EntryTable.RECDATE, EntryTable.DOB, EntryTable.HEALTHCARD_No, EntryTable.Responsible_PHU, EntryTable.iPHIS_NO, EntryTable.Region_Designation
HAVING (((EntryTable.SM24_No)>1) AND ((Max(EntryTable.RECDATE))>IIf([Forms]![frm090Q010GenotypeReport]![chk2yearmatch],DateAdd("yyyy",-2,Date()),#1/1/1900#)));



The help is much appreciated!

 

[dhookom]

The SQL I provided for you was only to find the groups. Beyond that, you would need to combine the query with other tables/queries to meet other criteria.

Duane
Hook'D on Access
MS Access MVP

 

[akaRose]

Ok I'll work on the rest, and see where I can get.

The other thing I noticed is that groups of 1 appear, but I would only like to show groups with more than one person. I thought that putting SM24_No>1 under HAVING would take care of this but it doesn't seem to. I think I need a COUNT>1 but I'm not sure how to include this.

Thanks