Sequentially Numbered Records in a Query, Revisited

[Fredo]

I have a report with one level of grouping and three levels of sorting. The folks at the office came up with the bright idea of sorting the second group with different fields than the first group.

Originally I thought I would need to create two reports, but then I had the idea to separate the referenced query into two, sort those with the different fields and then rejoin with a UNION query...

Unfortunately, (and many of you probably saw this coming) the Union Query resorted the list based on the first field in the SQL statement.

My Situation:
QryA has 10 of the fields from QryOriginalSource but filtered for [PatientStatus] = 1 and Sorted by [SectionLeader], [Site], [Insurance]
QryB has the same 10 fields from QryOriginalSource but filtered for [PatientStatus] = 2 and Sorted by [ActionReqd], [Site], [PtInitials]

I thought I could pull it off if I had a sequentially numbered field in QryA and QryB. Then I could join the two queries with the numbered field providing a forced sort. I searched the Net and found a great idea from Marshall Barton on another thread: https://www.pcreview.co.uk/forums/thread-1182376.php

The only trouble was that my sort was based on more than one field. So I came up with another idea so that I could use his code:
I created two calculated fields in QryOriginalSource:
SortFieldA: [SectionLeader] & " " & [Site] & " " & [Insurance]
SortFieldB: [ActionReqd] & " " & [Site] & " " & [PtInitials]
That would create a sorting field for each query so that I could use Marshall Barton's code from the other thread.

Then, in QryA I created a subquery field:
RecNum: (SELECT Count(*) FROM [qryOriginalSource] As X
WHERE X.SortFieldA <= [qryOriginalSource].SortFieldA)

and in QryB:
RecNum: (SELECT Count(*) FROM [qryOriginalSource] As X
WHERE X.SortFieldB <= [qryOriginalSource].SortFieldB) + 5000

(I added the 5000 for good measure, to make sure that the records from QryB would follow QryA)

So far, it worked like a charm. QryA consisted of all records with [PatientStatus] = 1 and sorted appropriately with a RecNum field on the end that was in numerical order, and QryB had all records with [PatientStatus] = 2 and sorted in its own fashion with a RecNum field that would follow QryA approriately once JOINED.

I was still patting myself on the back for figuring it out when I added the RecNum field to the UNION qry to join everything up. ACCESS CRASHED!!! Right in the middle of my back pattting! The UNION query works fine (other than the re-sorting problem) if I don't include the RecNum field in the SQL.

I feel like I am SOOO close, but now I am at a dead end.

Does anyone have an idea why it doesn't work? Or a better idea on how to accomplish my original goal?

I thought of another way of accomplishing my goal- use a make table query to store all the sorted records from QryA, add an autonumber field to the new table and then use an APPEND query to add the sorted records from QryB. However, this report is run on a daily basis, and I wasn't sure I could automate adding an Autonumber field into a table. (I haven't search the net yet, but I am sure it will involve a lot of Visual Basic...)

Any ideas?

Thanks in Advance!

 

[Fredo]

I left it alone for a couple days and then thought of another tack:

Instead of breaking up the Original Data into two queries (QryA and QryB), I could create two different RecNum fields instead in a QryAB:

RecNumA: (SELECT Count(*) FROM [qryOriginalSource] As X
WHERE X.SortFieldA <= [qryOriginalSource].SortFieldA)

RecNumB: (SELECT Count(*) FROM [qryOriginalSource] As X
WHERE X.SortFieldB <= [qryOriginalSource].SortFieldB) + 5000

Then, I would Create a new RecNum field that chooses between the two based on [Patient Status]

RecNum: IIf([Patient Status] =1,[RecNumA],[RecNumB])

That idea seemed to work pretty good. The records in QryAB came up with the proper RecNum value, though the records needed to be sorted.

I tried sorting right in the query, in the report or in a new simple select query of QryAB but either way, Access crashes.

I narrowed down the crashing to an attempted resort of a calculated field:
SELECT Count(*) FROM [DataSource] As X WHERE X.SortField <= [DataSource].SortField

Does anyone know why you can't sort on a field with this 'sequential number'/'running sum' type of formula?

 

[Fredo]

Anyhoo - I don't think anyone is reading this anyway, but maybe someone off in the future may want to sort two sections of a report differently:

I never did figure out why Access was crashing, but I figured I'd try to do without the 'sequential number'/'running sum' type formula.

I came up with the following idea: (it is so simple - I was so fixated on getting a field with a sequential order that I didn't need that I didn't see it coming...)

I had already made a SortField for each of the [PatientStatus] groups in order to generate the Sequential Number lists. All I needed to do was create a new query that used SortFieldsA for records with [PatientStatus] = 1 and SortFieldB for the others. To make sure the B group was at the bottom, I included a number of Z's before the [SortFieldsB] values.
SortFields: IIf([Patient Status] Like "1*",[SortFieldsA],"zzz " & [SortFieldsB])

Then I just sorted by SortFields in the Report. Worked like a charm this time.

Al