VBFnewcomer
Mar 4th, 2009, 11:42 PM
Hi,
I have a Table1 which has person's names. There is another table2 which stores person's phones. both are connected by perID. One person can have more than one phone.
I made a query to pull data from each table (lets say name & phones). It produces one row for each phone a person has. so assuming that there are 20 persons and each has 2 phone, the net result would be 20*2=40 rows.
I tried to make a report based on Query1 made above grouped on person's name and had a running sum in the group header (i.e name) (thinking of having s.no against each person). I had set the control source to =Count(Query1![Name]) and the running sum in the Data tab to over group.
When I run the report I find that the count increases by 2 because there are 2 phones for each group. Any idea how to make the increase only by each group. Or simply said how to make the S.Nos. for each name. something like below..
1. Name1
p1
p2
2. Name2
p1
p2
3. Name 3
p1
p2
Note:
I have used 2 phones for illustration, there could be more/less than 2 for each person.
There is other groupings above the name (which I had avoided mentioning for simplicity)
I have a Table1 which has person's names. There is another table2 which stores person's phones. both are connected by perID. One person can have more than one phone.
I made a query to pull data from each table (lets say name & phones). It produces one row for each phone a person has. so assuming that there are 20 persons and each has 2 phone, the net result would be 20*2=40 rows.
I tried to make a report based on Query1 made above grouped on person's name and had a running sum in the group header (i.e name) (thinking of having s.no against each person). I had set the control source to =Count(Query1![Name]) and the running sum in the Data tab to over group.
When I run the report I find that the count increases by 2 because there are 2 phones for each group. Any idea how to make the increase only by each group. Or simply said how to make the S.Nos. for each name. something like below..
1. Name1
p1
p2
2. Name2
p1
p2
3. Name 3
p1
p2
Note:
I have used 2 phones for illustration, there could be more/less than 2 for each person.
There is other groupings above the name (which I had avoided mentioning for simplicity)