Setting Dynamic group fields/headers on reports

[w8n]

My Access 2000 data base consists of Helpers that volunteer in Areas (Facilities, Service, Ministry) that have Subjects (Painting, Shopping, Ushering). Helper Bob is in Service, doing home repair, so he has an entry in tblServiceMembers: [ServiceMembers].[SvcHomeRep], SvcHomeRep is a checkbox.

I dynamically generate a query from user CheckBox fill-in so directors can see who is available in what Area/Subjects. The SQL for the generated query looks like:

SELECT [Helpers].[HelperLastName], [Helpers].[HelperFirstName], [Helpers].[HelperPhone], FacilitiesMembers.HelperID AS FacilitiesMembers_HelperID, ServiceMembers.HelperID AS ServiceMembers_HelperID
FROM (Helpers LEFT JOIN FacilitiesMembers ON Helpers.HelperID = FacilitiesMembers.HelperID) LEFT JOIN ServiceMembers ON Helpers.HelperID = ServiceMembers.HelperID
WHERE (((FacilitiesMembers.FacPaint)=True)) OR (((ServiceMembers.SvcHomeRep)=True))
ORDER BY FacilitiesMembers.FacPaint, ServiceMembers.SvcHomeRep, Helpers.HelperLastName;

I get a clean report for all criteria, but would like to automatically lay in headers and divide output by whether the people do both things (HomeRep and Paint here) or only one, ie.:

Service Home Repair and Facilities Painters
Joe Fig 555-3333
Dawn Hold 555-0988

Service Home Repair
Bob Pore 555-1212
Steve Still 555-2345

Facilities Painters
Ralph Aim 555-4567
Marcie Car 555-3214

I don't always need to report on two Subjects as here - sometimes only one, sometimes three. Using only one works perfectly (no sorting), but I don't know how to begin w/ DYNAMICALLY setting group fields, assigning GroupHeaders, etc. for dividing the info.

Perhaps it's easier to have many reports, each based on how many choices are made?

Any direction, books, sites, etc. is appreciated in advance.
Thanks for allowing me to ramble...


All help is appreciated in advance!

Bob

 

[dhookom]

I think the issue is your table structure. You are storing data values as field names and then setting them to true or false with check boxes. I would consider changing the table structure to include:
tblHelpers (one record per person)
tblAreas (one record per area of service)
tblSubjects (one record per subject with a field related to tblAreas)
tblHelperSubjects (one record per helper per subject)



Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[w8n]

It took some time and experimentation, but I got what I was looking for. I appreciate any and all who checked this out, and to dhookom for a reply. Please consider this a closed item!

All help is appreciated in advance!

Bob