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

[isayhello]

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....

[anguswalker]

You have a fundamental problem with the structure of your tables. Whatever query you right (unless you use dynamic SQL to generate a complex stored procedure) you will have to keep amending it as the years advance. Far better would be to normalise your database- to have a fields for the data, another for the date and another for whether it is monthly, quarterly or yearly data. Either that or have three separate tables, one each for monthly, quarterly and yearly data, with a field in each identifying which month, quarter or year. Mind you if it is the same data in each, then any summary (including weekly or daily if you wanted) could be extracted with the relevant query from a table which just included a date field.

[isayhello]

thanks anguswalker..
I got you...
But the thing is that I am getting excel files as input. And I have to convert those files in to DB tables and use those tables for running queries....
I know that this is a flawed design...And I am not using much of database's power....

But still is there any way that I can run those kind of queries with this kind of structure. Because the excel spreadsheet are generated by different module and it is not possible to change it.

Plz help..

[anguswalker]

Well, you could use VB to loop through the fieldnames and separate them into monthly, quarterly and yearly.
Something like (and I haven't even remotely tested this!). I'm presuming you have already created an ADO connection called conn.

Dim rsTemp as ADODB.Recordset
Set rsTemp = New Recordset
Dim strMonthFields as String
Dim strSQL as String
Set rsTemp = conn.Execute("SELECT * from YourTable")
For i = 1 to rsTemp.Fields.Count
  if instr(rsTemp.Fields(i).name,"Jan") > 1 or instr(rsTemp.Fields(i).name,"Feb") > 1 [I]... etc.[/I] Then
  strMonthFields = strMonthFields & rsTemp.Fields(i).name & ", "
next i
Set rsTemp = Nothing
strMonthFields = left(strMonthFields,len(strMonthFields) - 2
strSQL = "SELECT " & strMonthFields & " FROM YourTable ... etc.")

[isayhello]

Hi Anguswalker...Thx...

Well I am using DAO..
Can I use same kind of logic...??

[anguswalker]

Don't know DAO, but I imagine something similar.

[salvelinus]

DAO version of Angus's code. Make sure to set a reference to DAO 3.6 Object Library.

Dim rsTemp as DAO.Recordset
Dim db as DAO.Database
Dim strMonthFields as String
Dim strSQL as String
 
Set db= CurrentDB
Set rsTemp = db.OpenRecordset("SELECT * from YourTable")
For i = 1 to rsTemp.Fields.Count
  if instr(rsTemp.Fields(i).name,"Jan") > 1 or instr(rsTemp.Fields(i).name,"Feb") > 1 [I]... etc.[/I] Then
  strMonthFields = strMonthFields & rsTemp.Fields(i).name & ", "
next i
Set rsTemp = Nothing
 
strMonthFields = left(strMonthFields,len(strMonthFields) - 2
strSQL = "SELECT " & strMonthFields & " FROM YourTable ... etc.")

[isayhello]

Thx Salvelinus and Anguswalker...
A great help... But getting an error "Item not found in this collection" on If statement.
Actually it prints the string correctly. 2005FEB, 2005MAR, 2006JAN, 2006FEB, 2006MAR, is the final string. There are 59 columns in my table and when i = 59 this happens and it gets stuck on If statement....

string str has the table name.

Dim rsTemp As DAO.Recordset
Dim db As DAO.Database
Dim strMonthFields As String
Dim strSQL8 As String

Set db = CurrentDb
Set rsTemp = db.OpenRecordset("SELECT * from [" & str & "]")
For i = 1 To rsTemp.Fields.Count
If InStr(rsTemp.Fields(i).Name, "Jan") > 1 Or InStr(rsTemp.Fields(i).Name, "Feb") > 1 Or InStr(rsTemp.Fields(i).Name, "Mar") > 1 Then
strMonthFields = strMonthFields & rsTemp.Fields(i).Name & ", "
End If
Debug.Print strMonthFields
Next i
Set rsTemp = Nothing
End If

strMonthFields = Left(strMonthFields, Len(strMonthFields) - 2)
strSQL = "SELECT " & strMonthFields & " FROM [" & str & "]"

[anguswalker]

Sorry! The first field is .Field(0). So:
For i = 1 To rsTemp.Fields.Count
should be
For i = 0 To rsTemp.Fields.Count - 1

[isayhello]

Thanks Angus..

That is right...

Can you help me with the last SQL statement??

How can I pass a string to SQL?

strSQL = "SELECT " & strMonthFields & " FROM [" & str & "]"

[isayhello]

Well Angus...

I did this...
strSQL = "SELECT " & strMonthFields & " INTO [Month] " & _
"FROM [" & str & "]"
Debug.Print strSQL

It prints in the immediate window as..

SELECT 2005FEB, 2005MAR, 2006JAN, 2006FEB, 2006MAR INTO [Month] FROM [march'05]

It gives error that "syntax error (Missing operator) in query expression '2005FEB'"

is it because we need to give something like [march'05].2005FEB? How to do that?

Apart from 2005FEB, 2005MAR, 2006JAN, 2006FEB, 2006MAR I also want to output Product Name, Parameter Name and ItemDes, but those are going to be same in all the queries, so How to add those three fields in the SQL expression?

Thanks in advance...

[anguswalker]

You'd have to change this line:
strMonthFields = strMonthFields & rsTemp.Fields(i).Name & ", "
to
strMonthFields = strMonthFields & "[" & str & "].[" & rsTemp.Fields(i).Name & "], "

[isayhello]

Thanks angus...
U R Genious.....It took me about 1.5 hours yesterday to try on that...While U did that in a single shot!!! Hats off to you, sir...

The other question....
This is a simple one...

Actually I have a check box on the form, How can I check its condition that whether it is checked or not....!!

As an example if I have a textbox and I want to check if it is null or not...
I would put....

If IsNull(Me.txtBox) = True Or Me.txtBox = "" Then

What is similar for to see if checkbox is checked or not....

Thanks again..

[anguswalker]

You are welcome. It's nice to be thought of as a programmer! I'm actually a headteacher who has been teaching himself VB and SQL to develop an application for school.
Anyway to answer your question- use .Value, e.g.
If Me.Check1.Value = 0 Then MsgBox "Unchecked"
If Me.Check1.Value = 1 Then MsgBox "Checked"
If Me.Check1.Value = 2 Then MsgBox "Grayed"

[isayhello]

Hi Angus...
Strangely it is not working....

if me.check.value = 0 then
..............................................
elseif me.check.value = 1 then
....................................
end if

it is going directly to end of sub program without recognizing the checkbox event!!!

[anguswalker]

Sorry- I have to leave just now for a few days- my mother is very ill. I suggest you post a new thread with the checkbox question.

[isayhello]

Thanks a lot for you help so far... Angus...
My best wishes....