Create sums on different groupings?

[Ngan]

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

 

[Duane Hookom]

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.

 

[Ngan]

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

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

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

 

[Duane Hookom]

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

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

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

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

 

[Ngan]

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

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

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

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

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

 

[Duane Hookom]

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

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

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

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

:

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

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

 

[Ngan]

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

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

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

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


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

:

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

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

 

[Duane Hookom]

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

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

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

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

:

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


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

:

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

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

 

[Ngan]

Hi Duane,

Believe it or not, I did try the subreport route before posting and the
logic wouldn't work for me either Here's what I did (sorry this is
long since I try to give you all the details).

I broke that query into 2 queries as follows:

Query1:
Name Date Amount
======================
John 1/1/06 $100
John 2/1/06 $50
John 3/1/06 $75
John 4/1/06 $150

Query2
Name Date Store Qty
============================
John 1/1/06 A 10
John 1/1/06 B 15
John 2/1/06 A 7
John 3/1/06 A 5
John 3/1/06 B 3
John 4/1/06 A 8

I used Query1 as the record source for the main report and Query2 as the
record source for the subreport. I coded the Open_Report method to filter
the main report based on the date range selected. The main report is grouped
by [Name]. The sub-report is grouped by [Name], and then [Store]. There's a
calculated control in the [Store] Group Header of the sub-report that equals
to =sum([Qty]). The sub-report is placed in the [Name] Group Header section
of the main report. And here's what I ran into:

* If I link the 2 reports by using just the [Name] field: The sum of
[Amount] on main report comes out correctly based on the date range, BUT the
subreport doesn't get the filter. It just sums up all [Qty] for all dates.
For example, it will display something like this (let's assume date selection
is 1/1/06-3/1/06):

Name TotalAmount
===================
John $225 (correct)
Store TotalQty
====================
A 30 (NO! should only be 22)
B 18

* If I link the 2 reports by using both the [Name] and [Date] fields: The
sub-report only picks up the 1st matching record for every [Name] on the main
report (since it's trying to match [Name] and [Date]). Let's use the same
date range of 1/1/06-3/1/06. It will display this:

Name TotalAmount
===================
John $225 (correct)
Store TotalQty
====================
A 10 (1st matching record of John & 1/1/06)
B 15 (1st matching record of John & 1/1/06)

I understand its logic but I don't know how to get the sub-report to get the
right filtering as the main report. I tried to pass the date range to the
sub-report using the ServerFilter property but that errored up as well. BTW,
I'm using an ADP, not MDB, if that means anything.

Am I making any sense??

Thanks again
-ngan

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

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

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

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

:

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


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

:

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

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

 

[Duane Hookom]

Assuming you have a form "frmDates" with the date range in two text boxes
"txtStart" and "txtEnd". You set the criteria in the subreport's record
source to:
WHERE [Date] Between Forms!frmDates!txtStart And Forms!frmDates!txtEnd


--
Duane Hookom
MS Access MVP

Hi Duane,

Believe it or not, I did try the subreport route before posting and the
logic wouldn't work for me either Here's what I did (sorry this
is
long since I try to give you all the details).

I broke that query into 2 queries as follows:

Query1:
Name Date Amount
======================
John 1/1/06 $100
John 2/1/06 $50
John 3/1/06 $75
John 4/1/06 $150

Query2
Name Date Store Qty
============================
John 1/1/06 A 10
John 1/1/06 B 15
John 2/1/06 A 7
John 3/1/06 A 5
John 3/1/06 B 3
John 4/1/06 A 8

I used Query1 as the record source for the main report and Query2 as the
record source for the subreport. I coded the Open_Report method to filter
the main report based on the date range selected. The main report is
grouped
by [Name]. The sub-report is grouped by [Name], and then [Store].
There's a
calculated control in the [Store] Group Header of the sub-report that
equals
to =sum([Qty]). The sub-report is placed in the [Name] Group Header
section
of the main report. And here's what I ran into:

* If I link the 2 reports by using just the [Name] field: The sum of
[Amount] on main report comes out correctly based on the date range, BUT
the
subreport doesn't get the filter. It just sums up all [Qty] for all
dates.
For example, it will display something like this (let's assume date
selection
is 1/1/06-3/1/06):

Name TotalAmount
===================
John $225 (correct)
Store TotalQty
====================
A 30 (NO! should only be 22)
B 18

* If I link the 2 reports by using both the [Name] and [Date] fields: The
sub-report only picks up the 1st matching record for every [Name] on the
main
report (since it's trying to match [Name] and [Date]). Let's use the same
date range of 1/1/06-3/1/06. It will display this:

Name TotalAmount
===================
John $225 (correct)
Store TotalQty
====================
A 10 (1st matching record of John & 1/1/06)
B 15 (1st matching record of John & 1/1/06)

I understand its logic but I don't know how to get the sub-report to get
the
right filtering as the main report. I tried to pass the date range to the
sub-report using the ServerFilter property but that errored up as well.
BTW,
I'm using an ADP, not MDB, if that means anything.

Am I making any sense??

Thanks again
-ngan

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

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



:

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

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

:

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


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

:

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

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