[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access SQL Statement

Posted on 2007-10-14
11
Medium Priority
?
178 Views
Last Modified: 2010-03-20
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
0
Comment
Question by:Evan Cutler
  • 7
  • 4
11 Comments
 
LVL 19

Expert Comment

by:frankytee
ID: 20075546
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
 
LVL 9

Author Comment

by:Evan Cutler
ID: 20075623
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
 
LVL 19

Expert Comment

by:frankytee
ID: 20075744
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 9

Author Comment

by:Evan Cutler
ID: 20075833
Sorry,  all 20 records showed.
Tried Max and First.
Anything else?
0
 
LVL 19

Expert Comment

by:frankytee
ID: 20075850
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
 
LVL 19

Expert Comment

by:frankytee
ID: 20075860
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
 
LVL 19

Expert Comment

by:frankytee
ID: 20083092
arcee123, did it work?
0
 
LVL 9

Author Comment

by:Evan Cutler
ID: 20090392
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
 
LVL 19

Accepted Solution

by:
frankytee earned 2000 total points
ID: 20091145
?why have you changed your query?
has your question changed?
0
 
LVL 9

Author Comment

by:Evan Cutler
ID: 20091204
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
 
LVL 19

Expert Comment

by:frankytee
ID: 20092965
thanks, glad i could help
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question