Result of subquery sum based on fields of same record

[Olaf Richter]

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

 

[OfficeDev18 via AccessMonster.com]

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,

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

 

[Olaf Richter]

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

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,

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
I 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

 

[OfficeDev18 via AccessMonster.com]

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,

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
[quoted text clipped - 44 lines]