inserting field name from a query 1

[welshone]

hello all,
how can I do this :
I have a table with fields jan, feb, mar, apr, jun, and so on , in each of these fields is either a null or some value.
I want to beable to run a query so I can input a month and bring back data for that month that is not null.

something like :
select [chosen month] from table
where [chosen month] is not null

can that be done?

thank you for your help/

 

[dhookom]

Can you normalize your data? Months are field values, not field names. Your structure should be such that each value creates a new records for each month. If you can't change your structure, you can normalize it with a union query.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[welshone]

unfortunatley I cannot change the structure.
I have more fields I wish to display, so unioning the data will not work will it ?

the data looks like :

name access jan feb mar apr may
ws 12 y u y n
rt 11 u u
ek 11 u y y y

 

[lespaul]

no, you do need to union it. This query will normalize your structure:

SELECT [NAME], [ACCESS], "Jan" As MonthName, Jan As SomeLabel From tblName
UNION
SELECT [NAME], [ACCESS], "Feb", Feb From tblName
UNION
SELECT [NAME], [ACCESS], "Mar", Mar From tblName
etc...
UNION
SELECT [NAME], [ACCESS], "Dec", Dec From tblName

Save this: qryNormalized

now use it as the source for a query:

SELECT * FROM qryNormalized WHERE MonthName = [Which month?]



Leslie

 

[welshone]

thanks les, I understand !