DATE COLUMN

[gencom99]

I am connecting to Oracle through MS QUERY. The result is put into excel.

Set Query = QuerySheet.QueryTables("MONEY")
Query.Sql = "SELECT NUMB_TBLE.NUMB_NAME, TO_CHAR(NUMB_DATES_TBLE.DAY_MTH_YR, 'MONTH'), SUM(NUMB_TBLE.NUMB_PAY) " _
& "FROM JES.NUMB TBLE NUMB TBLE,
JES.NUMB_DATES_TBLE NUMB_DATES_TBLE" _
& "WHERE NUMB_TBLE.NUMB_ID = NUMB_DATES_TBLE.NUMB_ID AND NUMB_TBLE.NUMB_CODE IN (" & NumList & ") " _
& "Group by numb_tble.numb_name, TO_CHAR(NUMB_DATES_TBLE.DAY_MTH_YR, 'MONTH')
ORDER BY TO_CHAR(NUMB_DATES_TBLE.DAY_MTH_YR, 'MONTH')

This doesn't accomplish what I want

In my query, I want to display the month in words
I want to order the month by number

For example the result would be

Jones January 10.00
Doe April 20.00

How do I do this?

 

[SkipVought]

ORDER BY TO_CHAR(NUMB_DATES_TBLE.DAY_MTH_YR, 'MM')

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gencom99]

It didn't work. Also the column has an alias

I tried

Set Query = QuerySheet.QueryTables("MONEY")
Query.Sql = "SELECT NUMB_TBLE.NUMB_NAME, TO_CHAR(NUMB_DATES_TBLE.DAY_MTH_YR, 'MONTH') AS MONTH, SUM(NUMB_TBLE.NUMB_PAY) " _
& "FROM JES.NUMB_TBLE NUMB_TBLE,
JES.NUMB_DATES_TBLE NUMB_DATES_TBLE" _
& "WHERE NUMB_TBLE.NUMB_ID = NUMB_DATES_TBLE.NUMB_ID AND NUMB_TBLE.NUMB_CODE IN (" & NumList & ") " _
& "Group by numb_tble.numb_name, TO_CHAR(NUMB_DATES_TBLE.DAY_MTH_YR, 'MONTH')
ORDER BY TO_CHAR(NUMB_DATES_TBLE.DAY_MTH_YR, 'MM')


I received run-time error '1004'. General ODBC Error

 

[SkipVought]

Group by numb_tble.numb_name, NUMB_DATES_TBLE.DAY_MTH_YR
ORDER BY TO_CHAR(NUMB_DATES_TBLE.DAY_MTH_YR, 'MM')

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gencom99]

It didn't work; It grouped the data by each date.

For example if there were 10 January records, the result contained 10 records.

The purpose of the query is to sum the data by month.

There should have been one record for January

 

[SkipVought]

Duh, of course!



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gencom99]

Is there any way I can accomplish the following?

I am connecting to Oracle through MS QUERY

Set Query = QuerySheet.QueryTables("MONEY")
Query.Sql = "SELECT NUMB_TBLE.NUMB_NAME, TO_CHAR(NUMB_DATES_TBLE.DAY_MTH_YR, 'MONTH') AS MONTH, SUM(NUMB_TBLE.NUMB_PAY) " _
& "FROM JES.NUMB_TBLE NUMB_TBLE,
JES.NUMB_DATES_TBLE NUMB_DATES_TBLE" _
& "WHERE NUMB_TBLE.NUMB_ID = NUMB_DATES_TBLE.NUMB_ID AND NUMB_TBLE.NUMB_CODE IN (" & NumList & ") " _
& "Group by numb_tble.numb_name, TO_CHAR(NUMB_DATES_TBLE.DAY_MTH_YR, 'MONTH')
ORDER BY TO_CHAR(NUMB_DATES_TBLE.DAY_MTH_YR, 'MONTH')


In my query, I want to display the month in words
I want to order the month by number

For example the result would be

Jones January 10.00
Doe April 20.00

How do I do this?