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