SQL Query - Returning One Specific Column

[tdotsmiley]

Hi all,

I need some help with a sql query I am trying to do.
I had a sheet which I copied from excel.

Currently the columns which I have are Name, Phone, W1, W2, W3, W4, W5, W6, W7.

These W columns contain dates in each of them (as Strings).

The query I want to do is to Select a Name and W cell for each person that has a 09 (ie. September) in any of the W columns. I am able to do this using the following code:

SELECT Adults.NAME, W1, W2, W3, W4, W5, W6, W7
FROM Adults
WHERE (((Adults.W1) Like '*09/*')) OR (((Adults.W2) Like '*09/*')) OR (((Adults.W3) Like '*09/*')) OR (((Adults.W4) Like '*09/*')) OR (((Adults.W5) Like '*09/*')) OR (((Adults.W6) Like '*09/*')) OR (((Adults.W7) Like '*09/*'));

However, what I want my output to show is ONLY the September date. Right now it shows all of the other dates associated with the person. I know I get all of this extra output because my SELECT line states W1 ... W7 - but I am not sure how to change my code to result only the September stuff.

I know it sounds a bit confusing, but any help would be MUCH appreciated.

thanks

[MMcCarthy]

SELECT NAME, W1, W2, W3, W4, W5, W6, W7
FROM Adults
WHERE ((Month(CDate(W 1)) = 9) OR (Month(CDate(W2 )) = 9)
OR (Month(CDate(W3 )) = 9) OR (Month(CDate(W4 )) = 9)
OR (Month(CDate(W5 )) = 9) OR (Month(CDate(W6 )) = 9)
OR (Month(CDate(W7 )) = 9));

[tdotsmiley]

hi, thanks for that...however, i don't specifically use date.

let me show you an example of what i mean - please let me know if i can email you with a sample file so that it is easier for you to understand what i am asking :-)

the columns of W contain text such as:
D09/17 or G03/23 - the letter preceding is some sort code that I need for my coumns. Hence, that is why I have used the term Like in my query so that I can select other columns which have the 09.

[Killer42]

hi, thanks for that...however, i don't specifically use date.

let me show you an example of what i mean - please let me know if i can email you with a sample file so that it is easier for you to understand what i am asking :-)

the columns of W contain text such as:
D09/17 or G03/23 - the letter preceding is some sort code that I need for my coumns. Hence, that is why I have used the term Like in my query so that I can select other columns which have the 09.

Hi.

Can I just try to clarify the question here? I believe what you are asking is not how to select the rows, but how to display only the column(s) containing the valid date(s). Correct?

I don't know whether I can help much, but I wonder whether an IIF() for each field might be useful to produce either the valid date or nothing.

[MMcCarthy]

SELECT NAME, IIf(Mid(W1,2,2) ='09',W1,Null) As W1, IIf(Mid(W2,2,2) ='09',W2,Null) As W2, IIf(Mid(W3,2,2) ='09',W3,Null) As W3, IIf(Mid(W4,2,2) ='09',W4,Null) As W4, IIf(Mid(W5,2,2) ='09',W5,Null) As W5, IIf(Mid(W6,2,2) ='09',W6,Null) As W6, IIf(Mid(W7,2,2) ='09',W7,Null) As W7 FROM Adults WHERE W1 Is Not Null Or W2 Is Not Null Or W3 Is Not Null Or W4 Is Not Null Or W5 Is Not Null Or W6 Is Not Null Or W7 Is Not Null GROUP BY Name;

If this doesn't work, remove the group by name and try again.

If you only want one column it can be done, but only if no more than one of the columns will hold the september date at any one time. If you want to go for that let me know.

[tdotsmiley]

when i try to run this query I get an error stating that W1 is circular :S

[tdotsmiley]

when i try to run this query I get an error stating that W1 is circular :S

sorry i forgot to include that yes, many columns can hold the september date in them ... which makes it even more confusing.

[tdotsmiley]

new question:
this one should be simple i hope.

i want to try to do a not like '09' but the result I get when it is executed are all the names.

this is the query:
SELECT Elementary.NAME , W1, W2, W3, W4, W5, W6, W7
FROM Elementary
WHERE (((Elementary.W 1) not like '*09/*')) OR (((Elementary.W 2) not like '*09/*')) OR (((Elementary.W 3) not like '*09/*')) OR (((Elementary.W 4) not like '*09/*')) OR (((Elementary.W 5) not like '*09/*')) OR (((Elementary.W 6) not like '*09/*')) OR (((Elementary.W 7) not like '*09/*'));

i want it NOT to return any of the cells which have 09 in them, however they are all returned.

[MMcCarthy]

Change the OR's to AND's

new question:
this one should be simple i hope.

i want to try to do a not like '09' but the result I get when it is executed are all the names.

this is the query:
SELECT Elementary.NAME , W1, W2, W3, W4, W5, W6, W7
FROM Elementary
WHERE (((Elementary.W 1) not like '*09/*')) OR (((Elementary.W 2) not like '*09/*')) OR (((Elementary.W 3) not like '*09/*')) OR (((Elementary.W 4) not like '*09/*')) OR (((Elementary.W 5) not like '*09/*')) OR (((Elementary.W 6) not like '*09/*')) OR (((Elementary.W 7) not like '*09/*'));

i want it NOT to return any of the cells which have 09 in them, however they are all returned.

[Killer42]

Change the OR's to AND's

I think the basic problem here is that whether you use AND, OR or whatever, these criteria are selecting records (of course). While tdotsmiley, if I'm reading correctly, wants to pick and choose particular fields. That was why I brought up the issue of IIF().

If I'm mistaken, just ignore me - everyone else does. :)

[MMcCarthy]

I think you could be right killer and I could never ignore you.



This should solve the circular reference problem:



SELECT NAME, IIf(Mid(W1,2,2) ='09',W1,Null) As Col1,

IIf(Mid(W2,2,2) ='09',W2,Null) As Col2,

IIf(Mid(W3,2,2) ='09',W3,Null) As Col3,

IIf(Mid(W4,2,2) ='09',W4,Null) As Col4,

IIf(Mid(W5,2,2) ='09',W5,Null) As Col5,

IIf(Mid(W6,2,2) ='09',W6,Null) As Col6,

IIf(Mid(W7,2,2) ='09',W7,Null) 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;

For the negative query try:



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]

I think the basic problem here is that whether you use AND, OR or whatever, these criteria are selecting records (of course). While tdotsmiley, if I'm reading correctly, wants to pick and choose particular fields. That was why I brought up the issue of IIF().

If I'm mistaken, just ignore me - everyone else does. :)

yes i do want to select the fields ... but i still am getting an error when i run the statement that mccarthy has supplied me with :-S the IIF thing makes sense, but the error i get is:
You tried to execute a query that does not include the specified expression 'IIf(Mid(W1,2,2 )='09',W1,Null) ' as part of an aggregate function.

[tdotsmiley]

SELECT Adults.NAME
FROM Adults
WHERE (((Adults.W1) Not Like '*09/*')) AND (((Adults.W2) Not Like '*09/*')) AND (((Adults.W3) Not Like '*09/*')) AND (((Adults.W4) Not Like '*09/*')) AND (((Adults.W5) Not Like '*09/*')) AND (((Adults.W6) Not Like '*09/*')) AND (((Adults.W7) Not Like '*09/*'));

this returns just the column header of NAME

[MMcCarthy]

Remove the GROUP BY Name bit from the end of both queries.

yes i do want to select the fields ... but i still am getting an error when i run the statement that mccarthy has supplied me with :-S the IIF thing makes sense, but the error i get is:
You tried to execute a query that does not include the specified expression 'IIf(Mid(W1,2,2 )='09',W1,Null) ' as part of an aggregate function.

[MMcCarthy]

Change the negative query to:

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 And Col2 Is Not Null And Col3 Is Not Null
And Col4 Is Not Null And Col5 Is Not Null And Col6 Is Not Null And Col7 Is Not Null;