Total values in report footer

[AccessWorld]

My report footer is not recognizing the values in a text box that I populate from a combobox using the [field].column(1) formula. I get a text result in the text box but I wish to sum, or count, by how many times a particular value comes up. The value is not stored in a field but displays in the report based on the combobox

=[acnumber].column(1) formula.

I refer to the populated column text box that shows the aircraft model type in my report detail section by attempting to add up each time a particular aircraft model string shows up.

So...aircraft number is the stored [acnumber] table field on the report. The aircraft model number (747-400) is determined by the column (1) function associated with aircraft tail number.

...example aircraft number is n123QZ (not real) combobox also has the aircraft model and ID. [ACNumber].Column(1) returns the correct 747-400 model/type.

I want to total each aircraft model that shows up in the detail section in the report footer. All formulas I have tried do not appear to be able to total a text box populated in this manner and not stored as a field.

Results hoped for:
Model Total
MD-11F 5
747-400 4
767-300 10
757 25

Perhaps there is a way to save a text bix populated in this way to a field for that same record? Then I might be able to easily calculate this info in the report footer?

Any ideas are most welcome?
Thanks
Stuck in MEM

 

[theDBguy]

Hi. Did you say you have a Combobox in a report? Why? How are you populating this Combobox? Aren't those values stored in a table?

 

[arnelgp]

can you use DCount()?

 

[AccessWorld]

Hi. Did you say you have a Combobox in a report? Why? How are you populating this Combobox? Aren't those values stored in a table?

Yes....because it allows me to do this to select the aircraft model

=[combo45ACNumber].[column](1)
The model number is not part of the query (for now...can I populate the AC model in the query?.

 

[AccessWorld]

Got it...ditched the combo. Combined two different tables in query. All is good now in calculations. Didnt think about...your comment/question did the trick...thank you from across the ocean. Hope all in your family is doing ok during COVID. dave

 

[AccessWorld]

Is there a way to iterate through each model type to obtain sums for each without having to write a sum formula for each aircraft type?

 

[arnelgp]

you create another Total Query that will Count your aircraft by Type:
create a report for this Query.
add the New report as sub-report to your Current report.

 

[AccessWorld]

Will give dcount a try tomorrow

 

[AccessWorld]

And try the total query in a subreport.

 

[AccessWorld]

Used a cross tab query in a subreport. All is well...thank you all!

 

[theDBguy]

Used a cross tab query in a subreport. All is well...thank you all!

Congratulations! Glad to hear you got it sorted out. Good luck with your project.