Max value in multi table query 1

[byurow]

OK, it's official! I have no clue what I am trying to do

Lets see if I can explain it. I am trying to get a report of all Open cases based on the highest priority. I can't figure out how to get only the max value to show. I created a query to find all open batches. I then take that query and create a new query adding the Priority information. How do I limit the second query (or even combine them into just one query) so that only the highest priority shows up? The following are my queries:

QryAllOpenBatchs:

SELECT tblBatchNo.BatchNoID, tblBatchNo.BatchNo, tblProduct.ProdID, tblProduct.ProdCode, tblSKU.SKUNo, tblSKUFillLines.FilledUnits, tblSKUFillLines.FilledGals
FROM ((tblProduct INNER JOIN (tblBatchNo INNER JOIN tblBatchNoProduct ON tblBatchNo.BatchNoID = tblBatchNoProduct.BatchNoID) ON tblProduct.ProdID = tblBatchNoProduct.ProdID) INNER JOIN tblSKU ON tblProduct.ProdID = tblSKU.ProdID) INNER JOIN tblSKUFillLines ON tblSKU.SKUID = tblSKUFillLines.SKUID
WHERE (((tblSKUFillLines.FilledUnits) Is Null)) OR (((tblSKUFillLines.FilledGals) Is Null));

QryOpenBatchesBasedOnPriority:

SELECT P.[PriorityCodeID],C.[PriorityCode], B.[BatchNoID], B.[BatchNo], B.[ProdID], B.[ProdCode], B.[SKUNo], P.[DaysAvailable]

FROM (tblPriorityCode C
INNER JOIN tblProductPriorityCode P ON C.[PriorityCodeID] = P.[PriorityCodeID])
INNER JOIN QryOpenBatches B ON P.[BatchNoID] = B.[BatchNoID]

WHERE P.PriorityCodeID IN
(Select MAX(P1.PriorityCodeID)
From tblPriorityCode C1 INNER JOIN tblProductPriorityCode P1
ON C.[PriorityCodeID] = P.[PriorityCodeID]
Where C1.[PriorityCodeID] = C.[PriorityCodeID]
AND P1.[PriorityCodeID] = P.[PriorityCodeID] )

ORDER BY B.[BatchNo], B.[ProdNo];

I get a syntax error on this second query in the WHERE clause.

Any help would be greatly appreciated!

Brenda

 

[SonOfEmidec1100]

In the select MAX statement in the join conditions you are referencing tables C and P should be C1 and P1, and the where clause is not needed - I think.

WHERE P.PriorityCodeID IN
(Select MAX(P1.PriorityCodeID) AS PriorityCodeID
From tblPriorityCode C1 INNER JOIN tblProductPriorityCode P1
ON C1.[PriorityCodeID] = P1.[PriorityCodeID])

 

[byurow]

Thank you so much for your reply. You got rid of my syntax error, but I am not getting any data. I should get 4 rows. Any ideas what I am doing wrong?

 

[SonOfEmidec1100]

If you run the query without the where clause and order descending by priority does this confirm the 4 rows for the highest priority

SELECT P.[PriorityCodeID],C.[PriorityCode], B.[BatchNoID], B.[BatchNo], B.[ProdID], B.[ProdCode], B.[SKUNo], P.[DaysAvailable]

FROM (tblPriorityCode C
INNER JOIN tblProductPriorityCode P ON C.[PriorityCodeID] = P.[PriorityCodeID])
INNER JOIN QryOpenBatches B ON P.[BatchNoID] = B.[BatchNoID]

ORDER BY P.[PriorityCodeID] DESC;

 

[byurow]

I get 14 rows. The 3 (sorry not 4) rows are there along with the history rows. I just need the most recent.

Thanks again!

 

[SonOfEmidec1100]

Hmm what I suspect is the MAX is returning a value that has no rows in the QryOpenBatches so you need this in the where select. If not running out of ideas...

WHERE P.PriorityCodeID IN
(Select MAX(P1.PriorityCodeID) AS PriorityCodeID
From tblPriorityCode C1 INNER JOIN tblProductPriorityCode P1
ON C1.[PriorityCodeID] = P1.[PriorityCodeID]
INNER JOIN QryOpenBatches B1 ON P1.[BatchNoID] = B1.[BatchNoID])

 

[byurow]

Hmmmm Now I'm back to the syntax error! Oh well, maybe I will see if I can do this in a form. What I am trying to do is fill a list box with the information. Currently the list box fills in with all rows (my original query had 9 rows). I am trying to get it down to the 3. Maybe a DMax will solve the issue. I will re-post this in the forms forum.

Thanks so much for all of your help!

 

[SonOfEmidec1100]

Sorry I left out the brackets (too much time with sql server)

WHERE P.PriorityCodeID IN
(Select MAX(P1.PriorityCodeID) AS PriorityCodeID
From (tblPriorityCode C1 INNER JOIN tblProductPriorityCode P1
ON C1.[PriorityCodeID] = P1.[PriorityCodeID])
INNER JOIN QryOpenBatches B1 ON P1.[BatchNoID] = B1.[BatchNoID])

 

[byurow]

I should have caught that myself Oops. Well that got me down to 1 record! I wonder if maybe I am going about this backwards! Maybe I should be concentrating on just getting the max values for the one table: tblProductPriorityCode - I would need to find the max value of PriorityCodeID for EACH ProdID listed in the table. I know this is possible, but I'm not sure how to do it.

Any suggestions?

 

[byurow]

I GOT IT!!!!!! All this time I was trying to link all of the tables before I found the unique records! I created a query just on the one table tblProductPriorityCode and was able to get the max value. I then created a second query with all of the other fields and the criteria needed to find just the open batches. Then I linked the two together! Works great!

Thank you so much for all of your help. The posts were incredibly helpful to me (especially the last one which opened my eyes to what I was doing wrong!)

Thanks again!

Brenda