Alias woes

[Bogzla]

so, I have created a working SQL statement that summarises data from a bunch of different tables (it's a stock monitoring application so it all has to do with levels of stock, numbers of parts processed etc...):

SELECT tblPPIn.BatchID, 
tblPartDescriptions.DrawingNumber AS [Drawing Number], 
tblOrder.IssueNumber AS [Issue Number], 
tblPartDescriptions.Description AS [Description], 
tblPPIn.Qty AS [Total Booked In], 
IIf(IsNull((SELECT Sum(tblPPProcessed.QtyProcessed) AS SumOfQtyProcessed FROM tblPPProcessed WHERE tblPPProcessed.BatchID = tblPPIn.BatchID;)),'0',(SELECT Sum(tblPPProcessed.QtyProcessed) AS SumOfQtyProcessed FROM tblPPProcessed WHERE tblPPProcessed.BatchID = tblPPIn.BatchID;)) AS [Total Qty Processed], 
IIf(IsNull((SELECT Sum(tblPPOut.Qty) As sumofQtyOutPass FROM tblPPOut WHERE tblPPOut.BatchID = tblPPIn.BatchID AND tblPPOut.IQCPass = 'PASS';)),'0',(SELECT Sum(tblPPOut.Qty) As sumofQtyOutPass FROM tblPPOut WHERE tblPPOut.BatchID = tblPPIn.BatchID AND tblPPOut.IQCPass = 'PASS';)) AS [Total Good Parts Taken], 
IIf(IsNull((SELECT Sum(tblPPOut.Qty) As sumofQtyOutUninspected FROM tblPPOut WHERE tblPPOut.BatchID = tblPPIn.BatchID AND tblPPOut.IQCPass = 'UNINSPECTED';)),'0',(SELECT Sum(tblPPOut.Qty) As sumofQtyOutUninspected FROM tblPPOut WHERE tblPPOut.BatchID = tblPPIn.BatchID AND tblPPOut.IQCPass = 'UNINSPECTED';)) AS [Total Uninspected Parts Taken], 
IIf(IsNull((SELECT Sum(tblPPProcessed.QtyPass) AS SumOfQtyPass FROM tblPPProcessed WHERE tblPPProcessed.BatchID = tblPPIn.BatchID;)),'0',(SELECT Sum(tblPPProcessed.QtyPass) AS SumOfQtyPass FROM tblPPProcessed WHERE tblPPProcessed.BatchID = tblPPIn.BatchID;)) AS [Total Passes], 
IIf(IsNull((SELECT Sum(tblPPProcessed.QtyFailEtching) + Sum(tblPPProcessed.QtyFailCutOut) + Sum(tblPPProcessed.QtyFailFlatness) + Sum(tblPPProcessed.QtyFailHandling) + Sum(tblPPProcessed.QtyFailOther) AS SumofFails FROM tblPPProcessed WHERE tblPPProcessed.BatchID = tblPPIn.BatchID;)),'0',((SELECT Sum(tblPPProcessed.QtyFailEtching) + Sum(tblPPProcessed.QtyFailCutOut) + Sum(tblPPProcessed.QtyFailFlatness) + Sum(tblPPProcessed.QtyFailHandling) + Sum(tblPPProcessed.QtyFailOther) AS SumofFails FROM tblPPProcessed WHERE tblPPProcessed.BatchID = tblPPIn.BatchID;))) AS [Total Fails], 
[Total Booked In]-[Total Qty Processed]-[Total Uninspected Parts Taken] AS [Total Unprocessed Parts Remaining], 
[Total Passes]-[Total Good Parts Taken] AS [Total Good Parts Remaining], 
[Total Passes]/[Total Qty Processed] AS [Overall Yield] 
FROM tblPPIn INNER JOIN (tblPartDescriptions INNER JOIN tblOrder ON tblPartDescriptions.DrawingNumber = tblOrder.DrawingNumber) ON tblPPIn.BatchID = tblOrder.BatchID 
ORDER BY tblPPIn.BatchID ASC;

don't worry about the details, it works as it is above...

now I only want to display data for batches where there are some parts left, so I've tried adding the following WHERE clause:

WHERE ([Total Good Parts Remaining] + [Total Unprocessed Parts Remaining]) <> 0

should work fine, right?
nope, we get the (all too familiar) 'Enter parameter value: Total Good Parts Remaining' (and the same for 'Total Unprocessed Parts Remaining'), despite the fact I have used these aliases in the previous SQL with no problem...

Is there any way around this or do I have to create a big-ass WHERE statement complete with the subqueries I've already used previously?

This is seriously annoying me today... any help would be massively appreciated!

cheers,
Bogzla

 

[KenHigg]

My guess is that the iif() statement is causing the entire sql string to be interpreted by the vba engine first and it see's the brackets as params... Not sure what the best work around for that would be in this case.

???

ken

 

[Bogzla]

Yeah I figured it must be something to do with the way the SQL is processed in Access...
(I don't know much about the low-level stuff)

i got a (kind of) work-around because the query is called from excel, and the records displayed in a worksheet, I just hide the rows where both values are zero. Far from elegant but it's done the job...

 

[Simon_MT]

I know this may sound rather rudimentary but to overcome these situations I would be tempted to create Queries for each of the Table with a Left Join away from tblOrder using BatchID. If No record is found the value would be zero but the BatchID would always be present. Then just link these (sub)queries together in another query to get your report values. This way you can also create a tabulated form based on the Batch.ID with all the transactions that are that you want to see.

I would also be tempted to consider the cardinal sin of having non-normalised data and have Parts_Left on the tblOrder. This way you not only can you report but you get an overview that can be Form based.

Simon

 

[pbaldy]

The problem is that JET evaluates the WHERE clause before the SELECT clause, so it doesn't know about the alias values yet. Solutions include recreating the calculation in the WHERE clause or basing a second query on the original one and put the criteria there. Given your situation, I'd probably do the latter.

 

[Bogzla]

The problem is that JET evaluates the WHERE clause before the SELECT clause, so it doesn't know about the alias values yet. Solutions include recreating the calculation in the WHERE clause or basing a second query on the original one and put the criteria there. Given your situation, I'd probably do the latter.

hmm, I guess that does make sense for less complex queries that call many records.

I don't suppose there is a way to change the order in which the SQL is evaluated?

I have been trying to stay away from storing too many queries within Access but it's starting to look like I might have to rethink my approach....
think it may be time to do some speed tests

anyhow, big thanks for the input everyone, I at least understand what is going on now

 

[KenHigg]

Of course you can build the entire sql statement on the fly instead of embedding the bracketed stuff for the var's...


ken

 

[Simon_MT]

The reason the WHERE is executed this maybe to do with the old SQL although both these examples evaluate the same number of records:

SELECT Originals.[Orig Old Stock], Originals.[Orig Artist Ref] FROM Originals, Artists WHERE Originals.[Orig Artist Ref])=Artists.Artist

Is the same as

SELECT Originals.[Orig Old Stock] FROM Originals INNER JOIN Artists ON Originals.[Orig Artist Ref] = Artists.Artist

Simon