Count All Records From SubReport Place Value On Main Report

[JohnSmith]

The main report shows a list of products with a list of cars that have been involved in an accident.



Example Layout of Main report with integrated Sub report


Total Cars: 1

Accident1

• Car1

Accident2

• CarA

Accident3

• Car5
  
• CarD
  
• Car3

What I have done

on the Sub report I placed a TextBox in the sub Report Header
Sub Report Name: rptOwnersSubform
TextBox Name: textCountOwners
Control Source: =Count("*")

on Main report I placed a TextBox in the Report Header
Main Report Name: rptAccidents
TextBox Name: text88
Control Source: =[rptOwnersSubform].[Report]![textCountOwners]


Issue
I would like to count the number of cars that have been in an accident and place the number on the main report.
The problem is the total number of cars is always 1. It seems it counts the number of cars per accident, which is not what I need.
I want the total number of cars for the entire list, which should be 5.

Test File.zip


Thank you for any information you all can provide.

[June7]

You say customers are unique - there would not be same customer under multiple products?

Suggest you provide db for analysis. Follow instructions at bottom of my post.

[JohnSmith]

I have edited my original post with the file.
Thank You

[June7]

Maybe not optimized data structure. What you call a CarID is really ID of a person. Cars can be driven by different persons and persons can drive multiple cars. Cars and persons can be involved in multiple accidents.

Should rptOwnersSubform be named rptOwnersSubreport ?

Yes, the count is only the total records associated with the accident record that is displayed on main report. Calc in main report header is pulling from first accident and same calc in main report footer pulls from last accident. Total cars will have to be obtained with a subquery or DCount() expression.

SELECT tblAccidents.*, (SELECT Count("*") FROM tblCars) AS TotCars FROM tblAccidents;

Unfortunately that query fails as report RecordSource. So just bind report to table and expression in textbox: =DCount("*","tblCars")


Both reports can just be bound to tables instead of queries because there is no calculating or filtering in queries.

[Gicu]

Why not just use a dCount("*","YourSubReportRecordSource")?

Cheers,

[JohnSmith]

Thank you for the suggestions.

I cannot bind it to the table, it must be bound from a queried result.


I used

=DCount("*",[rptOwnersSubform].[Report]![LastName])

in the Control Source of the main report named rptAccidents
It's still showing #Error


I have "cleaned up" the file so as to be a bit more normalized.
Keep in mind this is not the real "DB" I use, I just created this one for this one problem that's kicking my a$$.
I have attached it below.

Better Test File.zip

[Gicu]

Please have a look at the attached file. If you need to limit the records in the subreport save its record source as a query then replace the table name in the dCount with the new query name.

Cheers,
Vlad

[JohnSmith]

Thank you everyone.
I have another issue, but I'll create another thread.
This issue is solved.

Thank You!!