Solved

Limit SQL Output in Excel

Posted on 2011-03-10
3
235 Views
Last Modified: 2012-05-11
I've got a Microsoft Query set up between a sql database and excel 2010 that I'm trying to filter out some information.  Attached is a copy of the report in its current state.  You'll see that the attached customer has two jobs in my system, all of which have multiple steps (Work Centers).  What I'd like to obtain is limiting the output of this script so that it only shows the first Work Center, which varies depending on the job.  Is this possible?  Attached is my SQL Code generated by Excel.  I tried using LIMIT 1 on the function at the end, but it tells me the code has errors when I apply it. "Job.Job" is the field that contains the job number, which is how I would like to limit it.  

Thank You for your help!

example.pdf
SELECT Job.Job, Job.Order_Date, Job.Customer, Job.Customer_PO, Job.Part_Number, Job.Customer_PO_LN, Job.Order_Quantity, Job.Status, Job.Sched_Start, Job.Sched_End, Job_Operation.Work_Center, Job_Operation.Status
FROM PRODUCTION.dbo.Job Job, PRODUCTION.dbo.Job_Operation Job_Operation
WHERE Job.Job = Job_Operation.Job AND ((Job.Status='Active') AND (Job_Operation.Status<>'C'))
ORDER BY Job.Customer

Open in new window

0
Comment
Question by:Kyle Witter
  • 2
3 Comments
 
LVL 7

Expert Comment

by:andymacf
ID: 35095398
Would the first work centre be the most recent one, if so, you could set your query to look for 'max' value on the relevant field and this will always pull in the first event.
0
 
LVL 1

Author Comment

by:Kyle Witter
ID: 35095440
The first work center is the location that the job is currently at in production.  This is the line that I would like to keep.  I guess I don't understand exactly how to set a max value to limit the output, though.

Do you have an example?  Thank you!
0
 
LVL 7

Accepted Solution

by:
andymacf earned 500 total points
ID: 35096818
Difficult to give you an example right away as I am away from my pc, but try this. In your SQL, try wrapping the value 'max(your value)… Select max(value) from
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

733 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