Need Aggregate Query Help!!!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ngan - 29 Apr 2006 18:18 GMT

Hi all,
I currently have 2 queries that produce the following recordsets:

Query1
============================
Name Date AmountSpent
John 2/1/2006 $100
John 3/1/2006 $150
John 4/1/2006 $50
Mary 2/15/2006 $200

Query2
============================
Name Date Store QtyPurchased
John 2/1/2006 A 5
John 2/1/2006 B 7
John 3/1/2006 A 10
John 4/1/2006 A 15
Mary 2/15/2006 A 21
Mary 2/15/2006 B 5

[AmountSpent] in Query1 and [QtyPurchased] in Query2 have virtually no
correlation. These are just 2 queries that produce different information on
the employees.

Now I need to produce another recordset that will look similar to this
(after the user selects a particular date range - let's say from
2/1/2006-4/1/2006):

Name TotalSpent Store TotalPurchased
John $300 A 30
John $300 B 7
Mary $200 A 21
Mary $200 B 5

I've literally tried all ways possible to come up with an aggregate query
that will produce the above requirement at no avail. Can someone please
guide me to the right direction?

Thanks much!

Reply to this Message

John Spencer - 29 Apr 2006 19:14 GMT

One method would be to do the aggregation in two separate queries and then join
the two aggregate queries. In SQL that would look something like the following.

Query One:
SELECT [Name]
, Store
, Sum(AmountSpent) as TotalAmount
FROM SomeTable
WHERE [Date] Between #2/1/06# and #4/1/06#
GROUP BY [Name], Store

Query Two:
SELECT [Name]
, Store
, Sum(QtyPurchased) as TotalQTY
FROM SomeTable
WHERE [Date] Between #2/1/06# and #4/1/06#
GROUP BY [Name], Store

Query Three:
SELECT A.[Name]
, A.Store
, TotalAmount
, TotalQTY
FROM Query1 as A INNER JOIN Query2 as B
On A.[Name] = B.[Name] And A.Store = B.Store

> Hi all,
> I currently have 2 queries that produce the following recordsets:
[quoted text clipped - 36 lines]
>
> Thanks much!

Reply to this Message

Ngan - 01 May 2006 23:35 GMT

Hi John,
Thanks so much for your response. However, your answer confirmed my fear of
not knowing how to apply this final query (Query Three) to a report (Perhaps
I should post this in the Reports forum?). I eventually need to produce a
report that displays similar information as stated my original post.
Currently the user has a form where they can select the date range, and all
of my other reports open up with a ServerFilter that read the date range from
this form. How can I construct this report based on the queri(es) you
suggested?
Thanks again
-ngan

> One method would be to do the aggregation in two separate queries and then join
> the two aggregate queries. In SQL that would look something like the following.
[quoted text clipped - 63 lines]
> >
> > Thanks much!

Reply to this Message

John Spencer - 02 May 2006 00:46 GMT

Build the three queries as stored queries. Use the third query as the record
source for the report.

If you have the form open, you can refer to the values in its controls.
SELECT [Name]
, Store
, Sum(AmountSpent) as TotalAmount
FROM SomeTable
WHERE [Date] Between [Forms]![Your Form Name]![Your Control Name] and
[Forms]![Your Form Name]![Your Other Control Name]
GROUP BY [Name], Store

By the way, I assume that Name and Date are NOT your real field names. If they
are you should be aware that they are reserved words in Access and MUST be
surrounded by brackets OR fully named with the table name preceding the field
name - [SomeTable].[Date]

> Hi John,
> Thanks so much for your response. However, your answer confirmed my fear of
[quoted text clipped - 75 lines]
> > >
> > > Thanks much!

Reply to this Message

Ngan - 02 May 2006 17:56 GMT

Hi John,
Thanks again for your time! I do know about referencing an opened form's
controls' values and the reserved words. This is just a completely
simplified version of what I have in real life.

Several points I'm confused about though:

Your originally suggested Query One has the [Store] field in it, which
shouldn't be there since I don't have [Store] in my Query 1. My Query 1 &
Query 2 are related by [Name] and [Date]. I didn't bring it up last time
since I thought I could just modify your suggested queries as follows (T-SQL
syntax):

Query One:
SELECT EmpName, SUM(Amount) AS TotalAmount
FROM dbo.Amounts
WHERE (DateStamp BETWEEN @BDate AND @EDate)
GROUP BY EmpName
Query Two:
SELECT EmpName, Store, SUM(Qty) AS TotalQty
FROM dbo.Stores
WHERE (DateStamp BETWEEN @BDate AND @EDate)
GROUP BY EmpName, Store
Query Three:
SELECT A.[Name], B.Store, TotalAmount, TotalQTY
FROM [Query One] as A INNER JOIN [Query Two] as B
On A.[Name] = B.[Name]

I understand that conceptually those 3 queries work perfectly. But in my
ADP file, after creating 2 "functions" called Query 1 and Query 2 (which work
fine the way they're supposed to) and trying to join them together, the
following SQL statement is created automatically:
SELECT [Query 1].EmpName, [Query 1].TotalAmount, [Query 2].Store,
[Query 2].TotalQty FROM dbo.[Query 1](,) [Query 1] INNER JOIN dbo.[Query
2](,) [Query 2] ON [Query 1].EmpName = [Query 2].EmpName

That SQL statement gets back with an "Incorrect syntax" critical error that
I have no clue how to fix. I assume it's because it's trying to join 2
parameter functions????

The next question is I'm not quite understanding where you want me to put
that last SQL statement (in your last post)??? VB code in the Open_Report
event?

Thanks so much again!
-ngan

==================

> Build the three queries as stored queries. Use the third query as the record
> source for the report.
[quoted text clipped - 92 lines]
> > > >
> > > > Thanks much!

Reply to this Message

John Spencer - 02 May 2006 20:14 GMT

I am stuck myself. I think that with T-SQL you would have to do something
else than what I am suggesting. I was using Access SQL with JET backend.

You might be able to do something like the following. My T-SQL is rusty and
this may or may not work.

SELECT A.[Name], B.Store, TotalAmount, TotalQTY
FROM
(SELECT EmpName, SUM(Amount) AS TotalAmount
FROM dbo.Amounts
WHERE (DateStamp BETWEEN @BDate AND @EDate)) as A
INNER JOIN
(SELECT EmpName, Store, SUM(Qty) AS TotalQty
FROM dbo.Stores
WHERE (DateStamp BETWEEN @BDate AND @EDate)
GROUP BY EmpName, Store) as B
ON A.[Name] = B.[Name]

If this fails, perhaps you should start a new thread - be sure to emphasize
you are doing this with T-SQL and in an ADP.

> Hi John,
> Thanks again for your time! I do know about referencing an opened form's
[quoted text clipped - 160 lines]
>> > > >
>> > > > Thanks much!

Reply to this Message