Need Help with Report

[psq77]

Hello everyone....



I'm hoping someone can help me build this report.....

I have a database with 3 tables: Applicant, Under18, Over18

The Applicant table houses information about the head of household (Name, Address, phone number, Age, etc). It has a primary Key that is used as the relationship to the other two tables

Tables Under18 and Over18 house information about the dependants in each household. They have fields such as Name, DOB, Age, etc.

I need a report that shows the Head of household and Address and then the number of children of each age group. For instance, if there are 3 children in the household, ages 1, 5, and 9 then I need the report to show:

Name_______ Address______0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15.......
-------------------------------------------------------------------
Smith, John 3 Main St_______ 1_____ 1_____ 1

The report also needs to calculate how many of each age group in total at the bottom. I can do that once I have the above figured out.

If anyone can help, it is much appreciated.

[NTC]

Well I will start things off on a bad foot by noting your table structure is very bad. The Over18 and Under18 needs to be merged into one table 'Children' perhaps. (Over or under age 18 is self evident by DOB - plus of course a childs age changes each year so they will move from table to table)

Be that as it may; you would have a classic Parent-Child (1-many) table relationship - literally in this case.

Make a main form/report on the parent table. Then insert a sub form/report that is sourced on the child(s) table. Getting the results to present side by side, rather than the norm up/down is probably a waste of time.

It is easy with Access to count (i.e. by Age). That is an AggregateQuery feature. You can insert that as a separate sub form/report.

As I tell all novices; one really must have a textbook to design a database. Examples of all these tasks will be there. Easily found at Amazon or a big book store. Forums are great but a textbook will save you a ton of time.

Hope this helps.

[psq77]

Since I'm building this database for a non-profit org they have specified how they want the data organized, hence the under18 and over18 tables. They have specified how they want this particular report to look, and indicated they want it presented side by side. They don't need the actual ages on the report, just the number of each age in the houshold and a total for all at the bottom. Is it possible?