Add increental info to existing data

[MITTENCAT]

Hi,

i have a very large set of data (around 5million rows) that i need to make a change to, i can do it Excel with a small set of data but hoping someone can help me with automating this is Access as it would be a lot simpler due to the amount of data. I am not that much of an Access user.

Basically I need to add - 0 the end of each cell of data then - 1, - 2 etc. to the subsequent number until it gets to a new line then start all over again

EG

ABC123 - 0
ABC123 - 1
ABC123 - 2
DEF123 - 0
DEF123 - 1
DEF123 - 2

 

[Andrzejek]

Wouldn't you rather have another Field (column) with the new data instead of modifying your existing data (which make it almost useless):

[pre]
FieldOne FieldTwo
ABC123 0
ABC123 1
ABC123 2
DEF123 0
DEF123 1
DEF123 2 [/pre]

And what is the Primary Key field in this table?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MITTENCAT]

That would work, the primary field is field one, which ever one is at the top.

 

[Andrzejek]

FieldOne with the values:
ABC123
ABC123
ABC123
DEF123
DEF123
DEF123

Cannot be a Primary Key field in your table. PK has to be unique for each record (and cannot be NULL)

You may want to look at this post: thread705-1763220 and use the logic to accomplish your task.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Blorf]

Interesting.

OK, if you add a new field, just temporarily, that is an auto number field, then you could do this with an update query.

if the auto number / 2 = Int(auto number / 2) then the value is 1 else
if the auto number / 3 = Int(auto number / 3) then the value is 2 else
The Value is 0

You could also do this using a record set in VBA that would not require the auto number field using the same logic as above by just using a variable that increments each time you move to the next record.

Hope this helps.



There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That DonÆt.

http://www.thelegacyreturns.com/