Create new field within table and populate

[Andy]

I have a table that I need to create a new field in and then generate the
values of this new field based upon an if statement.

I can do with a make table followed by an update query, but because the
table is in excess of a million records, I do not want to make any new
tables, just append to the existing one. Simple solution I know, but cannot
think of it!

Thanks

 

[Andy]

I don't want to mess about manually with creating extra fields within the
table, then running the query!

 

[Bob Barrows [MVP]]

I have a table that I need to create a new field in and then generate
the values of this new field based upon an if statement.

I can do with a make table followed by an update query, but because
the table is in excess of a million records, I do not want to make
any new tables, just append to the existing one. Simple solution I
know, but cannot think of it!

I don't understand the problem: surely you can simply open the table in
Design mode and add a new field, right? Once the new field is there, simply
run your update statement.

Why add a new field? Why not just create a calculated field or a saved query
that generates this new value on the fly?

Here are some questions to help decide when a new field is really needed:
Do you need to index this field? Do you need to save the value for
historical purposes?
If you can answer no to both these questions, then you probably don't need a
new field.

 

[Bob Barrows [MVP]]

Well then, I don't understand what you are trying to do.
You said "I have a table that I need to create a new field in "
And now you say "I don't want to mess about manually with creating extra
fields "

I'm sorry, but these statements seem to contradict each other.

 

[Andy]

Sorry, I had meant that I don't want to go into the table design view, create
a new field, then save the table, then run the query.

I think that I need to create the physical new field however as with a
million records, to calculate the query on the fly a number of times a day,
will take a long time of calculating. I would rather just calculate this
value and hard-write to the table itself.

 

[John W. Vinson]

I have a table that I need to create a new field in and then generate the
values of this new field based upon an if statement.

I can do with a make table followed by an update query, but because the
table is in excess of a million records, I do not want to make any new
tables, just append to the existing one. Simple solution I know, but cannot
think of it!

Thanks

The problem you'll have is that creating a new field in a table in code will
force Access to attempt to load the *entire table* into memory twice, once for
the existing copy and once for the new one. With that big a table you WILL run
out of memory. To get this done you will need to create the new table, empty,
with the appropriate field definitions and sizes (note: a MakeTable will make
all Text fields 255 bytes), followed by an Append query.

If you're routinely and repeatedly needing to add fields to your table, then
your table design is almost surely wrong. What is this field? Why is it
necessary to (apparently repeatedly?) change the structure of your table?

 

[Bob Barrows [MVP]]

Sorry, I had meant that I don't want to go into the table design
view, create a new field, then save the table, then run the query.

I don't understand. Why not? it's got to be less cpu-intensive than running
a make-table query that will lose much of the schema of your source table.
You will wind up having to recreate primary keys and any indexes you have
hopefully created to aid data retrieval, as well as fixing data types and
field sizes that Jet got wrong when creating the new table automatically.

I think that I need to create the physical new field however as with a
million records, to calculate the query on the fly a number of times
a day, will take a long time of calculating. I would rather just
calculate this value and hard-write to the table itself.

Are you routinely running queries that retrieve a million records at a
time?!? A number of times a day?!??? :-0

Look, why don't you test using a calculation in a saved query? You may find
it's not quite as slow as you are thinking it would be. If it turns out to
be prohibitively slow, then you can continue with your plan to hard-write
the result of the calculation into the table ... nothing lost but a little
time, which should be minor compared to the time spent in this newsgroup
already ... ;-)

Anyways, I am trying to figure out what your question is. How exactly do you
want us to help you ... given that you seem unwilling to take our advice ;-)

 

[Bob Barrows [MVP]]

The problem you'll have is that creating a new field in a table in
code will force Access to attempt to load the *entire table* into
memory twice, once for the existing copy and once for the new one.
With that big a table you WILL run out of memory.

Hey, John, I know the answer to this question for SQL Server, but not for
Jet:
I can believe that what you say is true for a DAO tabledef or ADOX Table
modification, but how about a JetSQL DDL "ALTER TABLE' statement? I would
suspect the answer to be "no", but I'm not really sure ...

 

[John W. Vinson]

Hey, John, I know the answer to this question for SQL Server, but not for
Jet:
I can believe that what you say is true for a DAO tabledef or ADOX Table
modification, but how about a JetSQL DDL "ALTER TABLE' statement? I would
suspect the answer to be "no", but I'm not really sure ...

Interesting question to which I do not know the answer. I used DDL all the
time way back when Oracle was my poison of choice, but haven't used it much in
Access (and until the most recent versions it really wasn't very well
supported), but I'll need to explore it a bit to see!