0

It's really hard for me to sort this thing out. I've tried every possible combinations on the FROM part but I can't seem to get it. I've tried comma, mixing inner join & left join then this.

The [give some date pls] is a parameter prompt (for MS Access 2007 only) so ignore it.

It says there's something wrong about my FROM part.

SELECT
    TJI.Serial AS ID,
    FIRST(TJI.Product) AS Product,
    ROUND((((SUM(TJI.JobOrder) * FIRST(TJI.ProductPackQTY)) - IIF(ISNULL(SUM(TJI.TheAmount)), 0, SUM(TJI.TheAmount))) / FIRST(TJI.STDYield)) / FIRST(TJI.MinBatch), 0) * FIRST(TJI.MinBatch) AS Batch,
    IIF(Batch <= 0, FIRST(MinBatch), Batch) AS FinalBatch

FROM TBL_JO AS TJ INNER JOIN (SELECT * FROM TBL_JOITEMS LEFT JOIN RET_STOCKS ON (Serial = TheItemSerial AND TJ.Date = TheDate) AS TJI ON TJ.Serial = TJI.JO_Serial

WHERE (TJ.Date = [give some date pls])
GROUP BY TJI.Serial
jestrange
  • 271
  • 3
  • 15

2 Answers2

1

I have had to guess how you want your joins on the derived table, but try something on these lines:

SELECT
TJI.Serial AS ID,
FIRST(TJI.Product) AS Product,
ROUND((((SUM(TJI.JobOrder) * 
    FIRST(TJI.ProductPackQTY)) - 
    IIF(ISNULL(SUM(TJI.TheAmount)), 0, SUM(TJI.TheAmount))) / 
    FIRST(TJI.STDYield)) / 
    FIRST(TJI.MinBatch), 0) * FIRST(TJI.MinBatch) AS Batch,

IIF(Batch <= 0, FIRST(MinBatch), Batch) AS FinalBatch

FROM TBL_JO AS TJ
INNER JOIN (SELECT * FROM TBL_JOITEMS j
            LEFT JOIN RET_STOCKS r
            ON j.Serial =r.TheItemSerial) AS TJI 

ON TJ.Serial = TJI.JO_Serial AND TJ.Date = TJI.TheDate
GROUP BY TJI.Serial

If you get an error like the above and no part of you SQL is highlighted, always suspect the subquery or derived table. In this case, you are using two tables, but your join was not referencing the tables involved. In addition, you had a stray bracket -- ( -- in your derived table that made nonsense of it.

BTW, it is generally considered a bad idea to use *, it is best to name the relevant fields (columns).

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • Thanks for the response but the J.Date is from TBL_JO.Date (instead of TBL_JOITEMS.Date) and is outside the () parenthesis. So I think this is the one causing the error, but I don't have any alternatives. It's like the 3 Tables are co-checking each other in comparing values. And I'll keep your tips in mind. – jestrange Jun 17 '12 at 09:09
  • You just cannot do that, move the reference to the outside table outside the derived table and add another join. It is possible you want a subquery, but it is hard to tell without data. – Fionnuala Jun 17 '12 at 09:11
  • I will upload the db. EDIT:Unfortunately can't upload it because I remembered it's too big 2GB+. – jestrange Jun 17 '12 at 09:19
  • How can I join these three tables into one table. With not requiring a match with RET_STOCKS (So I used LEFT OUTER JOIN) to show an item (instead a "0" value if no match). – jestrange Jun 17 '12 at 09:26
1

After experimenting for quite sometime now, I finally found the answer I've been searching for. Thanks for the hints and tips you've given Remou. It sure is a big help.

I've swapped the TBL_JO with RET_STOCKS and instead of using INNER JOIN, I've used the other method in defining relationship through WHERE.

SELECT JOSerial, JODate, PSerial, Product, Category, JobOrder, Yield, Pack, MinBatch, IIF(ISNULL(TheAmount),0,TheAmount) AS ReturnStocks
FROM
    (SELECT TJ.Serial AS JOSerial, TJ.Date AS JODate, TJI.Serial AS PSerial, TJI.Product AS Product, TJI.Category AS Category, TJI.JobOrder AS JobOrder, TJI.STDYield AS Yield, TJI.ProductPackQTY AS Pack, TJI.MinBatch AS MinBatch 
        FROM TBL_JO AS TJ, TBL_JOITEMS AS TJI
        WHERE TJ.Serial=TJI.JO_Serial)
AS TJX
LEFT JOIN
    RET_STOCKS AS RS
ON
    (TJX.JODate=RS.TheDate)
    AND
    (TJX.PSerial=RS.TheItemSerial)
jestrange
  • 271
  • 3
  • 15