Re: Need Aggregate Query Help!!!
- From: "John Spencer" <spencer@xxxxxxxxx>
- Date: Tue, 2 May 2006 15:14:22 -0400
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.
"Ngan" <Ngan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:94E374C9-50D7-42DC-9654-C695C400AAE1@xxxxxxxxxxxxxxxx
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
==================
"John Spencer" wrote:
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]
Ngan wrote:
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
"John Spencer" wrote:
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
Ngan wrote:
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!
.
- References:
- Re: Need Aggregate Query Help!!!
- From: Ngan
- Re: Need Aggregate Query Help!!!
- From: John Spencer
- Re: Need Aggregate Query Help!!!
- From: Ngan
- Re: Need Aggregate Query Help!!!
- Prev by Date: Re: MS Access SQL, HELP!!!
- Next by Date: Re: Query Order By then By...
- Previous by thread: Re: Need Aggregate Query Help!!!
- Next by thread: RE: Unstable database?
- Index(es):
Relevant Pages
|
