Complex Report with similar fields

[zmbaker]

I currently have 5 querys to create a report that is housed in excel. I open the query and copy and paste the data into excel for each of the 5. The reason being is that several of the fields are the same but the difference is the criteria in each query is different. Is it possible to make a report that uses several of the same fields but each having different criteria?

[pbaldy]

Two options come to mind: using subreports for each of the 5 queries and using a UNION query to pull all 5 into 1.

[June7]

What are the different criteria? Why report in Excel? I wonder if Access report Sorting & Grouping feature could accomplish what you are doing with 5 queries.

[zmbaker]

The Union Query solved about 98% of what I am trying to accomplish. The final missing part is that one of the queries contained a sum function on the datasheet. Is it possible in a report to only use certain cells from a query? Or alter the first query to provide the same results by using a sum function in the design view.

[June7]

What do you mean by 'certain cells' - only certain records?

Certainly report can do summary calcs.

[zmbaker]

This is the result of my Union query.


The bottoms 4 lines are accurate and are what I am looking for. However the top 18 need to be combined some how in the following. The first column needs to count them to total 18, the second column needs to sum them to equal $473565.96

I cant provide a database of this for confidentiallity purposes.

[June7]

Back to my question in post 3.

An expression in footer section can be like:
Count(IIf(TVCNo LIKE "UNKNOWN*", SumOfEstDeviceServiceAnnualAmount))
Sum(IIf(TVCNo LIKE "UNKNOWN*", SumOfEstDeviceServiceAnnualAmount,Null))

What is the confidentiality issue? Can you make copy and delete confidential data (name, ssn, phone, email)?

Or post a sample of raw data.

[zmbaker]

If I could pick certain records yes. I tried both of the above formulas and neither of them work, they both produce a #error

[June7]

Maybe put both fieldnames in [], although Access should do that for you.

All I can say is that the expressions have been successful for others. Without knowing your data, can't really be more specific.