SQL/VBA add row number column

[Jrod2541]

I have this table called: "ABC" and inside it are four fields: "SKU" (DataType: Short text), "VID" (DataType: Short text), "Cost" (DataType: Currency) and "Qty" (DataType: Number).

SKU    VID   Cost  Qty
123    545   $5    9
123    545   $5    25
145    441   $4    45
488    874   $7    4
474    457   $8    5
145    441   $4    47
 

I want to add another field/column called "Line #" that shows the row number so the table will look like this:

Line #   SKU    VID   Cost  Qty
1        123    545   $5    9
2        123    545   $5    25
3        145    441   $4    45
4        488    874   $7    4
5        474    457   $8    5
6        145    441   $4    47
 

I couldn't find any information on how to do this right because some rows have the same values.

Is there any way I can do this in either SQL or VBA?

[twinnyfo]

Is Line# (and PLEASE do not use "#" in the field name!) a unique identifier for each record? If it is, just add a Field to the Table, with AutoNumber Data type, and it will add numbers to all your records.

If this field is not a unique identifier, what is it used for and which rows would have a duplicate Line#?

[Jrod2541]

It is a unique identifier but I don't like using AutoNumber because if I delete a row, than the number skips. Take this table for example:

LineNumber   B    C
1            cd   sd
2            cd   sd
3            ef   df
4            cd   gb
 

Now if I want to delete line number 4, then add more rows here's what happens

LineNumber   B    C
1            cd   sd
2            cd   sd
3            ef   df
DELETED
 

Now that I deleted that row, let's say I want to add a row with values for B is "if" and for C it's "wq"

LineNumber   B    C
1            cd   sd
2            cd   sd
3            ef   df
5            if   wq
 

It skips line 4 and goes to line 5.

If it's not possible to make a query or a module that creates [LineNumber] for me? Or should I just suck it up and enter each line in manually?

[Seth Schrock]

Are you wanting to add the field to the table or just in a query to get a row number in the query results?

**Edit** Sorry, I didn't refresh before posting, so I didn't see post #3.

[twinnyfo]

Jrod,

There is nothing wrong with having missing row numbers. It happens all the time in databases. What happens if you were to change one of your items? Then you would delete it. Would you then renumber all of your other records, just because one of the items was deleted from the inventory?

I know, I am OCD myself, so having missing record numbers causes my head to screw into the ceiling, but I have learned to get over it. Don't over-complicate something that the DB will easily and happily uncomplicate for you.....

[Jrod2541]

I'm not trying to sound rude or anything but I do need the line numbers to be in correct order. It has nothing to do with preference.

This table is connected to a report which I'd need to print out once this is complete. It needs to show the line number on the left side for each row.

Is there a way I can show the line numbers on a report without using a query?

[Jrod2541]

Ok I just looked it up and I fixed it by creating an unbound textbox and in the property sheet under the "Data" tab I have:

Control Source =1

Running Sum Over All


Thanks for the advice and help

[twinnyfo]

Jrod, no rudeness was taken. I hope you did not consider my comments rude, either. Sometimes they have to be in numerical order, and I can understand that necessaity from time to time.

I have done what you are asking before--since you are using it in a report, it would best be done in a query and not in a table, since there is no value in storing that value--if your inventory changes.

I have done this in the past, but my query is always sorting based on certain values, which is not what you have. I will have to do some lookinginto this one just a bit...

[twinnyfo]

Ok. Glad you found an alternate solution.