How can I number/rank my rows sequentially but have it ascend and align with a specific column?

[TROD]

The query below 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 within its own column). MONUNIT can only be 0,2 or 3. MONY, MONE, & MONP can only be blank or -1.
I need to sort/ascending 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]

Seems there may be more to this than you have described. It isn't clear what you need based on the proper sequence of Row. MonLog and ROW are in sequential Order. We need more info on the requirement and Monday1.

[TROD]

Seems there may be more to this than you have described. It isn't clear what you need based on the proper sequence of Row. MonLog and ROW are in sequential Order. We need more info on the requirement and Monday1.

I need MonUnit and ROW to be in sequential Order.

I had created this MondayRow query:

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];

The only reason I created MondayRow query was because I couldnt figure out how to add a sequential Row numbering/rank to my Monday1 query.

This is my Monday1 query (which was written in design view):

SELECT [ST/RT/SI].[COMBINED LOG] AS MONLOG, [ST/RT/SI].UNIT AS MONUNIT, [ST/RT/SI].[PMPO+Y] AS MONY, [ST/RT/SI].[PMPO+E] AS MONE, [ST/RT/SI].[PMPO+P] AS MONP, SCHEDDATE.SCHED
FROM [ST/RT/SI] INNER JOIN SCHEDDATE ON [ST/RT/SI].NewStartDate = SCHEDDATE.MONDAY
GROUP BY [ST/RT/SI].[COMBINED LOG], [ST/RT/SI].UNIT, [ST/RT/SI].[PMPO+Y], [ST/RT/SI].[PMPO+E], [ST/RT/SI].[PMPO+P], SCHEDDATE.SCHED
HAVING (((SCHEDDATE.SCHED)=[Forms]![Dashboard]![MONDAYDATE]))
ORDER BY [ST/RT/SI].UNIT;




Do these details help?

[orange]

No, but your naming convention leaves a lot to be desired in my view.
Slashes and + signs should NOT be used in field or object names.
Can you step back and tell readers in simple,plain English what is the business issue/opportunity involved and
what exactly is the purpose of the ranking --simple English - not SQL?
We have to understand the issue before offering focused suggestions/advice.

[TROD]

No, but your naming convention leaves a lot to be desired in my view.
Slashes and + signs should NOT be used in field or object names.
Can you step back and tell readers in simple,plain English what is the business issue/opportunity involved and
what exactly is the purpose of the ranking --simple English - not SQL?
We have to understand the issue before offering focused suggestions/advice.

I am scheduler. I have an excel sheet data base with thousands of Work order Activities linked to Microsoft access. Table is call "TASK".
Each work order has many field data associtated with it. Each Work order is scheduled to start at different dates through out the year. Below is the "Task Table" example.

Fieldname:....ACT_ID...........Act_Name................Crew _Supv.......Risk1............Risk2................ Risk3...............Sched_Code.......Plant_Unit... ....Start.......................Finish............ ......Etc..more..fields
Data:............R1112126........RT-01-02-111-2........PMM1..............PMPO+E........PMPO+E... .........PMOP+Y..........1510..................03. ................3/5/2015_8:00..........4/8/2015_22:00......Etc..more..fields
Data:............R0237899........Destage.............. .....PMI5...............PMPO+Y........PMPO+P...... ......PMOP+E..........1515..................02.... .............3/5/2015_23:00..........5/2/2015_9:00......Etc..more..fields



Sched code 1510 is the work week of the year ie. Week 10 of year 2105.

I created a database table in access called "SCHEDDATE". SCHEDDATE contains the date of each day in each work week. Example below

Fieldname:...Sched....Monday........Tuesday........Wednesda y.....Etc..through..sunday
Data:...........1510......3/1/2015.......3/2/2015.......3/3/2015........Etc..through..sunday
Data:...........1511......3/8/2015.......3/9/2015.......3/10/2015......Etc..through..sunday



Ok the above is what I have to start with. Its just data.


I have a form that when I enter any Sched Code of a week "1510" it would return the whole work week as cloumns. ie. MONDAY....TUESDAY....WEDNESDAY.....THURSDAY....FRI DAY....SATURDAY....SUNDAY. as one sheet.
I have to link SCHEDDATE Table with TASK Table somehow in order to get the Sched form search to work.


Next:

I need to create a MONDAY Query and 6 other individual queries for the days of the week that return data as follow for each query.
I created a LOG expression that bundles specific data in one column.
Monday query
Fieldname:....MondayLOG.................................... .................................................. ..Monday_Plant_Unit........Monday_Risk1.........Mo nday_Risk2.........Monday_Risk3.........ROW
Data:............23:00..-..5/2..09:00..(R0237899)..Destage.............(PMI5).. ..........................02...................... .........PMPO+E................PMPO+E............. ....PMPO+Y...............1
Data:............08:00..-..4/8..22:00..(R1112126)..RT-01-02-111-2..(PMM1)...........................03............ ...................PMPO+E................PMPO+E... ..............PMPO+Y...............2


Tuesday query
Fieldname:....TuesdayLOG................................... .................................................. ...Tuesday_Plant_Unit........Tuesday_Risk1.......T uesday_Risk2.........Tuesda_Risk3.........ROW
Data:............??????..-..???..?????..(?????????)..???????.............(PM I5)................................00............. ..................PMPO+P................PMPO+Y.... .............PMPO+Y...............1
Data:............??????..-..???..?????..(?????????)..???????????????..(PMM1) ...............................03................. ..............PMPO+Y................PMPO+E........ .........PMPO+Y...............2



Now I created a new blank table called "Work Week". I manually Numbered the ROW Columns in table Work Week to go from 1 through 100.
Work Week Table:
Fieldname:....ROW..........MondayLog.........Monday_Plant_U nit........Monday_Risk1..........Monday_Risk2..... .ModayRisk3.......TuesdayLog......Tuesday_Risk1... ...Tuesday_Risk2......Tuesday_Risk3......ETC..for. .other..days..in..the..week.
Data:.............1..............Blank................ .Blank...........................Blank............ .........Blank..................Blank............. ...Blank...............Blank...................Bla nk..................Blank..................ETC..fo r..other..days..in..the..week.
Data:.............2..............Blank................ .Blank...........................Blank............ .........Blank..................Blank............. ...Blank...............Blank...................Bla nk..................Blank..................ETC..fo r..other..days..in..the..week.
Data:.............3..............Blank................ .Blank...........................Blank............ .........Blank..................Blank............. ...Blank...............Blank...................Bla nk..................Blank..................ETC..fo r..other..days..in..the..week.



Finally this is why I need Row numbers in my week day query.
I take all my Week day querys Monday, Tuesday, Wednesday,etc and I make an UPDATE! to my Work Week Table. The queries will be link to work week table by ROW numbers.
Its important that my week day queries stay sorted by UNIT so when they come into Work Week table the columns for that day are already asceneded by UNIT.



Below is how the week report from access should look like.
I only use the MondayLog, TuesdayLog etc to display the data needed in my report.
I use conditional format to color code anything that contains risk (Monday_Risk1,2,3 etc.) for different reasons. Blue, Red etc.
I also use condintioanl format to color code text asscociated with its UNIT.
Also As you can see the day of the week is sorted by Unit then time start.
For example Black text are all Unit 0 activities which start at the top.
Then next comes Brown text which are all Unit 2 activities.
Last comes Green text which are Unit 3 activities.






Attachment 19869