Can a report have both horizontal and vertical groupings? 1

[Tfrank]

I have a database that lists moms in one table and all there kids in a second table. I can easily combine them into one query. I would like to be able to create a report that lists the moms name followed by her children on the SAME LINE. I can easily do it now if the kid’s names are listed individually under the mom’s name, but this layout is too wasteful of the paper, I need to minimize the number of pages. Any ideas would be greatly appreciated, thanks.

Tfrank

 

[japyy]

As far as I know, this is almost impossible.
Your kids-table or -query needs a field indicating 'WhatKindOfKid', and the number of possible 'KindOfKids' needs to be limited.
In that case you can make a crosstab-query, with 'KindOfKid' as columnheader, and use it as source for a report. If there is no 'KindOfKid'-information at hand, you try to do something contrary to the way you set up your own tables. Don't!
If you realy want to persist, you can invent 'kind-of-kid' information, for examble:
Make a query 'qryFirstBorn' Group on mother, select minimum from date-of-birth. The same for 'qrySeconBorn', with te constraint 'WHERE kid not in(select kid from qryFirstBorn'. Etc, etc. Finally make a select-query joining Mom on qryFirstborn, qrySecondborn etc.
Lots of boring work, resulting in a very complicated, slow query. In my opinion, you should only take the trouble if the information 'KindOfKid' is meaningfull information, not to save paper. And if the information 'KindOfKid' is meaningfull, ask yourself if you defined your tables correctly...

 

[tlbroadbent]

You can do what you want with a cross-tab query. To make the development easy, you need to add a field to the child table if it doesn't already exist. The field would be ChildNo to identify the child's position in the family - 1st,2nd, 3rd,... Therefore, I assume your tables have the following columns.

MomTbl:
MomId - Number
MomName - Text
MonAddress - Text

ChildTbl:
ChildId - Number
ChildName - Text
ChildNo - Number (Child number within family)
MomID - Number

Assume the data looks like this.
[tt]
MomTbl:
MomID MomName MomAddress
1 Janice 555 Main St
2 Linda 2003 S Willow Dr
3 Mary 122 Franklin Ct #7B
.
.
.

ChildTbl:
ChildID ChildName ChildNo MomID
1 Larry 1 1
2 Pat 2 1
3 Sandra 1 2
4 Nathan 2 2
5 Mike 3 2
6 Kim 4 2
7 Robyn 5 2
8 Karen 1 3
9 James 2 3
.
.
.[/tt]

Create a query to join the two tables. I called it qFamilies.

SELECT MomTbl.MomName, MomTbl.MomAddress, ChildTbl.ChildNO, ChildTbl.ChildName
FROM MomTbl INNER JOIN ChildTbl ON MomTbl.MomId = ChildTbl.MomID;

Then create a cross-tab query. You can use the cross-tab query wizard. The query should look like the following.

TRANSFORM First(qFamilies.ChildName) AS FirstOfChildName
SELECT qFamilies.MomName, qFamilies.MomAddress
FROM qFamilies
GROUP BY qFamilies.MomName, qFamilies.MomAddress
PIVOT "Child" & CStr([ChildNO]);

When you execute the cross-tab query you'll see results like the following.
[tt]
MomName MomAddress Child1 Child2 Child3 Child4 Child5
Janice 555 Main St Larry Pat
Linda 2003 S Will.. Sandra Nathan Mike Kim
Mary 122 Franklin.. Karen James John Kellie Susan[/tt]

Hope this helps. Terry

http://www.defendscouting.com/

"I'm not dumb. I just have a command of throughly useless information." - Calvin, of Calvin and Hobbes