ACCESS FEED: RE: [MS_AccessPros] Value from a previous record

You missed the DESC in the ORDER BY in the subquery:

SELECT MCD_All_Jobs.Job_Number, MCD_All_Jobs.Stage_Num,
(SELECT TOP 1 MC_USED
FROM MCD_All_Jobs as S
WHERE S.Job_Number = MCD_All_Jobs.Job_Number and S.Stage_Num <
MCD_All_Jobs.Stage_Num
ORDER BY S.Stage_Num DESC
) AS previous_process
FROM MCD_All_Jobs
ORDER BY MCD_All_Jobs.job_number, MCD_All_Jobs.stage_num;

Make sure the table is indexed on Job_Number and Stage_Num.

Duane Hookom
MS Access MVP

> To: MS_Access_Professionals@yahoogroups.com
> From: tsherven@mcd.net
> Date: Tue, 7 Aug 2012 11:49:27 -0500
> Subject: RE: [MS_AccessPros] Value from a previous record
>
> Duane,
>
>
>
> Thanks for the help...I think I'm getting close. I realized that I had
> duplicate Stage_num in the same job. I'm now working with a different
> query. The query will run, but is giving me slightly different results
> than I want. Here's what it's giving me:
>
>
>
> Job_number Stage_num MC_Used Previous_Process
>
> 1 30 Prepress
> -
>
> 1 40 Cutter
> Prepress
>
> 1 50 Coater
> Prepress
>
> 1 60 Finisher
> Prepress
>
>
>
> My query is returning the MC_USED of the first record in each subsequent
> record. Also, my query is painfully slow. MCD_All_Jobs contains only
> about 250 records. There is no primary key in the table, but Stage_num
> is a unique value in each job. Here's my query:
>
>
>
>
>
> SELECT MCD_All_Jobs.Job_Number, MCD_All_Jobs.Stage_Num, (SELECT TOP 1
> MC_USED
>
> FROM MCD_All_Jobs as S
>
> WHERE S.Job_Number = MCD_All_Jobs.Job_Number and S.Stage_Num <
> MCD_All_Jobs.Stage_Num
>
> order by S.Job_Number, S.Stage_Num
>
> ) AS previous_process
>
> FROM MCD_All_Jobs
>
> ORDER BY MCD_All_Jobs.job_number, MCD_All_Jobs.stage_num;
>
>
>
> Any idea what's wrong with my query?
>
>
>
> Thanks,
>
>
>
> Troy
>
>
>
>
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Duane
> Hookom
> Sent: Tuesday, August 07, 2012 10:32 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Value from a previous record
>
>
>
>
>
> There only needs to be some unique value from one or more fields.
> Consider the ORDER BY clause from the subquery. If you create a simple
> totals query that groups by these fields, will it return multiple
> records?
>
> SELECT Work_Order_ID, Stage_Num, Count(*) as NumOf
> FROM Scheduled_Routings
> GROUP BY Work_Order_ID, Stage_Num
> HAVING Count(*) > 1;
>
> You need have the above query return no records.
>
> 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: Tue, 7 Aug 2012 09:14:00 -0500
> > Subject: RE: [MS_AccessPros] Value from a previous record
> >
> > Duane,
> >
> >
> >
> > There is no primary key in my table. Do you think this will still
> work,
> > or do I need a primary key? This is a linked table to a SQL DB.
> >
> >
> >
> > Thanks,
> >
> >
> >
> > Troy
> >
> >
> >
> > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Duane
> > Hookom
> > Sent: Monday, August 06, 2012 7:30 PM
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: RE: [MS_AccessPros] Value from a previous record
> >
> >
> >
> >
> >
> > 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
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > From: tsherven@mcd.net <mailto:tsherven%40mcd.net>
> <mailto:tsherven%40mcd.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%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
> Duane
> > > Hookom
> > > Sent: Monday, August 06, 2012 3:34 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.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>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > From: tsherven@mcd.net <mailto:tsherven%40mcd.net>
> <mailto:tsherven%40mcd.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]
> >
> >
> >
> >
> >
> > [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]