Multiple Queries on one report?! My head hurts now! 1

[NerdyOne]

I have a hum dinger of a report I'm trying to run. I will try to be brief with my description.

One report called Cash Collection Report. Within that report I need to have several different totals in it. I have a single table with 4 fields I need to query from. They are: Receipt_NO (unique ID for each receipt), For (the name of the room rented) Amount_Paid, and Tax.

What I would like is each line on the report looks at each record that has the same room name (For) and a total of all matching record's Amount_Paid and Tax.

I know this sounds confusing but I help somebody has the answer!

Example of the Report:

1. Weight Room ....... Amount: Tax:


The query would look for all records with the field "For" that equals "Weight Room" and add up all the Amount_Paid and place the toal in the box labeled "Amount:". Same thing for the "Tax" field.

... Told ya it was a hum dinger!

 

[ahmun]

Sounds like you have a table like this?

Receipt_No   RoomFor       Amount_Paid   Tax
----------   -----------   -----------   -----
101          Weight Room   55.55         5.55
102          Lounge        25.55         5.55
103          Pool/Spa      75.55         5.55
104          Weight Room   15.55         5.55
105          Weight Room   15.55         5.55
106          Weight Room   15.55         5.55

and now you want to to have a report that lists each entry as well as the totals?

So a sample report will look like:

Receipt_No   RoomFor       Amount_Paid   Tax
----------   -----------   -----------   -----
101          Weight Room     55.55        5.55
104          Weight Room     15.55        5.55
105          Weight Room     15.55        5.55
106          Weight Room     15.55        5.55
---------------------------------------------
Total                       102.20       22.20

Correct me if I'm wrong... but if this is the case, you can...
[ol][li]use a simple query to filter out just the Weight Room entries, or have the report's data source set to the table, and you can set the report's filter property to filter only "Weight Room"[/li]
[li]Create a tabular report (use Access Wizard if you are lazy or whatnot)[/li]
[li]Then, in the report's page footer (or, you can create a "For" footer with Groupings and Sorting Dialog Box) add a textbox with the following datasource:

=sum([ControlName])

, where "ControlName" is the name of the textbox that you have in your report to display the Amount_Paid (i.e.

=sum([Amount_Paid])

)[/li]
[li]Do Likewise for the Tax Field[/li][/ol]

In Access Reports, the

sum

function totals all the values... Hunt around for the above mentioned concepts in this forum or in Access Help... If you get stuck, feel free to ask me

Earnie Eng

 

[NerdyOne]

You are on the right track. While I try out your advise, let me explain my report a bit further.

Your example of my table is dead on the money (great job). But here's what I need the report to pull:

Name Total Amount Tax
------- ------------- ----

Weight Room [Total of Amounts] [Total of tax]

Meeting Room [Total of Amounts] [Total of tax]


So my problem is, the query to which my report is attached to can not be filtered by each room. In my head the control of each "amount" text box should read something like this:

=Sum([Cash Collection Query]![Amount_Paid]) And [Cash Collection Query]![For] = "Room Name"

Please forgive my lack of writing expression

Thanks for the help.

 

[ahmun]

The solution may be just a few steps away.

[ol][li]again, create a tabular report[/li]
[li]however, this time, you will not put anything in the "Detail" section of the report[/li]
[li]instead, delete all the contents of the detail section, and open up the "Sorting and Grouping" panel[/li]
[li]set your "For" field as one of the groups.[/li]
[li]Under that "For" field grouping, make sure the "Group Footer" OR "Group Header" property is set to "yes"[/li]
[li]Set the "Group On" propery to "Each Value"[/li][/ol]

Now you are set up to do a totals report.

[ol][li]In that "For" header/footer, place a textbox of just the "For" Field (the room name)[/li]
[li]Put in a textbox with the data source set to

=Sum([Amount_Paid])

and likewise for Tax[/li]
[li]arrange the format to your heart's content and you should be good to go[/li][/ol]

In your previous post you said, the query to which my report is attached to can not be filtered by each room. By using the sorting and grouping aspect of the report, you are able to filter by room without creating a complicated query.

Earnie Eng

 

[NerdyOne]

You're steps and descriptions were exact and MOST helpful. A feather in your hat my good sir!