How to add a new field to a Make Table query?

[Guest]

I've got a make table query that gets it's source from three different tables
but I would also like to add two (new) additional fields to the destination
table.
One would be a Yes/No field and the other a Date/Time field.
Is this possible to do and if so, can you explain how this can be achieved?
I don't want to use an Append query (as much as this would resolve this
particular issue) as it would mean deleting the contents of the table each
time a particular command is undertaken and I presume this would lead to some
major database bloating (?) I'm presuming that making a table is less prone
to creating bloat.

Thanks for your help guys,

Lee

 

[Allen Browne]

Making and removing a table will not be less bloating than deleting the
data. A Delete and Make Table query is the sensible choice.

If you are deperate to do it the other way anyway, you might be able to get
away with values like this in your Make Table query:
IIf(True, Null, #1/1/2002#) AS MyDateField
If you just use Null, JET has no idea what type the field should be, so you
get a Text field. The IIf() expression always returns Null (because the
first argument is always True), but the presence of an alternative gives it
the clue as to the intended data type.

 

[tina]

PMFJI, Allen, but did you mean to say "A Delete and *Append* query is the
sensible choice." ?

hth

 

[Guest]

I hope so Tina as I don't understand Allen's reply otherwise! ;-D

Thanks for your input guys, I'll go down the Delete and Append route to make
life a little easier.

Lee

 

[Allen Browne]

Yes, Tina, thanks for picking that up.

A Delete query, followed by an Append query is the sensible choice.