Format fields in Make Table Query

[smiley1978]

Is it possible to specify formats, field lenth, etc inside a make table query. I have a query that creates a table based on customer data, but at the same time, I am adding new fields that do not exist on any table and I want to define the data type, length etc... inside the make table query so that the query creates the table as I need it. The table that is made is used in queries later down the road. For all fields that I am adding, I am setting the value of the field to Null.

FYI.. MS Access 2007

 

[jackd]

I have acc2003 not 2007.
You could create a data definition query to create the table you want with all the fields and sizes and data types you want. Then instead of a Make Table query, you could use an Append query to insert records into the newly created table.

 

[smiley1978]

Thanks!!! I have never used the data definition query before. Is there a good resource on how to use it?

 

[jackd]

It really ends up being straight SQL.

In 2003 you have to goto query wizard, new, then get an sql view, then select SQL specific, then Data definition query. I'm not sure where it is exactly in 2007.

Here's a recent data definition query I had

CREATE TABLE XArticles (
ArticleID NUMBER NOT NULL PRIMARY KEY,
ArticleTitle TEXT(60) NOT NULL,
Copyright Number Not NULL
)
;

For SQL samples of create tables see
http://msdn.microsoft.com/en-us/library/bb177893(v=office.12).aspx

More info on sql here

http://msdn.microsoft.com/en-us/library/aa140011(v=office.10).aspx#acfundsql_ddl
http://msdn.microsoft.com/en-us/library/aa140015(v=office.10).aspx
http://msdn.microsoft.com/en-us/library/aa139977(v=office.10).aspx

 

[smiley1978]

Thank you. That worked.