Counting Number of Records Related to a Parent Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am pulling a report from a query based on 4 tables.

[Report] 1:M [Maintenance] 1:M [Composite Table] M:1 [User]

The report is grouped and sorted as follows:

Report
Maintenances on the Report
User associated with the maintenance

I am trying to include a text box in the report group header that shows the
number of maintenances performed on the report.

I have tried several different things including some suggestions in other
threads with no luck. If I count from the query I get incorrect results
since it counts the duplicate information needed to keep track of the many
users. (For instance, 1 maintenance with 4 users returns 4).

Any suggestions?
 
Hi.
What you could do is qreate a separate totals query where you group by
maintenance and user and count for totals.
than you can insert this query in your report and use the totals for the
counts.
Hope this helps.
Fons
 
darrep said:
I am pulling a report from a query based on 4 tables.

[Report] 1:M [Maintenance] 1:M [Composite Table] M:1 [User]

The report is grouped and sorted as follows:

Report
Maintenances on the Report
User associated with the maintenance

I am trying to include a text box in the report group header that shows the
number of maintenances performed on the report.

I have tried several different things including some suggestions in other
threads with no luck. If I count from the query I get incorrect results
since it counts the duplicate information needed to keep track of the many
users. (For instance, 1 maintenance with 4 users returns 4).
Click to expand...


Add a text box named txtMaintNum to the Maintenance group
header or footer. Set tis controls source expression to =1
and its RunningSum property to Over Group. The report group
footer text box can then display the total using the
expression =txtMaintNum
 
This is a follow-up question to the related topic posted below.

I am not trying to include a text box in the report header to show the total
number of maintenances included in the report which would look something like
this:

Report Header - show count of records in maintenance group
Report Group
Maintenance Group - Count of records in this group
User Group - DO NOT include detail records
in this section


I am pulling a report from a query based on 4 tables.

[Report] 1:M [Maintenance] 1:M [Composite Table] M:1 [User]

The report is grouped and sorted as follows:

Report
Maintenances on the Report
User associated with the maintenance

I am trying to include a text box in the report group header that shows the
number of maintenances performed on the report.

I have tried several different things including some suggestions in other
threads with no luck. If I count from the query I get incorrect results
since it counts the duplicate information needed to keep track of the many
users. (For instance, 1 maintenance with 4 users returns 4).
Click to expand...
Click to expand...
 
darrep said:
This is a follow-up question to the related topic posted below.

I am not trying to include a text box in the report header to show the total
number of maintenances included in the report which would look something like
this:

Report Header - show count of records in maintenance group
Report Group
Maintenance Group - Count of records in this group
User Group - DO NOT include detail records
in this section


I am pulling a report from a query based on 4 tables.

[Report] 1:M [Maintenance] 1:M [Composite Table] M:1 [User]

The report is grouped and sorted as follows:

Report
Maintenances on the Report
User associated with the maintenance

I am trying to include a text box in the report group header that shows the
number of maintenances performed on the report.

I have tried several different things including some suggestions in other
threads with no luck. If I count from the query I get incorrect results
since it counts the duplicate information needed to keep track of the many
users. (For instance, 1 maintenance with 4 users returns 4).
Click to expand...
Click to expand...
Click to expand...


Sorry, but I can't figure out what you want here. Could you
post a short sample of of a report that demonstrates what
you're trying to achieve?
 
Marsh -- Here is a sample of the information I am attempting to display

Report Header
[List of Criteria entered for the underlying query]
# Results: ### [This is the count that I am after]

Group Header (Report Info)
Report Num: 135 Report Name: TM Table Lookup

Group Header (Maintenance Info)
Request Date: 2005/01/01 Test
Date: 2005/01/01
[Other Maintenance Info]
[Other Maintenance Info]

Detail (User Info)
Sponsor
Joe Schmoe
Bill Bill
Tester
Bill Bill

The count I am after is the number of records in the maintenance info group
to be displayed in the Report Header as a "snapshot" of the results. If I
use the "Count" fucntion, I get a count of BOTh the maintenance info and the
user info (in the above sample, the count would return 4). Other threads
relevant suggested using the Running Total Over Group feature, but I have not
found a way to include that number in the header. Thanks for looking at this
for me!
 
darrep said:
Marsh -- Here is a sample of the information I am attempting to display

Report Header
[List of Criteria entered for the underlying query]
# Results: ### [This is the count that I am after]

Group Header (Report Info)
Report Num: 135 Report Name: TM Table Lookup

Group Header (Maintenance Info)
Request Date: 2005/01/01 Test
Date: 2005/01/01
[Other Maintenance Info]
[Other Maintenance Info]

Detail (User Info)
Sponsor
Joe Schmoe
Bill Bill
Tester
Bill Bill

The count I am after is the number of records in the maintenance info group
to be displayed in the Report Header as a "snapshot" of the results. If I
use the "Count" fucntion, I get a count of BOTh the maintenance info and the
user info (in the above sample, the count would return 4). Other threads
relevant suggested using the Running Total Over Group feature, but I have not
found a way to include that number in the header.
Click to expand...


OK, this is rather tricky because you want to display a
value before it is calculated.

First, you must have a control somewhere on the report that
uses Pages. A text box with =Page & " of " & Pages will do
fine. This will force Acces to format the report twice,
once to figure out how many pages and again to fill in the
value of Pages.

Next, use the RunningSum text box with expression =1 in the
maintenance header or footer section.

Now, create a module level variable named lngMaintCount and
add a line of code to the report Footer's Format event to
copy the running sum value to the variable.

Finally, add a line of code to the report header section's
Format event that copies the variable to the report header
text box.

Whew...
 
Back
Top