SQL Query - Returning One Specific Column

[tdotsmiley]

still doesnt give me the output i need. it removes the first name which does not have 09 in it, but then the rest are still there.
what this query is doing is removing the 09 date from the column and displaying all the names.

what i need it to do is to remove the name (with the record) of people who have 09 anywhere in their record

thanks a lot

[MMcCarthy]

SELECT NAME, IIf(Mid(W1,2,2) <>'09',W1,Nul l) As Col1,
IIf(Mid(W2,2,2) <>'09',W2,Nul l) As Col2,
IIf(Mid(W3,2,2) <>'09',W3,Nul l) As Col3,
IIf(Mid(W4,2,2) <>'09',W4,Nul l) As Col4,
IIf(Mid(W5,2,2) <>'09',W5,Nul l) As Col5,
IIf(Mid(W6,2,2) <>'09',W6,Nul l) As Col6,
IIf(Mid(W7,2,2) <>'09',W7,Nul l) As Col7
FROM Adults
WHERE Col1 Is Not Null OR Col2 Is Not Null OR Col3 Is Not Null
OR Col4 Is Not Null OR Col5 Is Not Null OR Col6 Is Not Null OR Col7 Is Not Null;

[tdotsmiley]

SELECT NAME, IIf(Mid(W1,2,2) <>'09',W1,Nul l) As Col1,
IIf(Mid(W2,2,2) <>'09',W2,Nul l) As Col2,
IIf(Mid(W3,2,2) <>'09',W3,Nul l) As Col3,
IIf(Mid(W4,2,2) <>'09',W4,Nul l) As Col4,
IIf(Mid(W5,2,2) <>'09',W5,Nul l) As Col5,
IIf(Mid(W6,2,2) <>'09',W6,Nul l) As Col6,
IIf(Mid(W7,2,2) <>'09',W7,Nul l) As Col7
FROM Adults
WHERE Col1 Is Not Null OR Col2 Is Not Null OR Col3 Is Not Null
OR Col4 Is Not Null OR Col5 Is Not Null OR Col6 Is Not Null OR Col7 Is Not Null;

that still does not perform the correct query :S

[Killer42]

that still does not perform the correct query :S

Could you try spelling out in as much detail as possible, exactly what you want the query to do?

[MMcCarthy]

Does this make any difference?
SELECT Name, IIf(Mid(W1,2,2) <>'09',W1,Nul l) As Col1,
IIf(Mid(W2,2,2) <>'09',W2,Nul l) As Col2,
IIf(Mid(W3,2,2) <>'09',W3,Nul l) As Col3,
IIf(Mid(W4,2,2) <>'09',W4,Nul l) As Col4,
IIf(Mid(W5,2,2) <>'09',W5,Nul l) As Col5,
IIf(Mid(W6,2,2) <>'09',W6,Nul l) As Col6,
IIf(Mid(W7,2,2) <>'09',W7,Nul l) As Col7
FROM Adults
WHERE Col1 Is Not Null OR Col2 Is Not Null OR Col3 Is Not Null
OR Col4 Is Not Null OR Col5 Is Not Null OR Col6 Is Not Null OR Col7 Is Not Null GROUP BY Name;

[tdotsmiley]

Does this make any difference?
SELECT Name, IIf(Mid(W1,2,2) <>'09',W1,Nul l) As Col1,
IIf(Mid(W2,2,2) <>'09',W2,Nul l) As Col2,
IIf(Mid(W3,2,2) <>'09',W3,Nul l) As Col3,
IIf(Mid(W4,2,2) <>'09',W4,Nul l) As Col4,
IIf(Mid(W5,2,2) <>'09',W5,Nul l) As Col5,
IIf(Mid(W6,2,2) <>'09',W6,Nul l) As Col6,
IIf(Mid(W7,2,2) <>'09',W7,Nul l) As Col7
FROM Adults
WHERE Col1 Is Not Null OR Col2 Is Not Null OR Col3 Is Not Null
OR Col4 Is Not Null OR Col5 Is Not Null OR Col6 Is Not Null OR Col7 Is Not Null GROUP BY Name;

when i execute this query i get the error that the specified expression IIf ... is not part of an aggregate function.

with this query i want to be able to select NAMES of people who do not have 09 in any of the W1 - W7 columns. basically, now i want to result the opposite of what was first done - selecting those that have 09.

i tried doing the simple opposite of using NOT LIKE, but that does not work.

i hope i am a bit more clearer.

[Killer42]

when i execute this query i get the error that the specified expression IIf ... is not part of an aggregate function. with this query i want to be able to select NAMES of people who do not have 09 in any of the W1 - W7 columns. basically, now i want to result the opposite of what was first done - selecting those that have 09.
i tried doing the simple opposite of using NOT LIKE, but that does not work.
i hope i am a bit more clearer.

Ok, given this description it sounds extremely simple, and has in fact already been covered by mmccarthy. I think this will do it

SELECT * From Adults
WHERE ((W1 Not Like "*/09*")    AND (W2 Not Like "*/09*")
  AND (W3 Not Like "*/09*")    AND (W4 Not Like "*/09*")
  AND (W5 Not Like "*/09*")    AND (W6 Not Like "*/09*")
  AND (W7 Not Like "*/09*"));

So, given that this has already been tried and rejected, what is the complication? Is it that you want each name only once? That would be where the aggregate (GROUP BY) comes in.

[tdotsmiley]

Ok, given this description it sounds extremely simple, and has in fact already been covered by mmccarthy. I think this will do it

SELECT * From Adults
WHERE ((W1 Not Like "*/09*")    AND (W2 Not Like "*/09*")
  AND (W3 Not Like "*/09*")    AND (W4 Not Like "*/09*")
  AND (W5 Not Like "*/09*")    AND (W6 Not Like "*/09*")
  AND (W7 Not Like "*/09*"));

So, given that this has already been tried and rejected, what is the complication? Is it that you want each name only once? That would be where the aggregate (GROUP BY) comes in.

yes, i did try the above code - basically what comes up are just the column names - no data is shown in the cells.

[Killer42]

yes, i did try the above code - basically what comes up are just the column names - no data is shown in the cells.

Well... I hate to ask the obvious, but are you sure there are any records that match the criteria? Maybe all of your records do have a "09" entry somewhere.

[tdotsmiley]

Well... I hate to ask the obvious, but are you sure there are any records that match the criteria? Maybe all of your records do have a "09" entry somewhere.

yup, i am 100% sure that there are multiple records which do not have 09 anywhere.

i don't know what else to try, you all have been of great help, but nothing seems to be able to execute the result i need :S

[Killer42]

yup, i am 100% sure that there are multiple records which do not have 09 anywhere.

i don't know what else to try, you all have been of great help, but nothing seems to be able to execute the result i need :S

This question is directed at someone like mmccarthy, who knows what they're talking about. Can you use functions like Instr() in a query? Perhaps something like this would work...

SELECT * From Adults
WHERE ((Instr(W1,"/09") = 0)    AND (Instr(W2,"/09") = 0)
  AND (Instr(W3,"/09") = 0)    AND (Instr(W4,"/09") = 0)
  AND (Instr(W5,"/09") = 0)    AND (Instr(W6,"/09") = 0)
  AND (Instr(W7,"/09") = 0));

[tdotsmiley]

This question is directed at someone like mmccarthy, who knows what they're talking about. Can you use functions like Instr() in a query? Perhaps something like this would work...

SELECT * From Adults
WHERE ((Instr(W1,"/09") = 0)    AND (Instr(W2,"/09") = 0)
  AND (Instr(W3,"/09") = 0)    AND (Instr(W4,"/09") = 0)
  AND (Instr(W5,"/09") = 0)    AND (Instr(W6,"/09") = 0)
  AND (Instr(W7,"/09") = 0));

ya, i'm not that great at access - just know the basics. hopefully mccarthy can provide some insight!

[Killer42]

ya, i'm not that great at access - just know the basics. hopefully mccarthy can provide some insight!

Of course, the best way to learn is probably to try it and see what happens. :)

[MMcCarthy]

Good suggestion Killer. OK Lets try:

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);

[tdotsmiley]

Good suggestion Killer. OK Lets try:

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);

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