Troy,
I expect this is because you have duplicate records in:
ORDER BY Scheduled_Routings.Work_Order_ID ASC, Stage_Num asc
That's why I added the primary key from the Orders table to the ORDER BY.
Duane
> To: MS_Access_Professionals@yahoogroups.com
> From: tsherven@mcd.net
> Date: Mon, 6 Aug 2012 16:45:02 -0500
> Subject: RE: [MS_AccessPros] Value from a previous record
>
> Duane,
>
>
>
> Thanks for the response. I tried something similar and get an error
> saying: "at most one record can be returned by this subquery". Here's
> my SQL statement:
>
>
>
> SELECT Scheduled_Routings.Work_Order_ID, Stage_Num,
>
> (SELECT TOP 1 Oper_Description
>
> FROM Scheduled_Routings S
>
> WHERE S.Work_Order_ID = Scheduled_Routings.Work_Order_ID and S.Stage_Num
> < Scheduled_Routings.stage_num
>
> ORDER BY S.Work_Order_ID ASC, S.stage_num ASC) AS previous_process
>
> FROM Scheduled_Routings
>
> ORDER BY Scheduled_Routings.Work_Order_ID ASC, Stage_Num asc
>
>
>
> Any ideas?
>
>
>
> Thanks,
>
>
>
> Troy
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Duane
> Hookom
> Sent: Monday, August 06, 2012 3:34 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Value from a previous record
>
>
>
>
>
> I typically do this with a subquer. Since you haven't provided table and
> field names, consider this query from the sample Northwind that returns
> the customer, order date, and previous order date.
>
> SELECT Orders.CustomerID, Orders.OrderDate,
> (SELECT TOP 1 OrderDate
> FROM Orders O
> WHERE O.CustomerID = Orders.CustomerID and O.OrderDate <
> Orders.OrderDate
> ORDER BY OrderDate Desc, OrderID DESC) AS PrevOrderDate
> FROM Orders
> ORDER BY Orders.CustomerID, Orders.OrderDate;
>
> Duane Hookom
> MS Access MVP
>
>
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > From: tsherven@mcd.net <mailto:tsherven%40mcd.net>
> > Date: Mon, 6 Aug 2012 14:56:12 -0500
> > Subject: [MS_AccessPros] Value from a previous record
> >
> > I'm trying to write a query that will list each process in a job, and
> > also list the process name from the previous record, if it's within
> the
> > same job. Example: the first process in the job will not have a
> > previous process listed. When the job number changes to a new job, I
> > don't want to list a previous process for the first process of the new
> > job. All processes have a numeric process number, and the query is
> > sorted in numeric order ascending.
> >
> >
> >
> > Here's an example of what I'm looking for:
> >
> >
> >
> > Job# Process Previous process
> >
> > 1 Prepress -
> >
> > 1 Cutter Prepress
> >
> > 1 Fold Cutter
> >
> > 2 Cutter -
> >
> > 2 Coating Cutter
> >
> > 3 Cutter -
> >
> >
> >
> > From my research, I've found some info on DLOOKUP, but I'm not sure
> how
> > to "reset" the previous process on the change of a job. Anyone have
> > ideas on how to do this?
> >
> >
> >
> > Thanks,
> >
> >
> >
> > Troy Sherven
> > MCD, Inc.
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
>
>
> [Non-text portions of this message have been removed]
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar