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

[mshmyob]

yes it can be locked but what happens when another user is trying to create the query to the temp table. I guess he will just have to wait until the query is done and the data deleted before he can get access to run his query. I guess putting in some checking if a user has access before trying to append would be ideal in a multi user environment - if single user then no problems.

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

[ADezii]

yes it can be locked but what happens when another user is trying to create the query to the temp table. I guess he will just have to wait until the query is done and the data deleted before he can get access to run his query. I guess putting in some checking if a user has access before trying to append would be ideal in a multi user environment - if single user then no problems.

cheers,

I tend to agree with you in that the use of a Temporary Table within a Multi-User environment, in this specific context, is probably not a good idea. How about changing the logic to a 3-Phase process, namely:

1. Delete all Records in Temp Table.
2. Populate Temp Table from Query with sequential numbering.
3. Again, Delete all Records in the Temp Table when the process is complete.

Now, prior to code execution, a User can check the Record Count of the Temp Table (DCount("*", "tblTemp")) . If the Record Count = 0 proceed as normal, if it is > 0, or DCount("*", "tblTemp") generates an Error, then some other User is currently in Phase 2 Appending Records to the Temp Table, or Deleting them. Just some crazy idea, don't actually know how well, if if at all, it will work.

[chadh]

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 :)

Well, I got of working exactly as I wanted. I used a make table query to get my temporary table and added and autonumber column to it. Then, using a hb command button, I clear the temp table, use an apped query to populate it and another query that uses the count command to give the sequential row numbers and a final command to export out to a csv file. So far it looks to be working great. Still need to do a bit more testing, but I'm confdent it is solved.

Thanks again for the advice.
Chad

[NeoPa]

Good thinking and well done.

Here's a tip if you want it :
If you copy a table from a previously defined one, then the AutoNumber setting for the new table is reset to 0.

This could save you the trouble of the Update query that uses the count() function. Technically, the way AutoNumber works is not defined or guaranteed, but it's pretty safe to assume it will continue to work that way.

[youmike]

Just one comment - in my experience, this sort of need is best handled by a custom VBA module with all the necessary SQL built in. It can be a statement created by concatenating strings with parameters such as date ranges and so on to make it flexible to users' specific needs. Relying on queries external to the module simply complicates the debugging process. Assuming the application is running front/back end and the need really is temporary, a local temporary table is no real problem and it could be deleted after the export command and prior to closing the VBA script. I tend to use a counter local to the VBA script and increment it as I loop through the records in the select statement.

To sum up, the script would create a temporary table, populate it from an SQL select statement, adding the value of an incremented local counter, export the resulting records and delete the temporary table.