Report Design Question

[arthurmq]

Hi,
I'm trying to run a report based on a table that contains client's names and its addresses, then each client has listed into columns up to 16 possible household members where each household member has 5 possible characteristics. The original table with the data I received is more complex, so I tried to depict this in a simple way on the following table:


______________(Person 1 Details)____(Person 2 Details)____=>up to PN16
Name |Address|PN1(D1)| PN1(D2)|.....|PN2(D1) |PN2(D2)|...


I created a report with the Name and Address on the top followed by 16 repeated blocks containing the same fields with the Details for Person 1 thru Person 16. In most cases every client's household contains only a few persons, however I need to contemplate any instance where the 16 possible people are listed. When I run the report shows the client's name and address and only a few blocks with people's details are filled out, the blocks for non-exiting persons are just blank. Is there a way to make the entire block of fields to be omitted if there is no data to show? or probably I need to re-design the whole report?
I will really appreciate any suggestion.

Thank you in advanced.

 

[SydneyGeek]

You need to rethink your data layout -- Access is a relational system, and works best when you group related data together.
The advantages are too many to name here, but here are some:
1. Simpler data entry
2. Fewer errors
3. Easier updates -- store and change an address in ne location, adn all related records get the change.
4. You get to keep most of your hair when designing reports and queries.
5. More flexible, easier to expand. No need to add more fields when more records are added.

In your case, Households makes one logical table.
HouseholdID (Autonumber, Primary Key)
Address1 (Text)
Address2 (Text)
City (Text)
State (Text)
Postcode (Text)

The next table is People.
PeopleID (Autonumber, Primary Key)
HouseholdID (Number) -- used to relate people to their households
FirstName (Text)
LastName (Text)
DOB (Date/Time)
Gender (Text)
Other fields as required.

Now, in Relationships view, add both tables. Drag and drop from HouseholdID in Households to HouseholdID in People to create the relationship. In the dialog, tick the options to Enforce Referential Integrity and Casade Update changes.

OK -- now for the interface.
Build an Autoform (columnar) based on Households, and call it frmHouseholds.
Build an Autoform (columnar) based on People, and call it subPeople. Now go into Design view on subPeople and, in the Properties, change Default View to Datasheet. Save and close the form.
Open frmHouseholds in Design view, Resstore so you can see the dataase container, and drag the subPeople icon onto the form. Access should automatically link the HouseholdID fields in both forms.
Use this form to enter people's details. You won't be limited to 16 per household, and any reports you build will only show rows for those people who exist -- no wasted space.

Speaking of the reports --
Build a query that shows all of the Household and People fields.
Use this query as the basis of the report.
Build the report using the Wizard. As you go through the Wizard, group by Household. Sort however you want on the other fields.
(Post back if you need details).

Denis

 

[arthurmq]

Thank you! I'm working on it.