SQL question

[Gerry Abbott]

I have a solution using two queries wich I wish to combine into a single
statement, to create a recordset.

the first which is named qryAllMovements
----------------------------------------------------------------------------
-----
SELECT tblStockItems.ProdId, tblMovements.mQuantity, tblMovements.sDate
FROM tblStockItems INNER JOIN (tblLocations INNER JOIN tblMovements ON
tblLocations.LocId = tblMovements.LocId) ON tblStockItems.stockId =
tblMovements.stockId
WHERE (((tblMovements.sDate)<#10/3/2003#) AND
((tblLocations.StImpFlag)=-1));
----------------------------------------------------------------------------
---------------
and the second

----------------------------------------------------------------------
SELECT qryAllMovements.ProdId, qryAllMovements.mQuantity
FROM qryAllMovements
GROUP BY qryAllMovements.ProdId, qryAllMovements.mQuantity
------------------------------------------------------------------------

Basically Im filtering the first using a date but leaving the date out of
the result, , then aggregating the resultss in the second. .
Any help most welcome

Gerry Abbott

[John Winterbottom]

"Gerry Abbott" <pl****@ask.ie> wrote in message
news:nz***************@news.indigo.ie...

I have a solution using two queries wich I wish to combine into a single
statement, to create a recordset.

post the table structures with some sample data and the output you need and
someone will be able to help.

[Gerry Abbott]

"John Winterbottom" <as******@hotmail.com> wrote in message
news:2h************@uni-berlin.de...

"Gerry Abbott" <pl****@ask.ie> wrote in message
news:nz***************@news.indigo.ie...

I have a solution using two queries wich I wish to combine into a single
statement, to create a recordset.

post the table structures with some sample data and the output you need

and someone will be able to help.

tblStockItems:
StockId(Key)
ProdId (number)

tblMovements
MovementId (key)
StockId (number)
sDate (date)
LocId(number)

tblProducts
ProdId (key)

Im filtering on the date in the movements table.
Each stock item has a product associated with it.
Each movement has a location associated with it.
I only include movements for locations for which a stock impact flag
(stImpFlag) is set.

Once its filtered on the date and the stockImpactFlag, I no longer need
these or the location flags.
I now have the total stock movements in and out of all stock locations
before a user defined date, forr all products.
So all i've got to do is to group the result by product, to get the net
stock total for each. (the second query)

Im really looking for a way to nest these sql statements into a single
statement, and am more interested in understanding the principles, than a
definitieve solution to this particular problem. I could not aggregate the
the query once the date remained in it for all the movements, thats' why i
ended up with two queries.

Thanks in advance.

Gerry

[John Winterbottom]

> > post the table structures with some sample data and the output you need

and

someone will be able to help.

tblStockItems:
StockId(Key)
ProdId (number)

tblMovements
MovementId (key)
StockId (number)
sDate (date)
LocId(number)

tblProducts
ProdId (key)

Im filtering on the date in the movements table.
Each stock item has a product associated with it.
Each movement has a location associated with it.
I only include movements for locations for which a stock impact flag
(stImpFlag) is set.

Your example tables don't show a locations table, and tblMovements doesn't
show a quantity. Apart from that you haven't given any sample data, so the
following is just a guess

select s.ProdID, Sum(m.Qty) as qtyMoved
from tblStockItems as s inner join
(
tblLocations as l inner join tblMovements as m on l.LocID = m.LocID
) on s.StockID = m.StockID
where l.stImpFlag=True
and m.sDate=[enterDate]
group by s.ProdID;

[John Winterbottom]

> > post the table structures with some sample data and the output you need

and

someone will be able to help.

tblStockItems:
StockId(Key)
ProdId (number)

tblMovements
MovementId (key)
StockId (number)
sDate (date)
LocId(number)

tblProducts
ProdId (key)

Im filtering on the date in the movements table.
Each stock item has a product associated with it.
Each movement has a location associated with it.
I only include movements for locations for which a stock impact flag
(stImpFlag) is set.

Your example tables don't show a locations table, and tblMovements doesn't
show a quantity. Apart from that you haven't given any sample data, so the
following is just a guess

select s.ProdID, Sum(m.Qty) as qtyMoved
from tblStockItems as s inner join
(
tblLocations as l inner join tblMovements as m on l.LocID = m.LocID
) on s.StockID = m.StockID
where l.stImpFlag=True
and m.sDate=[enterDate]
group by s.ProdID;

[Gerry Abbott]

My reply has somehow become detached from this thread!!

Here is my reply

Thanks John,
Think I've chosen an unnecesarily complex query as an example of what i'm
trying to solve. I've been building SQL using the QBE design view. The
inclusion of the 'where' clause as you outlined below in your example, in
place of the 'group' allows me to get close to where I want to be, by
allowing me to group, aggregate and filter in the same query, thus being
able to remove the date.
A simpler example which works is below. This shows me the aggegrate total
for each ProdId which has a total, ie is not null. .

----------------------------------------------------------------------------
--------------------------------------------
TEST1
SELECT tblStockItems.ProdId, Sum(tblMovements.mQuantity) AS SumOfmQuantity
FROM tblStockItems INNER JOIN tblMovements ON tblStockItems.stockId =
tblMovements.stockId
WHERE (((tblMovements.LocId)=sysconst(3)) AND ((tblMovements.sDate) Between
[parameter1] And [parameter2]))
GROUP BY tblStockItems.ProdId;
----------------------------------------------------------------------------
---------------------------------------------

I then tried adding my tblProducts, and was forced to change the joins
between tblStockItems and tbl Movements, and between tblProducts and
tblStockIetms, to Left Joins.
----------------------------------------------------------------------------
---------
TEST1
SELECT tblProducts.ProdId, Sum(tblMovements.mQuantity) AS Total
FROM tblProducts LEFT JOIN (tblStockItems LEFT JOIN tblMovements ON
tblStockItems.stockId = tblMovements.stockId) ON tblProducts.ProdId =
tblStockItems.ProdId
WHERE (((tblMovements.LocId)=sysconst(3)) AND ((tblMovements.sDate) Between
[parameter1] And [parameter2]))
GROUP BY tblProducts.ProdId
ORDER BY tblProducts.ProdId;
----------------------------------------------------------------------------
--------

But the result of this is the same as the first one above. and I don't get
the full list of products, only those for which the aggregate value is non
null.
So i'm left with generating another query, by adding the tblProducts, and
creating a left join with the prodId from either of the above queries, to
display all the products, including the ones for which there is no stock
items and hence no movements to compute. This is what I want.

----------------------------------------------------------------------------
-------
SELECT tblProducts.ProdId, Test1.Total
FROM Test1 RIGHT JOIN tblProducts ON Test1.ProdId = tblProducts.ProdId;
----------------------------------------------------------------------------
-------
But this leaves me once agin with two queries, one derived from the other.
Im hoping this makes my problem a little clearer.
Gerry Abbott


"John Winterbottom" <as******@hotmail.com> wrote in message
news:2h************@uni-berlin.de...

post the table structures with some sample data and the output you

need and

someone will be able to help.
tblStockItems:
StockId(Key)
ProdId (number)

tblMovements
MovementId (key)
StockId (number)
sDate (date)
LocId(number)

tblProducts
ProdId (key)

Im filtering on the date in the movements table.
Each stock item has a product associated with it.
Each movement has a location associated with it.
I only include movements for locations for which a stock impact flag
(stImpFlag) is set.

Your example tables don't show a locations table, and tblMovements doesn't
show a quantity. Apart from that you haven't given any sample data, so the
following is just a guess

select s.ProdID, Sum(m.Qty) as qtyMoved
from tblStockItems as s inner join
(
tblLocations as l inner join tblMovements as m on l.LocID =

m.LocID ) on s.StockID = m.StockID
where l.stImpFlag=True
and m.sDate=[enterDate]
group by s.ProdID;