SQL Query - Returning One Specific Column

[tdotsmiley]

oh ya, and i got an easier way (or so it looks simpler) to select those with '09':

SELECT Adults.NAME, W1
FROM Adults
WHERE (((Adults.W1) Like '*09/*'))

UNION
SELECT Adults.NAME, W2
FROM Adults
WHERE (((Adults.W2) Like '*09/*'))

UNION
SELECT Adults.NAME, W3
FROM Adults
WHERE (((Adults.W3) Like '*09/*'))

UNION
SELECT Adults.NAME, W4
FROM Adults
WHERE (((Adults.W4) Like '*09/*'))

UNION
SELECT Adults.NAME, W5
FROM Adults
WHERE (((Adults.W5) Like '*09/*'))

UNION
SELECT Adults.NAME, W6
FROM Adults
WHERE (((Adults.W6) Like '*09/*'))

UNION SELECT Adults.NAME, W7
FROM Adults
WHERE (((Adults.W7) Like '*09/*'));

[Killer42]

I don't like to go up against the master :) but I do have to question the "wordiness" of the negative query. The whole IIF() thing seems pointless, since by definition it is always false. In other words, where you say

SELECT Name, IIf(InStr([W1],"09"),Null,[W1]) AS Col1
IIf(InStr([W2],"09"),Null,[W2]) As Col2,
IIf(InStr([W3],"09"),Null,[W3]) As Col3,
IIf(InStr([W4],"09"),Null,[W4]) As Col4,
IIf(InStr([W5],"09"),Null,[W5]) As Col5,
IIf(InStr([W6],"09"),Null,[W6]) As Col6,
IIf(InStr([W7],"09"),Null,[W7]) As Col7
FROM Adults
WHERE (IIf(InStr([W1],"09"),[W1],Null) Is Not Null
AND IIf(InStr([W2],"09"),Null,[W2]) Is Not Null
AND IIf(InStr([W3],"09"),Null,[W3]) Is Not Null
AND IIf(InStr([W4],"09"),Null,[W4]) Is Not Null
AND IIf(InStr([W5],"09"),Null,[W5]) Is Not Null
AND IIf(InStr([W6],"09"),Null,[W6]) Is Not Null
AND IIf(InStr([W7],"09"),Null,[W7]) Is Not Null);

Wouldn't you get much the same result with

SELECT Name, W1, W2, W3, W4, W5, W6, W7
FROM Adults 
WHERE (IIf(InStr([W1],"09/"),[W1],Null) Is Not Null
AND IIf(InStr([W2],"09/"),Null,[W2]) Is Not Null
AND IIf(InStr([W3],"09/"),Null,[W3]) Is Not Null
AND IIf(InStr([W4],"09/"),Null,[W4]) Is Not Null
AND IIf(InStr([W5],"09/"),Null,[W5]) Is Not Null
AND IIf(InStr([W6],"09/"),Null,[W6]) Is Not Null
AND IIf(InStr([W7],"09/"),Null,[W7]) Is Not Null);

Haha... I was just about to say you had the Is Not Null backward here, too, when I realised you had reversed the order of the True/False results. Tricky! :)
Anyway, maybe it would still work if we boiled it down to a simplified version like

SELECT Name, W1, W2, W3, W4, W5, W6, W7
FROM Adults 
WHERE (Mid([W1],2,2)<>"09"
AND Mid([W2],2,2)<>"09"
AND Mid([W3],2,2)<>"09"
AND Mid([W4],2,2)<>"09"
AND Mid([W5],2,2)<>"09"
AND Mid([W6],2,2)<>"09"
AND Mid([W7],2,2)<>"09");

This seems as though it would be slightly more efficient, if it works.

[Killer42]

ok, this is giving me an error that the SELECT statement contains a word that is mis-spelled or punctutation is incorrect.

I believe there's a comma missing after Col1.

[MMcCarthy]

Just a missing comma, try again:

To get all names that do have "09":

SELECT Name, IIf(InStr([W1],"09"),[W1],Null) AS Col1,
IIf(InStr([W2],"09"),[W2],Null) As Col2,
IIf(InStr([W3],"09"),[W3],Null) As Col3,
IIf(InStr([W4],"09"),[W4],Null) As Col4,
IIf(InStr([W5],"09"),[W5],Null) As Col5,
IIf(InStr([W6],"09"),[W6],Null) As Col6,
IIf(InStr([W7],"09"),[W7],Null) As Col7
FROM Adults
WHERE (IIf(InStr([W1],"09"),[W1],Null) Is Not Null
AND IIf(InStr([W2],"09"),[W2],Null) Is Not Null
AND IIf(InStr([W3],"09"),[W3],Null) Is Not Null
AND IIf(InStr([W4],"09"),[W4],Null) Is Not Null
AND IIf(InStr([W5],"09"),[W5],Null) Is Not Null
AND IIf(InStr([W6],"09"),[W6],Null) Is Not Null
AND IIf(InStr([W7],"09"),[W7],Null) Is Not Null);

To get all names that don't have "09":

SELECT Name, IIf(InStr([W1],"09"),Null,[W1]) AS Col1,
IIf(InStr([W2],"09"),Null,[W2]) As Col2,
IIf(InStr([W3],"09"),Null,[W3]) As Col3,
IIf(InStr([W4],"09"),Null,[W4]) As Col4,
IIf(InStr([W5],"09"),Null,[W5]) As Col5,
IIf(InStr([W6],"09"),Null,[W6]) As Col6,
IIf(InStr([W7],"09"),Null,[W7]) As Col7
FROM Adults
WHERE (IIf(InStr([W1],"09"),[W1],Null) Is Not Null
AND IIf(InStr([W2],"09"),Null,[W2]) Is Not Null
AND IIf(InStr([W3],"09"),Null,[W3]) Is Not Null
AND IIf(InStr([W4],"09"),Null,[W4]) Is Not Null
AND IIf(InStr([W5],"09"),Null,[W5]) Is Not Null
AND IIf(InStr([W6],"09"),Null,[W6]) Is Not Null
AND IIf(InStr([W7],"09"),Null,[W7]) Is Not Null);

[NeoPa]

That's a beautiful and elegant result.
Bear in mind, though, that this may produce multiple records for the same NAME.
Also, probably not a problem, the resulting set shows all the W fields as W1 regardless of which field they came from.

[Killer42]

SELECT Adults.NAME, W1
FROM Adults
WHERE (((Adults.W1) Like '*09/*'))
UNION
SELECT Adults.NAME, W2
FROM Adults
WHERE (((Adults.W2) Like '*09/*'))

Could be good, but I think this will potentially return the same record multiple times. Is this a problem?

[Killer42]

These overlapping replies get a bit confusing at times, huh.

[NeoPa]

I just replied to T.:-)'s post (#31) and when I refresh, you guys have fitted in 3 (THREE) extra posts.
I'm off to bed.

[MMcCarthy]

I don't like to go up against the master :) but I do have to question the "wordiness" of the negative query. The whole IIF() thing seems pointless, since by definition it is always false. In other words, where you say

You can go against me anytime you like. I'm far from a master. Like most people in our game I picked up most of my information the hard way. That can leave some interesting gaps in knowledge.

You could very well be right about the query. I've done it so many times now I got locked into one approach. My biggest problem with queries is I get so bogged down in the logic I sometimes forget the preformance.

[NeoPa]

These overlapping replies get a bit confusing at times, huh.

roflmao
I was just gonna post that but I needed at least 10 chars - lol

[tdotsmiley]

Could be good, but I think this will potentially return the same record multiple times. Is this a problem?

nope, this one actually does what i want it too ... let me try the other stuff you all have posted, i shall be bak with a reply to the others

[tdotsmiley]

I don't like to go up against the master :) but I do have to question the "wordiness" of the negative query. The whole IIF() thing seems pointless, since by definition it is always false. In other words, where you say
Wouldn't you get much the same result with

SELECT Name, W1, W2, W3, W4, W5, W6, W7
FROM Adults 
WHERE (IIf(InStr([W1],"09/"),[W1],Null) Is Not Null
AND IIf(InStr([W2],"09/"),Null,[W2]) Is Not Null
AND IIf(InStr([W3],"09/"),Null,[W3]) Is Not Null
AND IIf(InStr([W4],"09/"),Null,[W4]) Is Not Null
AND IIf(InStr([W5],"09/"),Null,[W5]) Is Not Null
AND IIf(InStr([W6],"09/"),Null,[W6]) Is Not Null
AND IIf(InStr([W7],"09/"),Null,[W7]) Is Not Null);

Haha... I was just about to say you had the Is Not Null backward here, too, when I realised you had reversed the order of the True/False results. Tricky! :)
Anyway, maybe it would still work if we boiled it down to a simplified version like

SELECT Name, W1, W2, W3, W4, W5, W6, W7
FROM Adults 
WHERE (Mid([W1],2,2)<>"09"
AND Mid([W2],2,2)<>"09"
AND Mid([W3],2,2)<>"09"
AND Mid([W4],2,2)<>"09"
AND Mid([W5],2,2)<>"09"
AND Mid([W6],2,2)<>"09"
AND Mid([W7],2,2)<>"09");

This seems as though it would be slightly more efficient, if it works.

This code atleast generates a result. But another problem - I need to have the LIKE. The string in the field ie. T09/15 mean 09 - september and 15 - date .... if a name has D07/09 it also excludes this one - when in fact I just want to select ones that do not have September (09) ... therefore, I would have a whole lot more results.

[Killer42]

This code atleast generates a result. But another problem - I need to have the LIKE. The string in the field ie. T09/15 mean 09 - september and 15 - date .... if a name has D07/09 it also excludes this one - when in fact I just want to select ones that do not have September (09) ... therefore, I would have a whole lot more results.

No, that's not right. The Mid() function in that code specifies the 2nd and 3rd characters only, so the day would not be picked up. The parameters for Mid() are (String, startpos, length). If you used the Instr() function or the Like operator to scan for the month then you would need to specify "09/" to avoid the day.

[tdotsmiley]

No, that's not right. The Mid() function in that code specifies the 2nd and 3rd characters only, so the day would not be picked up. The parameters for Mid() are (String, startpos, length). If you used the Instr() function or the Like operator to scan for the month then you would need to specify "09/" to avoid the day.

killer42 - i meant to say that YOUR code worked for me.
mmccarthy's is still not working.

i have used the 09/ with LIKE in my very first query about showing just those in September, thats why that one works like a charm. but this one is still giving problems ...

[MMcCarthy]

killer42 - i meant to say that YOUR code worked for me.
mmccarthy's is still not working.

i have used the 09/ with LIKE in my very first query about showing just those in September, thats why that one works like a charm. but this one is still giving problems ...

What error are you getting on mine?