Selasa, 07 Agustus 2012

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
> From: tsherven@mcd.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@yahoogroups.com] On Behalf Of Duane
> Hookom
> Sent: Monday, August 06, 2012 7:30 PM
> To: MS_Access_Professionals@yahoogroups.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>
> > From: tsherven@mcd.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@yahoogroups.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>
> > 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>
> > > From: tsherven@mcd.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]

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar