Insert Column with Update Query

[ISW]

I did a search for this with no luck. I want to say I used to do this but it has been about two years since I have worked with Access. I know that if this is possible it should be pretty easy. One of you want to help me out here?

I thought it would go Something like this:
Filed: New_Column_Name
Table: Table_Name
Update to: ?

 

[Kavy]

Hey ISW,

I am not an expert, but danial in the link below suggested to make a new table to do it. I guess you could make a table with your old table data, then delete the old one all in the same query, and it should complete the same objective?

http://www.mrexcel.com/forum/showthread.php?t=299732

 

[ISW]

Thanks for the response. I will look into this more and see if it makes sense for me to do. The purpose of this is to save steps so if it will ultimately do that then we will see.

Any other suggestions are appreciated.

 

[jackd]

The Update query is for updating values(s) in one or more existing fields in an existing table.

The Update query is NOT a means to change the structure of a Table.

You could use an ALTER TABLE data definition approach.

Here's a link to topic generally
http://islandman.org/umuc/ifsm410/DataDefinitionQuery.html


The question becomes-- When you add this field to your table, how do you populate this new field? What about the existing records that did/do not have a value for this new field?

 

[SydneyGeek]

Another question: How often do you need to add new fields? You mention the need to 'save steps' so is it a regular requirement, say for a new month's data?

If so, you should redesign the database instead. There should be no need to add new fields when the next period's data is added. You find yourself continually updating queries, forms and reports, which is not how databases are meant to work.

Denis

 

[boblarson]

I agree with Denis - you should NOT be adding fields to the table for adding new months for the data. You should be adding ROWS of data and NOT COLUMNS. By doing so you are violating the rules of normalization and also making it much harder to do aggregating of data.