Add column query (1 Viewer)

[jadown]

Hello,

I made a query that makes a new table (NewTable). That works fine. However, I would like to add a new column that I will use an update query later to update the new column I created. Is this possible?

 

[plog]

Yes, but probably not the way to do things. MakeTable, Append and Update queries are the holy trinity of improperly structured databases.

Why did you need to make a new table? Why do you need to update its data in the future?

 

[jadown]

I really wanted a new query (qB) to run off an existing query (qA) but I couldn't figure out how to add a new column in the new querty (qB) that populates if the description name in the existing query (qA) contains the word "time" anywhere in that field.

So my logic was that qA would make a table. Then write another query to add a new column to the table and finally use an update querty to populate the new column based on if the description name in the table contained the word "time".

 

[pr2-eugin]

It can (Should) be written in the Second Query.. This will be simpler than you think.. Say if you have the first Query (qA) that populates the required fields..

SELECT oneField, twoField 
FROM tableA

The current second query (qB) which is based on the first query (qA)..

SELECT qA.oneField, qA.twoField
FROM qA

All you need to add is the new column that you need...

SELECT qA.oneField, qA.twoField, [B]IIf(InStr(qA.twoField, "time") <> 0, "Culprit", "Clean")[/B] [COLOR=Red][B]AS newColumn[/B][/COLOR]
FROM qA

Hope this helps !!

 

[jadown]

Paul,

thanks again. This works but I have so many different IIf(InStr(qA.twoField, "time") <> 0, "Culprit", statements that I get a syntax error. So I guess I wll have to write two different queries with all the IIf(InStr statements and then write another query to combine the newColumn1 is blank, then populate with newColumn2.

 

[pr2-eugin]

You have many Columns to create, based on different CheckStrings (time)? Or you have to create one Column that will check many CheckStrings (time, money, value)? IF it is the first, then you can just add many columns.. If it is the latter, you can create a simple function and then return "Culprit" if it matches the pattern.. Else return an Empty String..

 

[jadown]

Well let me ask this. Can I use the OR in the IIf(InStr(qA.twoField, "time" OR "day" OR "week") <> 0, "Culprit", "Clean" (this doesn't work)

If there is a way to use it like this it may cut down on the number of queries I have to write.

If I do have to write multiple queries, my final query can be if

IIf(qA.NewColumn AND qB.NewColumn2 <>0,qC.Column3,.....and so on.

 

[pr2-eugin]

Using Multiple conditions to Check will make you really confused.. So I would suggest you write a function.. and use that function to determine "Culprit" and "Clean"... If you do go forward in creating this complicated IIF (which I am not a Fan of) it should be like..

IIf(InStr(qA.twoField, "time") <> 0 [COLOR=Red][B]Or[/B][/COLOR] InStr(qA.twoField, "day") <> 0 [COLOR=Red][B]Or[/B][/COLOR] InStr(qA.twoField, "week") <> 0, "Culprit", "Clean")

Each string to test will become a new criteria..