Calculations in Groupings 1

[avancuren]

I want to subtract the sum of all records in a table based on the value in a certain field from the the sum of all records that have a different value in that field (subtract the INs from the OUTs) as part of a report.

I have been able to create queries that produce the desired record sets, but when I make the query the control source for the text box on my report, they do not work.

=(select [weight] from "Cert" where [In/Out]="in")

I get a #Name? result on the report.

I can group the data correctly using the grouping function, but don't know how to subtract one group from the other.

I can also create the groupings I want with a crosstab query, but, again, don't know how to subtract the sum of one group from the sum of another group.

 

[dhookom]

Do you have some table and field names as well as sample records and results? Your question is a bit un-clear to at least my old brain.

You can't use a SQL string as the control source of a control. You can use domain aggregate functions like:
=DSum("[Weight]","[Cert]","[In/Out]='in'")

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[avancuren]

That fact that I cannot use a SQL string as the source for a control explains much of my problem. Thank you for that.

I have a series of entries in a table that are weight certificates representing a number of different commodities moving in or out.

The table is Certs. Relevant fields are [Commodity], [In/Out], and [Weight].

I want to produce a report that groups by Commodity, then groups by In/Out beneath that. I want a sum under each subgroup (In/Out) and then I want to subtract the total outs from the total ins for each commodity.

Using the grouping function in a report I can produce the grouping that I want and can produce the In or Out sums using a =Sum([Weight]) in the [In/Out] footer.

What I cannot figure out how to do is subtract the sum of the Out dynaset from the sum of the In dynaset in the Commodity footer. When I use =(DSum("[Weight]","[Certs]","[In/Out]='In'")-DSum("[Weight]","[Certs]","[In/Out]='Out'")) in the [Commodity] footer it appears to be giving me the sum for the entire table rather than for the [Commodity] dynaset.

 

[dhookom]

Try:
=Sum(Abs([In/Out]="In") * Weight) - Sum(Abs([In/Out]="Out") * Weight)

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[avancuren]

This solution worked great. But now I have another thing to add to the mix.

My report is based on a parameter query that returns either data on a specific commodity or data on all commodities.

I've now added a parameter to the query to limit the dates that are returned. Results are either all dates or a date range.

The In-Out calculation, which is a perpetual inventory figure, needs to be calculated on all transactions, regardless of date, even if the report contains only transactions for a date range.

The report needs to be something like:
COMMODITY #1
Inbound transaction #1 in date range
Inbound transaction #2 in date range
Inbound transaction #3 in date range
---------------------------------------
Total of inbound transactions in date range

Outbound transaction #1 in date range
Outbound transaction #2 in date range
Outbound transaction #3 in date range
----------------------------------------
Total of outbound transactions in date range


Total of all inbound transactions for commodity (regardless of date range) - Total of all outbound transactions for commodity (regardless of date range)


COMMODITY #2
...

I am confused because I want the total to reflect the grouping by commodity that the report does, but I need it to include all dates.

Any suggestions for the best way to do this?

 

[dhookom]

Create a totals query that creates the numbers you need by Commodity for all dates. Add this query to your report's record source query and join the commodity fields.

Also, consider dumping parameter prompt queries in favor of references to controls on forms.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[accessewbie]

I am working with a coworker on her database. She has a report that groups by Contract Admin. Under that she counts by the Type of Report for each contract admin. She wants a total that will sum all the reports that were counted above for each contract admin. looks like this

Contract Admin
Type of Paperwork Counts each different type
Total Totals the above

Contract Admin
Type of Paperwork Counts each different type
Total Totals the above

and it repeats. Right now I get the grouping right on Contract Admin and it counts the type of paperwork but I can get the total to only appear at the end of the grouping for each admin. Does this make sense? Please help us and thanks in advance.

 

[dhookom]

I don't quite understand your/her question. Do you want a summary list of values and counts in a report footer?

I think you need a subreport but I can't be sure.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]