Access SQL Statement

I have a SQL Statement:

SELECT Jobs.JobNo, Items.ItemID, ServiceCategories.CatName, Items.ItemNo, ServiceCategories.CatNo
FROM ServiceCategories INNER JOIN (Services INNER JOIN (Jobs INNER JOIN Items ON Jobs.JobID = Items.JobID) ON Services.SerNo = Items.SerNo) ON ServiceCategories.CatNo = Services.CatNo
GROUP BY Jobs.JobNo, Items.ItemID, ServiceCategories.CatName, Items.ItemNo, ServiceCategories.CatNo
HAVING (((Jobs.JobNo)=69766))
ORDER BY Items.ItemNo, ServiceCategories.CatNo DESC;

This gives me this:
JobNo      ItemID                       CatName      ItemNo      CatNo
69766      1223137                        Press              1           201
69766      1223138                        Offset      1           102
69766      1223136                        Plate                1             6
69766      1223135                   Item                1              0
69766      1223141                        Press               2            201
69766      1223142                        Env: Business      2      113
69766      1223140                        Plate                2               6
69766      1223139                        Item                2               0
69766      1223146                        Cut                3              401
69766      1223147                        Cut                 3              401
69766      1223145                        Press       3            201
69766      1223148                        Cover              3           110
69766      1223144                        Plate                3                6
69766      1223143                        Item                3                 0
69766      1223153                        Press              4      201
69766      1223154                        Env: Business      4      113
69766      1223152                        Plate               4                 6    
69766      1223151                        Item                4                 0
69766      1223150                        Graphics      5            1
69766      1223149                        Item                5                0


Based on my orentation of ItemNo, and CatNo, I have the ItemNo ascending and the CatNo Descending.
Based on this I have the Max value on the first line of each unique Item No.  There are 5 groups of items, and I want the table to just have the top CatNo's.  For example.

JobNo      ItemID                       CatName      ItemNo      CatNo
69766      1223137                        Press              1           201
69766      1223141                        Press               2            201
69766      1223146                        Cut                3              401
69766      1223152                        Plate               4                 6    
69766      1223150                        Graphics      5            1

I only want one ItemNo, and the max value of CatNo for each one, leaving a table with five records.

Help?  Thanks in advance.
I bow to the great EE Gods :)

Evan
LVL 9
Evan CutlerVolunteer Chief Information OfficerAsked:
Who is Participating?
 
frankyteeCommented:
?why have you changed your query?
has your question changed?
0
 
frankyteeCommented:
try
SELECT Jobs.JobNo, Items.ItemID, ServiceCategories.CatName, Items.ItemNo, Max(ServiceCategories.CatNo) as CatNo
FROM ServiceCategories INNER JOIN (Services INNER JOIN (Jobs INNER JOIN Items ON Jobs.JobID = Items.JobID) ON Services.SerNo = Items.SerNo) ON ServiceCategories.CatNo = Services.CatNo
GROUP BY Jobs.JobNo, Items.ItemID, ServiceCategories.CatName, Items.ItemNo
HAVING (((Jobs.JobNo)=69766))
ORDER BY Items.ItemNo, ServiceCategories.CatNo DESC;
0
 
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
Access returned error:

"You tried to execute a query that does not include the specified expression 'ServiceCategories.CatNo' as part of an aggregate function."

Ok, What Did I do?
Thanks
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
frankyteeCommented:
opps, had ServiceCategories.CatNo DESC in the order by clause
try
SELECT Jobs.JobNo, Items.ItemID, ServiceCategories.CatName, Items.ItemNo, Max(ServiceCategories.CatNo) as CatNo
FROM ServiceCategories INNER JOIN (Services INNER JOIN (Jobs INNER JOIN Items ON Jobs.JobID = Items.JobID) ON Services.SerNo = Items.SerNo) ON ServiceCategories.CatNo = Services.CatNo
GROUP BY Jobs.JobNo, Items.ItemID, ServiceCategories.CatName, Items.ItemNo
HAVING (((Jobs.JobNo)=69766))
ORDER BY Items.ItemNo
0
 
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
Sorry,  all 20 records showed.
Tried Max and First.
Anything else?
0
 
frankyteeCommented:
then split it into 2 queries,
the first being your original, call it say "qBaseQuery"
the 2nd, a new query based on your first:

SELECT JobNo, ItemID, CatName, ItemNo, Max(qBaseQuery.CatNo) as CatNo
from qBaseQuery
GROUP BY JobNo, ItemID, CatName, ItemNo
ORDER BY ItemNo
0
 
frankyteeCommented:
or if you want it still in one query:
SELECT z.JobNo, z.ItemID, z.CatName, z.ItemNo, Max(z.CatNo) as CatNo
from
(SELECT Jobs.JobNo, Items.ItemID, ServiceCategories.CatName, Items.ItemNo, ServiceCategories.CatNo
FROM ServiceCategories INNER JOIN (Services INNER JOIN (Jobs INNER JOIN Items ON Jobs.JobID = Items.JobID) ON Services.SerNo = Items.SerNo) ON ServiceCategories.CatNo = Services.CatNo
GROUP BY Jobs.JobNo, Items.ItemID, ServiceCategories.CatName, Items.ItemNo, ServiceCategories.CatNo
HAVING Jobs.JobNo =69766
) as z
GROUP BY z.JobNo, z.ItemID, z.CatName, z.ItemNo
ORDER BY z.ItemNo
0
 
frankyteeCommented:
arcee123, did it work?
0
 
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
almost....

SELECT Items.ItemNo, ServiceCategories.CatName, ServiceCategories.DepNo
FROM ServiceCategories INNER JOIN (Services INNER JOIN (Jobs INNER JOIN Items ON Jobs.JobID = Items.JobID) ON Services.SerNo = Items.SerNo) ON ServiceCategories.CatNo = Services.CatNo
GROUP BY Items.ItemNo, Jobs.JobNo, ServiceCategories.CatName, ServiceCategories.DepNo
HAVING (((Items.ItemNo) In (SELECT [ItemNo] FROM [Items] As Tmp GROUP BY [ItemNo] HAVING Count(*)>0)) AND ((Jobs.JobNo)=69766))
ORDER BY Items.ItemNo, ServiceCategories.DepNo DESC;

Gives me:
ItemNo      CatName      DepNo
1      Press      2
1      Offset      1
1      Item      0
1      Plate      0
2      Press      2
2      Env: Business      1
2      Item      0
2      Plate      0
3      Cut      4
3      Press      2
3      Cover      1
3      Item      0
3      Plate      0
4      Press      2
4      Env: Business      1
4      Item      0
4      Plate      0
5      Graphics      0
5      Item      0

Where I was.....
But Adding MAX to ItemNo (the first column)
Gives me:
MaxOfItemNo      CatName      SumOfDepNo
1      Offset      1
3      Cut      8
3      Cover      1
4      Press      8
4      Env: Business      2
4      Plate      0
5      Item      0
5      Graphics      0

huh?
Then First gives me:
FirstOfItemNo      CatName      SumOfDepNo
1      Press      8
1      Offset      1
1      Plate      0
1      Item      0
2      Env: Business      2
3      Cut      8
3      Cover      1
5      Graphics      0

Still, huh?  why 4 1's, and where's 4?  :P

Lastly,

SELECT Items.ItemNo, First(ServiceCategories.CatName) AS FirstOfCatName, Max(ServiceCategories.DepNo) AS MaxOfDepNo
FROM ServiceCategories INNER JOIN (Services INNER JOIN (Jobs INNER JOIN Items ON Jobs.JobID = Items.JobID) ON Services.SerNo = Items.SerNo) ON ServiceCategories.CatNo = Services.CatNo
GROUP BY Items.ItemNo, Jobs.JobNo
HAVING (((Items.ItemNo) In (SELECT [ItemNo] FROM [Items] As Tmp GROUP BY [ItemNo] HAVING Count(*)>0)) AND ((Jobs.JobNo)=69766))
ORDER BY Items.ItemNo, Max(ServiceCategories.DepNo) DESC;

Seems to get me the closest.
FirstOfCatName      ItemNo      MaxOfDepNo
Item      1      2
Item      2      2
Item      3      4
Item      4      2
Item      5      0

But "Item" doesn't match DepNo....
Help?

0
 
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
No, tried to menuever some variables.
I decided to use your second recommendation:
SELECT z.JobNo, z.ItemID, z.CatName, z.ItemNo, Max(z.CatNo) as CatNo
from
(SELECT Jobs.JobNo, Items.ItemID, ServiceCategories.CatName, Items.ItemNo, ServiceCategories.CatNo
FROM ServiceCategories INNER JOIN (Services INNER JOIN (Jobs INNER JOIN Items ON Jobs.JobID = Items.JobID) ON Services.SerNo = Items.SerNo) ON ServiceCategories.CatNo = Services.CatNo
GROUP BY Jobs.JobNo, Items.ItemID, ServiceCategories.CatName, Items.ItemNo, ServiceCategories.CatNo
HAVING Jobs.JobNo =69766
) as z
GROUP BY z.JobNo, z.ItemID, z.CatName, z.ItemNo
ORDER BY z.ItemNo

but I added Items.ItemID = (insert for/next loop variable) and
Top 1 at the start.

This gave me what I needed.  

Thanks for everything!!!
This helped IMMENSELY.
0
 
frankyteeCommented:
thanks, glad i could help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.