Totals from Parameter Query

[gguy]

I am trying to create a report to show totals for several different inspections types based on a date range.

Currently, I only have three fields in the query, inspection type (group by), inspection type (count) and inspection date (where, date range).

I am trying to use a Dlookup to show the totals on the report. If I remove the date parameter from the query and only get the grand total for each inspection type from the table then the Dlookup will work. If the date parameter is left in the query than all I get is an #error on the report .

Should I be using something besides a Dlookup to show these to show these totals for the proper date range.

This is the query I am using;

SELECT inspections.inspection_type, Count(inspections.inspection_type) AS CountOfinspection_type
FROM inspections
WHERE (((inspections.inspection_date)>=[begining date] And (inspections.inspection_date)<=[ending date]))
GROUP BY inspections.inspection_type;

GGuy

 

[Rich]

Why can't you just base the report on the query?

 

[gguy]

Because I don't want to show the totals for all the inspection types. Only some of the inspection types.
Also, when I get this fixed, I will need to show other totals(same date range) from a different query on the same report.

 

[Rich]

Then use a sub report for the other table/query and filter out the results you don't want, Domain Aggregate functions are slow, and in this case unnecessary

 

[gguy]

I see what your saying but I need some help with the filtering part. I am pretty new to Access report creation.

The is the reason I like the Dlookup function is that it has a where clause.

For example, I will want to show the totals for an inspection type called AG-use but not Non-Ag-use or maybe not any other type but Ag-use.

If I add a field to the report for count_of_inspection_type how do I filter out all other inspection types and only get the total for Ag-use.

Thanks, GGuy

 

[Rich]

=Sum(Iif([count_of_inspection_type ]="Ag-use",1,0)) you should also avoid spaces in field names and use an alias for Totals fields

 

[gguy]

Thanks Rich that worked, I see the total for the inspection type that I am requesting and only that inspection type.

I have another problem though, the sum total for inspection type repeats for each record that is queried. For example, if the total was 1600 ag-use records, then the field will show 1600 but also 1600 times.

 

[Rich]

Put the control in a group or Report footer