Re: Create sums on different groupings?

Looking back at the original message, I would probably use a subreport which
would allow for two different record sources.
You could base your main report on the group by and totals query I suggested
and then add a subreport for the store totals.
--
Duane Hookom
MS Access MVP

"Ngan" <Ngan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1BBB29A3-C3AF-4FE9-90D0-F60BB8FDC374@xxxxxxxxxxxxxxxx
Hi Duane,
Hmm... I know that feature then (or I think I do :-), but that won't
produce
what I need. Not sure how I can describe this precisely without writing a
whole essay, but here's the dilemma:
- Going by your original suggestion: "...create a totals query that groups
by Name, date, and Amount. Sum this query by Name to get the total
amount.
Then add this query to your report's record source so you have the 375"
==> I
can NOT Sum this query yet because I don't know what the user will select
for
their date range yet. I used the example of [1/1/06-4/1/06], hence the
$375.
But if the user selects a different date range, the total will come out
differently. I can do the first part of your suggestion, which is
grouping
by Name, Date, and Amount, but I can't do the Summing until the report is
actually run. If I understand your suggestion correctly, you meant to
produce something like this:
Name Date Amount Store Qty
======================================
John 1/1/06 $375 A 10
John 1/1/06 $375 B 15
John 2/1/06 $375 A 7
John 3/1/06 $375 A 5
John 3/1/06 $375 B 3
John 4/1/06 $375 A 8

Unfortunately the $375 can't be predetermined. It's a dynamic figure as
well based on the date selection...

I apologize in advance if I'm way off, but I'm more than happy to provide
more details if needed.

Thanks
-ngan



"Duane Hookom" wrote:

While in the design of a report's record source, you can select to Add
Table/Query. You would need to add the query and join the appropriate
fields.
--
Duane Hookom
MS Access MVP

"Ngan" <Ngan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CB82E527-F72C-4E63-8B23-2949DDD4C3F7@xxxxxxxxxxxxxxxx
Hi Duane,

Sorry for my ignorance, but how do you "add" a query to the report's
record
source?

Here's what I got so far:
The report's record source is based on the originally stated query and
it
displays the [Store] and sum of [Qty] properly.
I created another View (I'm using ADP) that does what you suggested,
i.e.
grouping [Name], [Date], and [Amount]. I can't sum yet in this View
since
I
don't what the date range is yet. Now how do I sum the [Amount] in
this
View
based on the date selection, and then display the result in my report?

Side note: Not sure how relevant this is, but the original query I
posted
is
a much simpler version of what I have in reality. My real-life query
is a
join query of several aggregate queries that compute tons of other
information. Hence, I really am dreading hard-coding these SQL
statements
if
that's the case.

Thanks again for your great help!
-ngan

"Duane Hookom" wrote:

I didn't expect you to replace your report's record source with my
query.
My
comment was "Then add this query to your
report's record source so you have the 375".
--
Duane Hookom
MS Access MVP


"Ngan" <Ngan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0BEBF956-58C2-4D65-BD89-9871A9C7405E@xxxxxxxxxxxxxxxx
Hi Duane,
Thanks for your response. However, how do I display the [Store] and
sum
of
quantities spent on each store using your query? Each report can
only
have 1
record source, so if I use your query, which I assume produces the
following
recordset:

John 1/1/06 $100
John 2/1/06 $50
John 3/1/06 $75
John 4/1/06 $150

Then where do [Store] and [Qty] fit in the picture? The report needs
to
display both the Total Amount spent for the date range (which is
$375)
and
the total [Qty] spent on each [Store] during that date range as
well.

Thanks again in advance for any insight!
-ngan

"Duane Hookom" wrote:

You can create a totals query that groups by Name, date, and
Amount.
Sum
this query by Name to get the total amount. Then add this query to
your
report's record source so you have the 375.
--
Duane Hookom
MS Access MVP

"Ngan" <Ngan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4F10B6DC-4E4F-4F48-91BC-23D111958554@xxxxxxxxxxxxxxxx
Hi all,
I currently have the following recordset to be the data source
for a
report:
Name Date Amount Store Qty
======================================
John 1/1/06 $100 A
10
John 1/1/06 $100 B
15
John 2/1/06 $50 A
7
John 3/1/06 $75 A
5
John 3/1/06 $75 B
3
John 4/1/06 $150 A
8

The [Amount] field is the same for each combination of [Name] &
[Date].
It
means on that particular [Date], [Name] spent an [Amount] of
money.

Now I need to produce a report that displays the following
information
after
letting the user select a particular date range (for EX, from
2/1/06-4/1/06):

Name TotalAmount
=================
John $375 ($100 + $50 + $75 + $150)
Store TotalQty
==============
A 30 (10 + 7 + 5 + 8)
B 18

I am unable to make the grouping work the way I want it to.
Currently
if
I
create a calculated field on the report that equals to
Sum([Amount]),
it
will
come out to be $550 ($100 + $100 + $50 + $75 + $75 + $150)
instead
of
$375.
How do I tell the report to ignore the amount if duplicated
[Name] &
[Date]
combination?

Any help is greately appreciated!
-ngan











.

Relevant Pages

  • RE: Combo Box Problem
    ... "Your Record Source query doesn't appear to have an filter. ... record source query it won't display in the report? ... "Duane Hookom" wrote: ...
    (microsoft.public.access.reports)
  • Re: Query form coding
    ... The record source for the list box "namelst" is: ... The report record source seems to give the desired records. ... The code for the button (create the filter) works if no names have been selected in the names list box. ... I have tried to include the query when using the wizard to design the report but I keep getting an error saying I have used a table or query that is based on a table or query and when I remove the query fields from the list it allow me to continue with the report design. ...
    (microsoft.public.access.formscoding)
  • Re: Create sums on different groupings?
    ... I used Query1 as the record source for the main report and Query2 as the ... "Duane Hookom" wrote: ... You could base your main report on the group by and totals query I ...
    (microsoft.public.access.reports)
  • Re: Create sums on different groupings?
    ... I used Query1 as the record source for the main report and Query2 as the ... "Duane Hookom" wrote: ... You could base your main report on the group by and totals query I suggested ...
    (microsoft.public.access.reports)
  • Re: Generate such a report
    ... The balances appearing in report are as ... How do I add a sum to a query you've made for me? ... payables in a table called "Settlements". ... Receivable By, Receivable From, and Amount. ...
    (microsoft.public.access.formscoding)
Loading