How to create a new field in a table with a query

[mrtauntaun]

I need to update table1 with data from table2 using an update query. The problem is there is no field in table1 for me to update to. I want the query to create a new field in table1 so I then have a valid target to update the data too.

Example:
Table2 has a field named 'DataField'
Create [Table1].[Datafield] and update data from [Table2].[Datafield] to [Table1].[Datafield]

Note that Table1 and Table2 are joined by primary key, where both fields are equal (solid join).

Thank you!

 

[dhookom]

What's wrong with going to the design view of the table and adding the field? You can also use a data-definition query or code to add a field to a table. Search Help on these options and if you don't understand then come back.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mrtauntaun]

There is nothing wrong with doing it that way, but my end result is to have this query part of a macro I have run daily. This way everything happens automatically and I don't have to create the field by hand every day. I would rather not use code. I will search for more information on data-definition queries, thanks.

 

[dhookom]

Access SQL supports the "ALTER TABLE" data-definition query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

and you only have to add the field to the table once and it's there forever......

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[mrtauntaun]

No, the table is pulled from a daily feed from another system and creates table1. So I will need to create this field on a daily basis. Effectively the original table is deleted and recreated daily. It's not my DB, im just being asked to make changes to it.

 

[gmmastros]

>> but my end result is to have this query part of a macro I have run daily

Do you have visions of adding a field to a table every day?

I strongly encourage you to 'change your mind'. This is terribly un-normalized. Also, I'm reasonably certain that Access has a limit to the number of fields in a table. It's probably something like 256 fields, maybe 1024 fields. Whatever it is, you'll probably wish you had found another solution (hopefully one that is properly normalized).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrtauntaun]

I completely agree, gmmastros, it's not the way I would do it, but its the way I was told to do it. Unfortunately it's not my DB. It looks like our two posts overlapped a bit, but the table is effectively deleted everyday and remade via a push through a ODBC link from another system.

 

[gmmastros]

Understood. I felt obligated to post because I was afraid that you were not aware of this potential problem.

I've used code similar to this in the past.

Alter Table [!]TableName[/!] Add Column [!]ColumnName[/!] [blue]DataType[/blue]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[fneily]

You state: "Effectively the original table is deleted and recreated daily". Besides the very important normalization problem already mentioned, every time you delete and then recreate the table, you immensely increase the size of the database. It does not "defrag" itself to recover the space left from deletion. You should not follow the order "told to do it". And to prove it, check the size under File-Database Properties the General tab, then do a Tools -Database Utilities - Compact and Repair and check the size again.

 

[mrtauntaun]

Its not that simple unfortunately. It's not my DB, and this is the way it needs to be done for a vareity of reasons. I do understand your point however, and the DB DOES compact and repair itself daily after these tasks are done.