Creating Line or Row numbers on query results.

[chadh]

I have two select queries that need to have a column of line numbers on them. The numbers need to simply be 1 through however many rows there are in the result set. With the data I'm using the result set usually under 100 rows for each query. I've read solutions that involve using the primary key field and a COUNT function. Those have not worked for me as my data set comes from a linked CSV file with no primary key. I'm using a linked CSV file because the data is updated often. I want to get my CSV file, run my queries on it and get my exported files. To further complicate matters, the data does not contain a field contains unique values that could be a primary key.

The results of these two queries will be exported to CSV files and then imported into another data system.

Any help or ideas would be appreciated.

Chad

[NeoPa]

I'm afraid this is logically impossible, as there is no definition of the order, and SQL doesn't recognise the concept of ordinal numbers per line, or to put it another way, of record positions.

There are ways of fiddling this in code (VBA procedures) but fundamentally this is not supported.

[chadh]

OK, so I need to find a way of making a new field that contains a unique value for each line. If I did this with an expression, lets say concatenating two other fields into new one such that it is unique, could I then use that with the count function?

This seemingly-simple thing has turned into a major roadblock for my project.

I've read that record numbers or an auto-incrementing number could be added to a report. This would work, as long as I could export the report to a csv file.

Chad

[NeoPa]

Possible, but reports are not laid out in the format you'd need. Exporting a report does just that, the displayed data, not the underlying record source.

I'm afraid to say that people who understand databases never see this as a straightforward issue. It can only seem that way to those who don't appreciate what it is they're trying to do.

[chadh]

Ok, so lets try a different approach. Could I create a 'temporary' table and populate it with my query? Then, add an auto number (1 through the total number of records) to the table and run a new select query on that temporary table that just returns everything in it (ordered by the new auto number), export that query to the CSV file and then delete the temporary table.

I presume, if this is feasible, it could be scripted in VB and run with as and event.

I understand using an INSERT statement to put the query results into a table. I'm not so sure about setting up the table, getting the correct fields, adding the auto number and then clearing or deleting it after the export. I would want the table cleared or completely deleted so there would not be residual data the next time a user would run the command.

Chad

[NeoPa]

It most certainly is (I'm impressed).

There are various reasons why I wouldn't try suggesting this. Among which is the worry that you might still go away with "wrong" ideas about database concepts. Most of the others are about whether someone could get their head around it and various practicalities.

Clearly, your heads already there, so, regardless of the worry about the understanding, I think we can progress along these lines.

As you are clearly with the plot, I suggest leaving you to produce what you can on those lines. If you get stuck, or need help at any phase, come back with the details and we can certainly help.

I will say that you're on a good track here. It certainly can work, so if you get stuck you already know that's all it is. I will await your response, particularly your eventual, and inevitable, success :)

[ADezii]

Ok, so lets try a different approach. Could I create a 'temporary' table and populate it with my query? Then, add an auto number (1 through the total number of records) to the table and run a new select query on that temporary table that just returns everything in it (ordered by the new auto number), export that query to the CSV file and then delete the temporary table.

I presume, if this is feasible, it could be scripted in VB and run with as and event.

I understand using an INSERT statement to put the query results into a table. I'm not so sure about setting up the table, getting the correct fields, adding the auto number and then clearing or deleting it after the export. I would want the table cleared or completely deleted so there would not be residual data the next time a user would run the command.

Chad

chadh, I've used this logic several times when I absolutely, positively, needed sequential numbering, for one reason or another. My approach is to:

1. Temporarily, convert your Query to a Make Table Query, execute it then Delete all the Records in it. You now have your Temp Table, minus the Counter Field. This process is only a 1-Shot deal, and will not be performed again.
2. This Temp Table also contains a newly added [Rec_Num] {LONG} Field, not AutoNumber, which will be sequentially numbered for each Appended Record.
3. Create a Recordset based on the Query and pro-grammatically Append each Record to the Temp Table. Increment the [Rec_Num] Field by 1 for each Append Operation:
   

   Do While Not Main_RS.EOF         'based on Query
     Recordset_Append.AddNew       'based on Temp Table
        ... Append other Fields here (Main_RS!*) ==>  Recordset_Append![<Field>]
        intCounter = intCounter + 1
        Recordset_Append![Rec_Num] = intCounter
     Recordset_Append.Update
     Main_RS.MoveNext
   Loop

4. Assuming success, the results of your Query exist in the Temp Table and are sequentially numbered.
5. Export the Temp Table to any Format you so desire.

[NeoPa]

As you are clearly with the plot, I suggest leaving you to produce what you can on those lines. If you get stuck, or need help at any phase, come back with the details and we can certainly help.

Personally, I still think you would be better off trying to work something out for yourself first. I think you probably have the right idea and going through that process will help you more than simply picking up a solution (although obviously that's an option).

[mshmyob]

Hello Ade. Question I have because I did this the other day to solve the problem with the Subform calculated value problem that you gave me a better solution to.

I did this solution for the problem I had stated above but my added column was for the calculation and then populated my subform.

The reason I didn't go with this type of solution was I was thinking what might happen in a multi-user environment. I would assume problems would occur with appending to the table and getting wacky results in the query if multiple people were trying to do everything at the same time.

I created a routine that created a temp table name based on a date/time stamp. I thought there had to be another (better) way and you did give me one in the thread.

Am I over thinking the multi user environment and using a temp table with a name that never changes.

cheers,

chadh, I've used this logic several times when I absolutely, positively, needed sequential numbering, for one reason or another. My approach is to:

1. Temporarily, convert your Query to a Make Table Query, execute it then Delete all the Records in it. You now have your Temp Table, minus the Counter Field. This process is only a 1-Shot deal, and will not be performed again.
2. This Temp Table also contains a newly added [Rec_Num] {LONG} Field, not AutoNumber, which will be sequentially numbered for each Appended Record.
3. Create a Recordset based on the Query and pro-grammatically Append each Record to the Temp Table. Increment the [Rec_Num] Field by 1 for each Append Operation:
   

   Do While Not Main_RS.EOF         'based on Query
     Recordset_Append.AddNew       'based on Temp Table
        ... Append other Fields here (Main_RS!*) ==>  Recordset_Append![<Field>]
        intCounter = intCounter + 1
        Recordset_Append![Rec_Num] = intCounter
     Recordset_Append.Update
     Main_RS.MoveNext
   Loop

4. Assuming success, the results of your Query exist in the Temp Table and are sequentially numbered.
5. Export the Temp Table to any Format you so desire.

[ADezii]

Hello Ade. Question I have because I did this the other day to solve the problem with the Subform calculated value problem that you gave me a better solution to.

I did this solution for the problem I had stated above but my added column was for the calculation and then populated my subform.

The reason I didn't go with this type of solution was I was thinking what might happen in a multi-user environment. I would assume problems would occur with appending to the table and getting wacky results in the query if multiple people were trying to do everything at the same time.

I created a routine that created a temp table name based on a date/time stamp. I thought there had to be another (better) way and you did give me one in the thread.

Am I over thinking the multi user environment and using a temp table with a name that never changes.

cheers,

Good point, how about setting the following Option on the OpenRecordset() Method for the Temp Table, and see what happens?

'The dbDenyWrite Option prevents other users from modifying or adding records
'(Microsoft Jet Recordset objects only).
Set MyRS = MyDB.OpenRecordset("tblTemp", dbOpenDynaset, dbDenyWrite)
 
MyRS.AddNew
   ...
MyRS.Update