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]
Tidak ada komentar:
Posting Komentar