MSAccess - Adding a field to an existing table..

[milehigh]

I have a existing table called 'data'

I want to add a field called 'month'

For the 2000 records already in the 'data' table I want to randomly assign a month to each.

I was trying to use an update query but having trouble... Any suggestions?

Thanks in advance.

 

[Haircut]

Open up the SQL editor and the syntax is:

ALTER TABLE tablename
ADD COLUMN columnname columndatatype

 

[bunker]

in access go to design mode on the table and add the column.

Not sure about randomly adding a month though.

 

[labgeek]

Unfortunately this seems simple but it's not. Seems like you should be able to "UPDATE table1 SET [month] = rnd()*12;" But you can't.
The problem with updating using rnd()*12 is that it's rnd() is only determined the 1 time then all records are updated with that 1 value. This is a known "feature" of the Jet engine Access uses.

You could write a VBA procedure under Modules, and step through updating each record.

Do you have a Autonumber field on the table? If so, you could "UPDATE table1 SET [month] = ([recid] Mod 12)+1;" where table1 is your table name, and recid is the autonumber field. Don't have an autonumber field, add it. Run the update and then delete the field. This isn't a random distribution but will evenly divide the records into months 1-12. Note you want the +1 on the end because mod will yield 0 through 11.


Edit -

Ok figured out a way to do it.
Create a 2nd table matching the first (use copy it's easiest... - Structure ONLY!) Then add the month field and set rnd()*12 as the default value. Then run the query INSERT INTO newtable SELECT * FROM oldtable; The default value statement will get executed for each inserted record giving you a random (pseudo-random anyway) month for each record.