Result of subquery sum based on fields of same record

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Olaf Richter - 22 Mar 2006 18:09 GMT

Hello there,

I use a query to display purchases by origin (PurchOrigin),
seller(CustomerCounter), buyer(PurchCompID)
and quantity during [Start Date] and [End Date].

It is grouped by PurchOrigin, CustomerCounter, PurchCompID and sums the
quantity.

I need the quantity sum of the same period for previous year for each record
in above query

For that I use following subquery

PrevPurch: NZ((Select Sum([Purch]) From tblPurchases
Where PurchOrigin=tblPurchases.PurchOrigin
and CustomerCounter=tblPurchases.PurchSeller
and PurchCompID=tblPurchases.PurchCompID
and tblPurchases.PurchBookDate Between dateadd("yyyy",-1,[Start Date]) And
dateadd("yyyy",-1,[End Date]);),0)

It all works fine as long as there is no seller with business in different
origins. Then it sums sales from all origins.

How can I limit the where clause in subquery for PurchOrigin,
CustomerCounter and PurchCompID to particular record in "main" query ?

Any tip is appreciated

Thank you
Olaf

Reply to this Message

OfficeDev18 - 22 Mar 2006 18:51 GMT

Hi, Olaf,

I would make tblPurchases an INNER JOIN to the PrevPurch subquery, joined on
tblPurchases.PurchOrigin = MainTable.PurchOrigin.

I.e., (Select Sum([Purch]) From tblPurchases INNER JOIN MainTableNameHere ON
tblPurchases.PurchOrigin = MainTableNameHere.PurchOrigin WHERE.......

Hope this helps,

Sam

>Hello there,
>
[quoted text clipped - 27 lines]
>Thank you
>Olaf

Signature

Sam

Reply to this Message

Olaf Richter - 23 Mar 2006 11:46 GMT

Hello Sam,

thank you for the help, unfortunately I still did not succeed. Apart from receiving a syntax error (I still have to work on) I wonder about the following:

I have to join my query with the tblPurchases on three fields (PurchOrigin, CustomerCounter, PurchCompID)
Is it at all possible to join a subquery within a query(x) to a table using contents from within just this query(x)?

My resulting three fields PurchOrigin, CustomerCounter and PurchCompID from query(x) are the link fields to tblPurchases to calculate field PrevPurch

Where am I wrong?

Olaf

>>> OfficeDev18 via AccessMonster.com<u14095@uwe> wrote on 22.03.06:
>
[quoted text clipped - 44 lines]
>>Thank you
>>Olaf

Reply to this Message

OfficeDev18 - 24 Mar 2006 18:30 GMT

Hi, Olaf,

and sorry for the delay.

After reviewing both our posts, I don't see any error. Would you mind doing a
copy-and-paste here of your entire SQL statement, and also tell where you get
the syntax error. Does the cursor stop at any word or character, etc?

Sam

>Hello Sam,
>
[quoted text clipped - 14 lines]
>>>Thank you
>>>Olaf

Signature

Sam

Reply to this Message