Please help building up SQLs, for separating out Year, Quarter and Month data

[rushitshah]

Hello Ppl..

I want help regarding generating different SQL statements.....for the following kind of table......

Product|Parameter|ItmDes|jan'05|feb'05|05Q1|05Y|jan'06|06Q2|06Y.......
_______________________________________________________________
So what I want to do is to run queries to find out monthly, quarterly and yearly data separately.....

1) jan'05, feb'05, ..... jan'06, feb'06......are monthly data...

so what I want is (logically)

when month is selected on the form for the output data,
ssql = "Select s.*jan* AND s.*Feb* AND s.*Mar*...........& _
"INTO [MONTH]" & _
"FROM [" & Me.PCYcle & "] s "

but SQL does not support this kind of * statements. I am sure that month field will include Jan, Feb, Mar, that kind of string , but it can be for year 05, 06, 07....so not sure about the year part.

2) 05'Q1, 05'Q2, ..... 06'Q3, 06'Q4......are quarterly data...

so in this case,

when quarter is selected on the form for the output data,
ssql = "Select s.*Q*" & _
"INTO [QUARTER]" & _
"FROM [" & Me.PCYcle & "] s "

but SQL does not support this kind of * statements. I am sure that month field will include Q1, Q2, Q3, Q4 that kind of string , but it can be for year 05, 06, 07....so not sure about the year part.

3) 05'Y, 06'Y, 07'Y......are yearly data...

so in this case,

when year is selected on the form for the output data,
ssql = "Select s.*Y*" & _
"INTO [Year]" & _
"FROM [" & Me.PCYcle & "] s "

but SQL does not support this kind of * statements. I am sure that month field will include Y in all year data column, but it can be for year 05, 06, 07....so not sure about the year part.

Please help building up this kind of logic and SQL statements... I am not sure about the year part in columns, it can be anything....so can not specifically define columns like,

select s.05'Q1, s.06'Q2, s.07'Q3........

Because I am developing this application for all kind of input tables and they may have different year range, so need some kind of dynamic way to generate this.

Thanks in advance....

 

[RV]

You're running into some problems caused by not normalising your database structure.
I suggest to start on that.
Use the search facility on search on "normalisation", that should help to to understand what relational databases are all 'bout and how to create an adequate database structure.

RV

 

[rushitshah]

Thanks RV,
I know that the DB is poorly designed. But I am not the owner of the DB.
The tables in the DB are created by converting the spreadsheets in to tables. The spreadsheet is generated by another tool, so pretty much I am stuck with this.

I know that to store data period wise is a better option and should do that, but can not change is as it is generated by a whole procedure. So trying to find out a way out of this.

Please help with those SQLs for access DB and with DAO. I am sure that there should be some way though not as elegant as possible, but i do not have other alternative.

Thanks a in advance..