Sub-Query issues

[enigma54]

Hi,

First off I stress I have this working perfectly using 3 visual queries. I would really like to further understand SQL nested queries though and combine the three visual queries into one SQL query. (Hope that makes sense!)

Basically I can insert the sub-query into the query as an expression but I dont understand how to reference fields within the sub query from the parent query.

The purpose of the query is to calculate the length difference in aluminium blocks between two stock takes (dates). It therefore shows the usage of these

The first query (Query1) (shown below) looks up all the lengths of all the different aluminium blocks processed at stock take A:

SELECT TblHoneycombBlock.BlockID, TblStockTake.StockTakeLength, TblStockTake.StockTakeDateID, TblHoneycombBlock.OriginalLength
FROM TblHoneycombBlock RIGHT JOIN TblStockTake ON TblHoneycombBlock.BlockID = TblStockTake.BlockNoID
GROUP BY TblHoneycombBlock.BlockID, TblStockTake.StockTakeLength, TblStockTake.StockTakeDateID, TblHoneycombBlock.OriginalLength
HAVING (((TblStockTake.StockTakeDateID) Like [Forms]![FrmReports]![CboUsageStockTakeDate1]));

The second query (Query2) (shown below) looks up the lengths of all the different blocks processed at stock take date B:

SELECT TblHoneycombBlock.BlockID, TblStockTake.StockTakeLength, TblStockTake.StockTakeDateID, TblHoneycombBlock.OriginalLength
FROM TblHoneycombBlock RIGHT JOIN TblStockTake ON TblHoneycombBlock.BlockID = TblStockTake.BlockNoID
GROUP BY TblHoneycombBlock.BlockID, TblStockTake.StockTakeLength, TblStockTake.StockTakeDateID, TblHoneycombBlock.OriginalLength
HAVING (((TblStockTake.StockTakeDateID) Like [Forms]![FrmReports]![CboUsageStockTakeDate2]));

Finally, the third query pulls the information together. It finds the difference in length of all matching blocks. If a block is found in Query1 (most recent date) that doesnt exist in Query2 (oldest date) then it assumes the block was processed somewhere in between the two stock takes and therefore Takes the value in Query 1 away from the original length of the block. If a block exists in Query2 but not in Query1 then a value of 0 is inserted (as the block has been used up completely). The information is then grouped using a concatenated string representing the type of aluminium each block is made from and each block that belongs to that type is added together. This is shown below

SELECT [Density] & "-" & [CellSizeNumerator] & "/" & [CellSizeDenominator] & " - " & [FoilThickness] & [CoreType] & "-" & [Grade] AS Type, Sum(IIf([Query2]![BlockID] Not In ([Query1]![BlockID]),"0",IIf([Query2]![BlockID] In ([Query1]![BlockID]),[Query2]![StockTakeLength]-[Query1]![StockTakeLength],[Query1]![OriginalLength]-[Query1]![StockTakeLength]))) AS [Usage]
FROM TblGrade RIGHT JOIN (TblCoreType RIGHT JOIN ((TblHoneycombBlock LEFT JOIN Query2 ON TblHoneycombBlock.BlockID = Query2.BlockID) LEFT JOIN Query1 ON TblHoneycombBlock.BlockID = Query1.BlockID) ON TblCoreType.ID = TblHoneycombBlock.NormalPerforated) ON TblGrade.ID = TblHoneycombBlock.GradeID
GROUP BY TblGrade.Grade, TblHoneycombBlock.Density, [Density] & "-" & [CellSizeNumerator] & "/" & [CellSizeDenominator] & " - " & [FoilThickness] & [CoreType] & "-" & [Grade]
HAVING (((Sum(IIf([Query2]![BlockID] Not In ([Query1]![BlockID]),"0",IIf([Query2]![BlockID] In ([Query1]![BlockID]),[Query2]![StockTakeLength]-[Query1]![StockTakeLength],[Query1]![OriginalLength]-[Query1]![StockTakeLength])))) Is Not Null))
ORDER BY TblGrade.Grade, TblHoneycombBlock.Density;

Sorry this is ever so slightly long winded.

Thanks a lot

Dave

 

[Kryst51]

SELECT [Density] & "-" & [CellSizeNumerator] & "/" & [CellSizeDenominator] & " - " & [FoilThickness] & [CoreType] & "-" & [Grade] AS Type, Sum(IIf([Query2]![BlockID] Not In ([Query1]![BlockID]),"0",IIf([Query2]![BlockID] In ([Query1]![BlockID]),[Query2]![StockTakeLength]-[Query1]![StockTakeLength],[Query1]![OriginalLength]-[Query1]![StockTakeLength]))) AS [Usage]
FROM TblGrade RIGHT JOIN (TblCoreType RIGHT JOIN ((TblHoneycombBlock LEFT JOIN Query2 ON TblHoneycombBlock.BlockID = Query2.BlockID) LEFT JOIN Query1 ON TblHoneycombBlock.BlockID = Query1.BlockID) ON TblCoreType.ID = TblHoneycombBlock.NormalPerforated) ON TblGrade.ID = TblHoneycombBlock.GradeID
GROUP BY TblGrade.Grade, TblHoneycombBlock.Density, [Density] & "-" & [CellSizeNumerator] & "/" & [CellSizeDenominator] & " - " & [FoilThickness] & [CoreType] & "-" & [Grade]
HAVING (((Sum(IIf([Query2]![BlockID] Not In ([Query1]![BlockID]),"0",IIf([Query2]![BlockID] In ([Query1]![BlockID]),[Query2]![StockTakeLength]-[Query1]![StockTakeLength],[Query1]![OriginalLength]-[Query1]![StockTakeLength])))) Is Not Null))
ORDER BY TblGrade.Grade, TblHoneycombBlock.Density;

I don't know that the SELECT portion of your statement should be set up like that. See this link for a tutorial, this should get you going in the right direction. You also my consider designing one in the access QBE, either what you are working on, or something simpler just to see, and going to SQL view. If you use what you are already working on you can just copy and paste the SQL view text where you want it.

EDIT: Here is another link

 

[enigma54]

Hi,

Thanks for the fast response.

I'll certainly take a look at the link posted. I think the difference with mine is that it involves two nested queries. I am also unsure how to setup a relationship between the nested queries and the main query (which currently I have setup in the QBE). I have to start at the beginning though to have a chance of understanding it !

As it is the query setup works. Infact I designed it in Access' Visual Query editor (QBE) - I didnt actually know the name for that until now! I am just interested.

The first part of the select takes field names from the query and concatenates the field values into a string. This produces a field called type:

[Density] & "-" & [CellSizeNumerator] & "/" & [CellSizeDenominator] & " - " & [FoilThickness] & [CoreType] & "-" & [Grade]

When viewed it looks similar to this:

1.8-3/4 - 25N-3003

These are standard aluminium block types that are used within the company.

Once again thanks for the reply and the two links. Much appreciated

Dave

 

[Kryst51]

What I am getting at is that your SELECT portion doesn't perform those operations, it pulls in fields, so if you have a field called "Type" that is built by that calculation then that is what need to be in the SELECT, not the calculation itself. At least that is my understanding.

Edit: I just read up on this and realized that I am wrong.... Ignore this post.

 

[enigma54]

No problems! Its good to have input anyway. Glad we both learned something from this

 

[vbaInet]

Sometimes you don't need to use a subquery. A simple join to the other two queries may do the trick. But I don't understand what you're trying to achieve? When you say join the three queries what do you actually mean?

By the way, I think Query1 and Query2 can be one query if you use LIKE ... OR LIKE ... The key there is the OR operator. However, your query isn't using wildcards so using LIKE is a waste of resources. Just use the equal to sign instead. [fieldname] = [combobox1] OR [fieldname] = [combobox2]

 

[enigma54]

vbaInet
Thanks for the reply.

Ive attached a much slimmed down version of the database. It most likely explains an awful lot better than I ever could.

I simply couldnt think of a way it could be done in just one query. However, I am open to improvements or suggestions if you fancy taking a quick glance

Thanks for your time

 

[vbaInet]

I was just taking a look at your query. You don't need to merge it into one because your current method is more effecient than using three subqueries.

But there's on thing I would like to point out with respect to the Usage field. You currently have it as:
Usage: Sum(IIf([Query2]![BlockID] Not In ([Query1]![BlockID]),"0",IIf([Query2]![BlockID] In ([Query1]![BlockID]),[Query2]![StockTakeLength]-[Query1]![StockTakeLength],[Query1]![OriginalLength]-[Query1]![StockTakeLength])))

I don't think the bit in red wil ever be True. Here's a revised version:
Usage: Sum(IIf(IsNull([Query2]![BlockID]) Or IsNull([Query1]![BlockID]), 0, [Query2]![StockTakeLength]-[Query1]![StockTakeLength]))

See if that works the same

 

[enigma54]

Thanks a lot for that. It does work the same in my environment

Thanks again for the assistance!

 

[vbaInet]

You're welcome!

Just to elaborate, there are cases where a subqueryies would be the right choice but in your case, because you're referencing different fields from the subqueries results, you would need separate subqueries to get the field's data, whereas with your current method it's pulling from a query that has already generated that record so it's simply referencing that field on the current record. If that makes sense

 

[enigma54]

Thanks for the explanation. I figured there must be a time and place for both but its just a case of learning when!

Thanks again for the assist

 

[vbaInet]

No problemo!