I need to create a report for a database I have created, and its proving to be elusive.
I have two tables, one with a lot of peoples personal data like name, phone, etc, referenced by a key of type long. Each of these people were recruited by someone, so they each have a reference to that other person in the same table. ie...
key name phone recruited
1 george 555 2125 0
2 frank 555 2233 1
3 rachel 555 2334 1
4 edward 555 2345 3
.
.
.
The second table contains weekly sales figures with a date. ie...
user id date amount
1 23/10/2000 $2500.00
2 23/10/2000 $1400.00
3 23/10/2000 $1650.00
4 23/10/2000 $2350.00
.
.
.
The report must take the people in the list and order them from the top down sorting them into a list corresponding to their recruiter with a totals value beneath each group (which may be nested). ie...
george's group
1 george $2500
2 frank $1400
rachel's group
3 rachel $1650
4 edward $2350
rachel's total $4000
.
.
.
george's group $11050
I have created a script that creates a new table and puts all of the people in it in their groups and sub groups with this information being recorded in fields group1 group2 etc, and the report with these fields in the 'grouping and sorting' and tried building the report from that.
This works to the extent that it displays the groups and subgroups correctly, however the 'grouping and sorting' feature then wants to give every entry a footer...resulting in each entry with 10 subtotals.
I am using Access 97 with this db, but if you can answer this in terms of Access 00 then thats fine.
Any suggestions at this stage are welcome as I have tried hundreds of ideas and am running out!
chat soon
Michael
nullgod@swift-infotech.com.au
I have two tables, one with a lot of peoples personal data like name, phone, etc, referenced by a key of type long. Each of these people were recruited by someone, so they each have a reference to that other person in the same table. ie...
key name phone recruited
1 george 555 2125 0
2 frank 555 2233 1
3 rachel 555 2334 1
4 edward 555 2345 3
.
.
.
The second table contains weekly sales figures with a date. ie...
user id date amount
1 23/10/2000 $2500.00
2 23/10/2000 $1400.00
3 23/10/2000 $1650.00
4 23/10/2000 $2350.00
.
.
.
The report must take the people in the list and order them from the top down sorting them into a list corresponding to their recruiter with a totals value beneath each group (which may be nested). ie...
george's group
1 george $2500
2 frank $1400
rachel's group
3 rachel $1650
4 edward $2350
rachel's total $4000
.
.
.
george's group $11050
I have created a script that creates a new table and puts all of the people in it in their groups and sub groups with this information being recorded in fields group1 group2 etc, and the report with these fields in the 'grouping and sorting' and tried building the report from that.
This works to the extent that it displays the groups and subgroups correctly, however the 'grouping and sorting' feature then wants to give every entry a footer...resulting in each entry with 10 subtotals.
I am using Access 97 with this db, but if you can answer this in terms of Access 00 then thats fine.
Any suggestions at this stage are welcome as I have tried hundreds of ideas and am running out!
chat soon
Michael
nullgod@swift-infotech.com.au
