Senin, 06 Agustus 2012

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
> From: tsherven@mcd.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]

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar