I have a request to be able to sequentially number records in a query. I
have looked in the forum and found similar questions, but I guess I am not
making the connection. I have a table named, Cycle_Time, that I am running a
query off of and the fields include Facility, Ord Num, Ord Line Num, Cycle
Time. I am after a result to group it by facility. I am have an example
below.
Facility Ord Num Ord Line Num Cycle Time Result I want
HK 1234 001 10 1
HK 4567 003 9 2
HK 7899 002 8 3
JF 9874 003 12 1
JF 5632 001 11 2
JF 6543 002 10 3
I would appreciate any help on coding this. Thanks for all your help.
|
|
0
|
|
|
|
Reply
|
 Utf
|
1/28/2008 2:34:00 PM |
|
Not exactly what you want, the result will be:
Facility Ord Num Ord Line Num Cycle Time Result I want
HK 1234 001 10 1
HK 4567 003 9 2
HK 7899 002 8 3
JF 5632 001 11 1
JF 6543 002 10 2
JF 9874 003 12 3
with:
SELECT a.facility, a.ordNum, LAST(a.ordLine), LAST(a.cycle), COUNT(*) as
ResultIwant
FROM myTable AS a INNER JOIN myTable AS b
ON a.facility = b.facility
AND a.ordNum >= b.ordNum
GROUP BY a.facility, a.ordNum
where you will have to replace the name myTable with the real name of your
table (at two places).
Hoping it may help,
Vanderghast, Access MVP
"Keith S" <KeithS@discussions.microsoft.com> wrote in message
news:6BEF7B42-D9A3-4E44-BA90-D71D3B6452C1@microsoft.com...
>I have a request to be able to sequentially number records in a query. I
> have looked in the forum and found similar questions, but I guess I am not
> making the connection. I have a table named, Cycle_Time, that I am
> running a
> query off of and the fields include Facility, Ord Num, Ord Line Num, Cycle
> Time. I am after a result to group it by facility. I am have an example
> below.
>
> Facility Ord Num Ord Line Num Cycle Time Result I want
> HK 1234 001 10 1
> HK 4567 003 9 2
> HK 7899 002 8 3
> JF 9874 003 12 1
> JF 5632 001 11 2
> JF 6543 002 10 3
>
> I would appreciate any help on coding this. Thanks for all your help.
|
|
|
0
|
|
|
|
Reply
|
Michel
|
1/28/2008 2:47:10 PM
|
|
You need a ranking subquery in your main query
SELECT Facility
, Ord Num
, Ord Line Num
, Cycle Time
, 1 + (SELECT Count(*)
FROM YourTable as Tmp
WHERE Tmp.Facility = YourTable.Facility
AND Tmp.[Cycle Time] > YourTable.[Cycle Time]) as Rank
FROM YourTable
In the query grid (Design view) in a new field "cell" enter the following
(all on one line)
Rank: 1 + (SELECT Count(*) FROM YourTable as Tmp WHERE Tmp.Facility =
YourTable.Facility AND Tmp.[Cycle Time] > YourTable.[Cycle Time])
Note that with a large set of records this typeof query can be slow.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
"Keith S" <KeithS@discussions.microsoft.com> wrote in message
news:6BEF7B42-D9A3-4E44-BA90-D71D3B6452C1@microsoft.com...
>I have a request to be able to sequentially number records in a query. I
> have looked in the forum and found similar questions, but I guess I am not
> making the connection. I have a table named, Cycle_Time, that I am
> running a
> query off of and the fields include Facility, Ord Num, Ord Line Num, Cycle
> Time. I am after a result to group it by facility. I am have an example
> below.
>
> Facility Ord Num Ord Line Num Cycle Time Result I want
> HK 1234 001 10 1
> HK 4567 003 9 2
> HK 7899 002 8 3
> JF 9874 003 12 1
> JF 5632 001 11 2
> JF 6543 002 10 3
>
> I would appreciate any help on coding this. Thanks for all your help.
|
|
|
0
|
|
|
|
Reply
|
John
|
1/28/2008 2:54:55 PM
|
|
Thank you Michel, this is exactly what I need to finish a project. I
appreciate your time.
"Michel Walsh" wrote:
> Not exactly what you want, the result will be:
>
> Facility Ord Num Ord Line Num Cycle Time Result I want
> HK 1234 001 10 1
> HK 4567 003 9 2
> HK 7899 002 8 3
> JF 5632 001 11 1
> JF 6543 002 10 2
> JF 9874 003 12 3
>
>
>
> with:
>
>
>
> SELECT a.facility, a.ordNum, LAST(a.ordLine), LAST(a.cycle), COUNT(*) as
> ResultIwant
> FROM myTable AS a INNER JOIN myTable AS b
> ON a.facility = b.facility
> AND a.ordNum >= b.ordNum
> GROUP BY a.facility, a.ordNum
>
>
>
> where you will have to replace the name myTable with the real name of your
> table (at two places).
>
>
>
>
>
> Hoping it may help,
> Vanderghast, Access MVP
>
>
> "Keith S" <KeithS@discussions.microsoft.com> wrote in message
> news:6BEF7B42-D9A3-4E44-BA90-D71D3B6452C1@microsoft.com...
> >I have a request to be able to sequentially number records in a query. I
> > have looked in the forum and found similar questions, but I guess I am not
> > making the connection. I have a table named, Cycle_Time, that I am
> > running a
> > query off of and the fields include Facility, Ord Num, Ord Line Num, Cycle
> > Time. I am after a result to group it by facility. I am have an example
> > below.
> >
> > Facility Ord Num Ord Line Num Cycle Time Result I want
> > HK 1234 001 10 1
> > HK 4567 003 9 2
> > HK 7899 002 8 3
> > JF 9874 003 12 1
> > JF 5632 001 11 2
> > JF 6543 002 10 3
> >
> > I would appreciate any help on coding this. Thanks for all your help.
>
>
>
|
|
|
0
|
|
|
|
Reply
|
Utf
|
1/28/2008 3:07:01 PM
|
|
One last question about this, in my table it is sorted by Facility and Cycle
Time. I want it to number how it is sorted in the table. Is there syntax to
have it sort that way? I have tried moving the fields around in the select
statement and in the Group By.
Thanks again.
"Keith S" wrote:
> I have a request to be able to sequentially number records in a query. I
> have looked in the forum and found similar questions, but I guess I am not
> making the connection. I have a table named, Cycle_Time, that I am running a
> query off of and the fields include Facility, Ord Num, Ord Line Num, Cycle
> Time. I am after a result to group it by facility. I am have an example
> below.
>
> Facility Ord Num Ord Line Num Cycle Time Result I want
> HK 1234 001 10 1
> HK 4567 003 9 2
> HK 7899 002 8 3
> JF 9874 003 12 1
> JF 5632 001 11 2
> JF 6543 002 10 3
>
> I would appreciate any help on coding this. Thanks for all your help.
|
|
|
0
|
|
|
|
Reply
|
Utf
|
1/28/2008 9:46:09 PM
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_0E55_01C8624E.853037C0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
Try the following:
SELECT a.facility, a.cycle, LAST(a.ordLine), LAST(a.OrdNum), COUNT(*) =
as=20
ResultIwant
FROM myTable AS a INNER JOIN myTable AS b
ON a.facility =3D b.facility
AND a.cycle>=3D b.cycle
GROUP BY a.facility, a.cycle
Hoping it may help,
Vanderghast, Access MVP
"Keith S" <KeithS@discussions.microsoft.com> wrote in message =
news:39C7253A-2355-4235-9B05-50B554D2C739@microsoft.com...
> One last question about this, in my table it is sorted by Facility and =
Cycle=20
> Time. I want it to number how it is sorted in the table. Is there =
syntax to=20
> have it sort that way? I have tried moving the fields around in the =
select=20
> statement and in the Group By.
------=_NextPart_000_0E55_01C8624E.853037C0
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">
<META content=3D"MSHTML 6.00.6000.16587" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV>Try the following:</DIV>
<DIV> </DIV>
<DIV>SELECT a.facility, <STRONG>a.cycle</STRONG>, LAST(a.ordLine),=20
LAST(a.OrdNum), COUNT(*) as <BR>ResultIwant<BR>FROM myTable AS a =
INNER=20
JOIN myTable AS b<BR> ON a.facility =3D=20
b.facility<BR> AND a.<STRONG>cycle</STRONG>>=3D=20
b.<STRONG>cycle</STRONG><BR>GROUP BY a.facility,=20
a.<STRONG>cycle</STRONG></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>Hoping it may help,</DIV>
<DIV>Vanderghast, Access MVP</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>"Keith S" <<A=20
href=3D"mailto:KeithS@discussions.microsoft.com">KeithS@discussions.micro=
soft.com</A>>=20
wrote in message <A=20
href=3D"news:39C7253A-2355-4235-9B05-50B554D2C739@microsoft.com">news:39C=
7253A-2355-4235-9B05-50B554D2C739@microsoft.com</A>...</DIV>>=20
One last question about this, in my table it is sorted by Facility and =
Cycle=20
<BR>> Time. I want it to number how it is sorted in the =
table. Is=20
there syntax to <BR>> have it sort that way? I have tried =
moving the=20
fields around in the select <BR>> statement and in the Group=20
By.<BR></BODY></HTML>
------=_NextPart_000_0E55_01C8624E.853037C0--
|
|
|
0
|
|
|
|
Reply
|
Michel
|
1/29/2008 1:11:15 PM
|
|
|
5 Replies
107 Views
(page loaded in 0.21 seconds)
Similiar Articles: sequentally numbering records in a report - microsoft.public ...I would like to number the records in a report so that the first record is number 1, and each subsequent record is incremented by 1. That is, 1, 2,... Sequential numbering of records - microsoft.public.access ...Having read the various postings on the subject i am more confused than ever. what I want to do is to create a field that will contain sequential num... Numbering the Records of a Query - microsoft.public.access.reports ...Hi folks, I'm using the code module below to create a extra column which will number, in serial order, the records produced by my query. The problem i... Sequentially number records in a query - microsoft.public.access ...Hi all - I know this is a common question, but I haven't found a complete answer. Is there a technique to generate a sequentially-numbered, calcula... Number Records by group in Report - microsoft.public.access ...I would like to number or rank records in a report by grouping. The ranking or numbering would be based on the lowest time. I have a field of [HandleT... Incremental Numbering - microsoft.public.access.formscoding ...Can this task be accomplished through an update query? I want to number the lines for an existing database. There are 13K+ records in table A. ... Excel - Numbering Rows while using filtering - microsoft.public ...When using auto-filtering, how can I "number" how many records are displayed. ... Continuous form / sequential numbering - microsoft.public.access ...Continuous form / sequential numbering - microsoft.public.access ... Sequential Numbering of Records in Access Report - Dev Articles Sequential ... How do I customize my numbering sequence - microsoft.public.access ...How do I customize my numbering sequence so that I start at 10000? ... ... Hi, If you are talking about an Autonum field, insert a record into your table that ... Select only even or odd number records - microsoft.public.access ...Hi, I have a table with among other things has on number field. I want two reports, one that select only records with even number in the number fi... Catalog numbering systems for single records - Wikipedia, the free ...This article presents the numbering systems used by various record companies for single (mainly 7"33 ⅓ and 45, and 10" 78 rpm) records. Capitol Records began with ... NUMBERING AND FILING SYSTEM Rajakumar.M, ...Rajakumar.M, Manager-Medical Records, Aravind Eye Care System. Introduction Medical records in most health care institutions are filed numerically according to ... How to Access Numbering Records From a Query | eHow.comMicrosoft Access provides a means to store important data. This is accomplished through the use of database tables, which are a collection of fields and records ... Ranking or numbering records - Allen Browne's Database and TrainingHow to rank records in Microsoft Access ... Ranking or numbering records. JET does not have features to rank/number rows as some other SQL implementations do, so ... Access/VBA Tutorials - Ranking or numbering records in forms/reportsTutorial: Ranking or numbering records in forms/reports ... Microsoft Access Tips for Serious Users. Provided by Allen Browne, February 2008. 7/20/2012 10:53:19 PM
|
Search |
Post Question |
Groups |
Stream |
About |
Register
16 followers. 