Another Record Numbering Question

[mrfritz44]

My current task is taking me to the bounds of Access SQL that I wish I never had to travel.

My current query results are arranged below:

C121212121 data data
D121212121 data data data
L121212121 data data data data data data
L121212121 data1 data1 data1 data1 data1 data1
L121212121 data2 data2 data2 data2 data2 data2
C333333333 data data
D333333333 data data data
L333333333 data data data data data data
L333333333 data1 data1 data1 data1 data1 data1
C555555555 data data
D555555555 data data data
L555555555 data data data data data data
L555555555 data1 data1 data1 data1 data1 data1
L555555555 data2 data2 data2 data2 data2 data2
L555555555 data3 data3 data3 data3 data3 data3


Each pair of "C" and "D" records can have a number of "L" records.

Within a new query, I need to number the "L" records based on their respective position. For example:

C121212121 data data
D121212121 data data data
L121212121 data data data data data data 001
L121212121 data1 data1 data1 data1 data1 data1 002
L121212121 data2 data2 data2 data2 data2 data2 003
C333333333 data data
D333333333 data data data
L333333333 data data data data data data 001
L333333333 data1 data1 data1 data1 data1 data1 002
C555555555 data data
D555555555 data data data
L555555555 data data data data data data 001
L555555555 data1 data1 data1 data1 data1 data1 002
L555555555 data2 data2 data2 data2 data2 data2 003
L555555555 data3 data3 data3 data3 data3 data3 004

Hopefully the data example I wrote is self explanatory. I only wish the execution were as intuitive!

Please help!

Thanks,

Fred

 

[PHV]

I need to number the "L" records based on their respective position
What is the sequence ordering scheme ?
How do you know that the 3rd "L" record is below the 2nd and above the 4th ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

I assume that all these records are from the same table.

Question is

Are you imposing some ordering on the table to display them in the sequence that you're seeing ... and by implication, that you want them numbered?

The essence of the problem is that relational databases don't have any inherent ordering of the records so you need some mechanism to compare records to determine which is first, second, third, etc.

Do you have such a comparison that you can relate?

 

[mrfritz44]

They have been ordered per the first query and will always appear as such. The second query should add the numbering to the "L" records.

 

[PHV]

Why not posting the SQL code of the first query (at least the ORDER BY clause) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mrfritz44]

I'm at home now away from my work comp and away from that query. Is it possible to develop an answer just by assuming that the data above is in a standard table? Assume NULL values for the "C" and "D" rows' number values.

 

[PHV]

In a standard table there is NO respective position unless you sort the data with an ORDER BY clause.
To better understand what I mean simply do a keyword search in this forum for rank and another for ranking

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Is it possible to develop an answer just by assuming that the data above is in a standard table?

No ... it isn't ... at least not using SQL alone.

About the only way I could do this, without knowing the rule that placed the records in that order, would be to read the table (or query) into a recordset and then loop through the recordset appending a loop-generated counter to each record.

 

[mrfritz44]

OK, back at work. Here are the two parts to this. First listed is the UNION query, then the SORT query:

SELECT "C"+CLMNO+" "+IIf(Len(AGE)=2," "+AGE,AGE)+IIf(G=NULL,"0",IIf(G="M","1","2"))+FDT+TDT+" "+BILLTYPE+PNO+" "+" "+" "+" "+" " AS RECORDS , CLMNO,"C" AS B FROM tblOutput;
UNION ALL
SELECT "D"+CLMNO+DXCD AS RECORDS, CLMNO,"D" AS B FROM tblOutput;
UNION ALL
SELECT "L"+CLMNO+"INO"+P1+PM1+SD1+R1+S1+C1+" "+" " AS RECORDS,CLMNO,"L" AS B FROM tblOutput;
UNION ALL
SELECT "L"+CLMNO+"INO"+P2+PM2+SD2+R2+S2+C2+" "+" " AS RECORDS,CLMNO,"L" AS B FROM tblOutput;
UNION ALL
SELECT "L"+CLMNO+"INO"+P3+PM3+SD3+R3+S3+C3+" "+" " AS RECORDS,CLMNO,"L" AS B FROM tblOutput;
UNION ALL
SELECT "L"+CLMNO+"INO"+P4+PM4+SD4+R4+S4+C4+" "+" " AS RECORDS,CLMNO,"L" AS B FROM tblOutput;

The concatenations represent the "data", "data1", "data2", & "data3" in my original posts.
The concatenation of spaces is for the purpose of placeholding (looks wierd I know).

This query now SORTS the UNIONS:

SELECT RECORDS
FROM Assembly
WHERE RECORDS IS NOT NULL
ORDER BY CLMNO, B;


Records = L555555555 data3 data3 data3 data3 data3 data3 (this is a string)

CLMNO = 5555555555 (this field is broken out in assembly as its own field AND imbedded in the 'Records' string)

B = "C", "D", or "L" (this field is ALSO broken out in assembly as its own field AND imbedded in the 'Records' string)

The concatenation of spaces is for the purpose of placeholding (looks wierd I know)

 

[PHV]

ORDER BY CLMNO, B
So the "L" records are sequenced RANDOMLY ...


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mrfritz44]

Yes, the L records are sorted randomly and only need to be ordered once. These records are being formatted for a batch process only once.

Thanks.