Add Yes/No Field in query

[fboehlandt]

Hello everyone,

I am looking to create a query that adds a new field to the resulting table. The field should be 'Yes/No' and for every entry the default should be 'Yes'. The query looks something like this:

Select .*, [here the new field]
From MyTable1
Union
Select .*, [here the new field]
Form MyTable2

The Union statement isn't really relevant, just for completion's sake. Any help is greatly appreciated

Regards
Florian

 

[JHB]

Hello everyone,

I am looking to create a query that adds a new field to the resulting table. The field should be 'Yes/No' and for every entry the default should be 'Yes'. The query looks something like this:

Select .*, [here the new field]
From MyTable1
Union
Select .*, [here the new field]
Form MyTable2

The Union statement isn't really relevant, just for completion's sake. Any help is greatly appreciated

Regards
Florian

You are not creating a query result table, it is only showing the query result.
If you want to create a table from a query, you have use the query statement INTO.

Select .*, [here the new field] INTO [TheNewTableName]
Form MyTable2

But it will not give you what you are asking for, a Yes/No field with the default = Yes. Either you have to create a new table using VBA-code, or alter an already created table using VBA-code.
Look in the MS-Access Help-file how to do that - search word = Alter.

 

[fboehlandt]

Hm, okay thanks. I didn't mean to create a table but I suppose the easiest solution is to create one, create a frield and run a code over it to fill it.
Cheers

 

[nanscombe]

This would give you a new column called yourColumnName with the string "Yes" in each record

Select .*, "Yes" as yourColumnName
From MyTable1
Union
Select .*, "Yes" as yourColumnName
Form MyTable2

You would get a boolean value of -1 (True or Yes) if you used

Select .*, Yes as yourColumnName
From MyTable1
Union
Select .*, Yes as yourColumnName
Form MyTable2

For a single query the column entry would look like yourColumnName:"Yes" or yourColumnName:Yes in the query designer.

The "Yes" would show up as a dummy column in the query results but would not exist in the original tables.

 

[fboehlandt]

Thanks Nigel, that is what I was looking for (Code 2). Greatly appreciated

 

[Pat Hartman]

Rather than "Yes", use True and False or -1 and 0

 

[nanscombe]

The word Yes also produces a value of -1 in the result.