How to Create an autonumbering row column in my Query without having an ID column is my table.

[TROD]

How do I write a query that automatically numbers my rows without having my table contain an ID column.



I want my query to go from:

SCHED CREW
1524 PMM1
1532 PMM3
1325 PMM1
2RCT PMM2

To:
ROW SCHED CREW
1 1524 PMM1
2 1532 PMM3
3 1325 PMM1
4 2RCT PMM2

I tried this SELECT SCHED,CREW,(SELECT COUNT(*)FROM table AS X WHERE X.SCHED=table.SCHED AND X.CREW<= table.CREW) AS ROW FROM table;
but it returns this
ROW SCHED CREW
1 1524 PMM1
1 1532 PMM3
1 1325 PMM1
1 2RCT PMM2

[orange]

Try

select * from
   (SELECT a1.sched, a1.crew, COUNT(*) AS Row  FROM table8 AS a1 
  INNER JOIN 
 table8 AS a2 ON
              (a1.sched >= a2.sched)   
GROUP BY 
a1.sched
, a1.crew)
  ;

This is not called autonumbering. Autonumber has a whole different meaning in Access.

You could call this a Ranking query.

[TROD]

Orange

That worked just fine.
What would the Code look like if I only had one column? For example I wanted to Number/Rank just the SCHED column.
Also what if i had more than two coulmns? For example I had columns SCHED, CREW, SITE, DATE, TIME which I also want a Row Number/Rank.

Another question;
How can I apply this row Numbering/RANK to my already exsiting query's?
My Query is written using Design View not SQL. My Query is grabbing data from one data table and from another Query. I was wondering if I can somehow add Row Numbers that Query if possible via Design View instead of SQL. If SQL is the best and only way then I'll use that.

Thank You

[orange]

Use the query I showed and adjust -- do some trial and error.
Save the original so you don't lose it, but play with some parameters, you will learn by doing.

Good luck.

You can also search for Allen Browne Ranking query

[accedeholdings]

As for me, this query will be solved using SQL.

[TROD]

I will tinker around with it. The issue that I'm currently having is that some of my data is the same and I want a different Row/Number/Rank for each data regardless if it the same. For example:
Instead of this:
ROW SCHED CREW UNIT
1 1524 PMM1 0
1 1524 PMM1 2
1 1524 PMM1 2
1 1524 PMM1 3

It should look like this:
ROW SCHED CREW UNIT
1 1524 PMM1 0
2 1524 PMM1 2
3 1524 PMM1 2
4 1524 PMM1 3


Thanks Again

[ssanfu]

You might also check out Roger's Access Library (NumberedQuery.mdb (beginner)) : http://www.rogersaccesslibrary.com/forum/topic309.html

[orange]

With the addition of UNIT, Try

  SELECT a1.sched, a1.crew,a1.unit, COUNT(*) AS Row 
FROM CrewsAndSched AS a1
 INNER JOIN CrewsAndSched AS a2 
ON a1.sched >= a2.sched
 GROUP BY a1.sched, a1.crew,a1.unit;

[TROD]

You guys have been great. I have another dilemma. Ingore all the periods,just trying to align data.

This query works:

SELECT a1.[MONLOG], a1.[MONUNIT], a1.[MONY], a1.[MONE], a1.[MONP], COUNT(*) AS Row
FROM Monday1 AS a1 INNER JOIN Monday1 AS a2 ON a1.[MONLOG] >= a2.[MONLOG]
GROUP BY a1.[MONLOG], a1.[MONUNIT], a1.[MONY], a1.[MONE], a1.[MONP];

Which returns this:
MONLOG..MONUNIT..MONY..MONE..MONP..ROW
A ..............2.....................-1.................1
B ..............3..........-1............................2
C ..............3..........-1............................3
D ..............2...............................-1.......4
E ..............2................................... ......5
F ..............3................................... ......6


MONLOG is the only field that is variable among itself (MONLOG data is all different and not one row will match another). MONUNIT can only be 0,2 or 3. MONY, MONE, & MONP can only be blank or -1.
I need to order by MONUNIT. So I need my query to return the rows by MONUNIT All 0 first, All 2 next & then 3 last. But I need my ROW sequence numbering to coincide with MONUNIT. See example below.

This is what I need it to return:

MONLOG..MONUNIT..MONY..MONE..MONP..ROW
A.............. 2.....................-1................1
D ..............2..............................-1.......2
E ..............2................................... .....3
B ..............3.........-1............................4
C ..............3.........-1............................5
F ..............3................................... .....6


Ive tried re-arranging the query but got this:

MONLOG..MONUNIT..MONY..MONE..MONP..ROW
A ...............2..................-1.................1
D ...............2.............................-1......4
E ...............2.................................. ....5
B ...............3........-1...........................2
C ...............3........-1...........................3
F ...............3.................................. ....6

I also got something like this:

MONLOG..MONUNIT..MONY..MONE..MONP..ROW
A ...............2..................-1.................27
D ...............2............................-1.......27
E ...............2.................................. ....27
B ...............3........-1...........................27
C ...............3........-1...........................27
F ...............3.................................. ....27


I really appreciate any help.
Thanks

[orange]

I think you probably have a structure issue since the technique you are describing is not common.
Perhaps if readers had more info, more focused suggestions and advice would be forth cominig.

Tell us in plain English what the issue is, and may be more options will evolve.

Too often queries and code mask the issue --they show us How you have tried to do something. But readers need to know WHAT you were trying to solve.