Need a strategy for a membership report

[vicdsfbayarea]

I'm a relative newby and need a report strategy to assist a non-profit create a report - "annual update" pages that are mailed to their membership once per year. The information for the report (one page per family) is in five tables. The first table is the "household table" and contains address, phone number, etc. The remaining tables contain two or three bits of information about family members - four types of family members in all (e.g. children, non-members in the household, etc.)

There is a hint of a possible strategy in The Missing Manual where a combo box is used and its row source contains a SQL query to a table that is not the basic table used in the report. Were I to use this approach, do I need to have a separate combo box and query for each bit of data I need to extract (e.g. child's name, birthday, email address, etc.) or is there a way that one query can satisfy the extract requirements for the entire row of data? I recognize that I would need to query a second, third and fourth table for the other classifications of people in the household.

Is there a better or cleaner approach?

Your assistance is really appreciated.

Vic

 

[Dairy Farmer]

For each record in Tables 2-4 have a Household field that is linked to Table1 (i.e. FamilyName). That way you would be able to extract any record from these tables based on FamilyName from Table1.

But you could simplify it a bit more:

Table1
IDHousehold (unique number)
FamilyName
Address
Phone

Table2
IDMembers
MemberName
RelationshipType (from Table3)
FamilyName (from Table1)

Table3
IDRelationship
RelationshipType

Now you can filter data by FamilyName.

 

[vicdsfbayarea]

Thank you dairy farmer.

Indeed, there is already the presence of a numerical "family id" in each of the five tables. There is also an ordered sixth table with everything in it, and it is coded with the relationship of each individual as well as the family ID. (This was the "mother" table which was spun out of the front-end system which is something like a hobbled CRM system.) The goal here is to produce a 1-page report, one for each family, with all household members but a separate section for each household member - e.g. child section, etc. The fields for each section are different. In your opinion, how can this be done in Access Reports? BTW, I have no problem creating a report for the "mother table" but it does not meet the organization's goal.

Thank you in advance!

Vic

 

[Explorer]

Hi Vic,

I am a newbie as well and this is how I solve this problem by my lame method:

1. The presence of family Id in each table makes sure that you can combine these 4 or 5 tables by that.
2. Sometimes for me combining these tables gets tricky so I try to chop it into pieces by making a query using a set of two tables. Then I make another query by using the rest of the two tables. Once I am sure of that those two queries have all the fields that I need then I make the final query by both the queries using the unique id to give me access to all the fields.
3. Then I design the report and use that final query as data source.

I think it should work.

 

[boblarson]

Table2
IDMembers
MemberName
RelationshipType (from Table3)
FamilyName (from Table1)

Incorrect - You store the ID not the family name. Store the ID because if the name is spelled wrong or changes (due to some circumstance) you only need to change it in ONE record whereas if you store the name in multiple records you have to ensure to go fix each. Plus it is more efficient to store the KEY as a foreign key than to store text.

Same goes for RelationshipType - it should be the relationship ID that is stored there.

 

[vicdsfbayarea]

Bob,

Thank you for your words of wisdom. Question: Is there a rational way to display the results of multiple queries on one report? I need a 1-page report for each family. The format is (1) some words and a logo, (2) family info, (3) more words, (4) member's name and 3 -4 data fields, (5) more words and then up to three more groups in the family. Everything is stored coded by family ID in a single table, and I used queries to segment data into their own tables based on the relationship in the family (child, etc.) What would your strategy be for constructing such a report - one for each family (~100 families)?

Thank you in advance,

Vic

 

[Dairy Farmer]

I just gave the basic layout of the tables and their relationships. I didn't go into how to link them.

What I was meaning is that all the households are stored in Table1. Then in Table2 you have a field that links the person to the household.

A list of relationship types are stored in Table3. Then in Table2 you have a field that secifies the relationship.

Table2 has all the details related to a person (name, Address, phone,..., Household and Relationship)

Now you are able to query a household.

The report may look something like this:

Smith
Smith, Bob - Parent - 123 Boom St - 555 1234
Smith, Jane - Child - 123 Boom St - 555 1234
Smith, Robert - Child - 456 Palm Dr - 555 4321
Jones, Richard - non member - 456 Palm Dr - 555 4322

 

[boblarson]

Bob,

Thank you for your words of wisdom. Question: Is there a rational way to display the results of multiple queries on one report? I need a 1-page report for each family. The format is (1) some words and a logo, (2) family info, (3) more words, (4) member's name and 3 -4 data fields, (5) more words and then up to three more groups in the family. Everything is stored coded by family ID in a single table, and I used queries to segment data into their own tables based on the relationship in the family (child, etc.) What would your strategy be for constructing such a report - one for each family (~100 families)?

Thank you in advance,

Vic

If this is all stored in a single table, then the design is incorrect.

 

[Explorer]

Vic,

I don't know what is stopping you from displaying all the information on one report. If you have the data set then all you gotta do is put all the required fields on the report at appropriate places.
I do it everyday even if the design is incorrect (as in bob's words) but it still gives me one report as I desire.

 

[boblarson]

You can also use subreports if necessary.

 

[vicdsfbayarea]

Bob, Thank you very much, especially about subreports. First of all, the commercial front-end process that keeps track of all of the family data spits out a single file with everything. As a separate process I've queried out the family and individuals into a total of five separate tables linked by a unique numeric family ID. Table#1 is the family data table. I've tried subreports, and the issue there is how to prevent each subreport from showng up on a separate page. I lose page control with subreports: family information shows up on its own page followed by what is on subreport#1 for a second page, and subreport#3 on a third page, etc. I suspect the issue is setting up the group; however, I think I am doing that logically by telling Access to group on family ID. The theory is that when family ID changes so should the page. However, Access seems to be changing the page for every family and every individual in that family. Do you have any idea what is happening?

 

[boblarson]

What should be happening is

1. The GROUPING and SORTING in the Report should be on FamilyID or Family Name

2. The subreports need to be linked with their master/child links on FamilyID

3. The subreports need to be put into the DETAILS section and set to be as small heighth as possible. You put the subform control on the form and then can have it look like this (but it will view and print fine):

4. If you want a new page after every family then in the group FOOTER you can set the FORCE NEW PAGE property to AFTER SECTION.

 

[vicdsfbayarea]

Thank you, Bob. You have given me enough clues to give it a try. This is new information that was not in the manual I have been using (Missing Manual).

I appreciate it very much. Will re-post will success.

Best regards,

Vic