E
edu23more
Hello.-
I am having problems with a query that it might seem simple but is
giving me a lot of problems. Most of the values given by the query are
incorrect, they all seems to be a lot higher that what it should. The
SQL for this query is:
SELECT BAKINV.ITEM, BAKINV.ITEM_DESC, Sum(BAKINV.QTY_BY_LOC) AS
SumOfQTY_BY_LOC, Sum([BAKINV.TOT_ROLCST]*[QTY_BY_LOC]) AS STOCK_VALUE,
Avg(([HISTEXT1.QTY_ISSUED]/6)) AS AvgMonthUsage, Sum(BAKINV.ON_ORD_QTY)
AS SumOfON_ORD_QTY, Sum([REQD_QTY]-[ISSUED_QTY]) AS FCST_QTY,
Avg(BAKINV.TOT_ROLCST) AS AvgOfTOT_ROLCST
FROM HISTEXT1 INNER JOIN (DEMANDS INNER JOIN BAKINV ON DEMANDS.ITEM =
BAKINV.ITEM) ON HISTEXT1.COMPONENT = DEMANDS.ITEM
WHERE (((BAKINV.ITEM_CLAS2)="3"))
GROUP BY BAKINV.ITEM, BAKINV.ITEM_DESC
HAVING (((BAKINV.ITEM) Like "v*"))
ORDER BY Sum([BAKINV.TOT_ROLCST]*[QTY_BY_LOC]) DESC;
I am linking 3 different tables. Demands, Histext1 and Bakinv. I belive
I am doing this right but I know there is something wrong. The output
of this query should be something like:
ITEM,ITEM DESC,QTY_BY_LOC,STOCK_VALUE,
AVG_MONTH_USG,ON_ORD_QTY,FCST_QTY
Any other column from this query that is not mention before, is there
because if not, it doesn't event run the query.
Any help on this will be really appreciated.
Thanks in advance
I am having problems with a query that it might seem simple but is
giving me a lot of problems. Most of the values given by the query are
incorrect, they all seems to be a lot higher that what it should. The
SQL for this query is:
SELECT BAKINV.ITEM, BAKINV.ITEM_DESC, Sum(BAKINV.QTY_BY_LOC) AS
SumOfQTY_BY_LOC, Sum([BAKINV.TOT_ROLCST]*[QTY_BY_LOC]) AS STOCK_VALUE,
Avg(([HISTEXT1.QTY_ISSUED]/6)) AS AvgMonthUsage, Sum(BAKINV.ON_ORD_QTY)
AS SumOfON_ORD_QTY, Sum([REQD_QTY]-[ISSUED_QTY]) AS FCST_QTY,
Avg(BAKINV.TOT_ROLCST) AS AvgOfTOT_ROLCST
FROM HISTEXT1 INNER JOIN (DEMANDS INNER JOIN BAKINV ON DEMANDS.ITEM =
BAKINV.ITEM) ON HISTEXT1.COMPONENT = DEMANDS.ITEM
WHERE (((BAKINV.ITEM_CLAS2)="3"))
GROUP BY BAKINV.ITEM, BAKINV.ITEM_DESC
HAVING (((BAKINV.ITEM) Like "v*"))
ORDER BY Sum([BAKINV.TOT_ROLCST]*[QTY_BY_LOC]) DESC;
I am linking 3 different tables. Demands, Histext1 and Bakinv. I belive
I am doing this right but I know there is something wrong. The output
of this query should be something like:
ITEM,ITEM DESC,QTY_BY_LOC,STOCK_VALUE,
AVG_MONTH_USG,ON_ORD_QTY,FCST_QTY
Any other column from this query that is not mention before, is there
because if not, it doesn't event run the query.
Any help on this will be really appreciated.
Thanks in advance