I no longer needed my microsoft query analyzer program. In removing it I may
have also removed the microsoft query that allows my Excel 2007 to query my
SQL Database.

How to I reinstall the microsoft query tool in Excel?

Jenn,

I don't think you have removed the Query since this query feature is
part of the office suite and not part of the third party tools (Analyser,
etc...), to ensure you have it, with your workbook excel opened, try
motivating to Data menu Import External Data New Database Query MS
Access Database then locate your database or external source...

Hope this helps,
Adnan



"jenn" wrote:

I no longer needed my microsoft query analyzer program. In removing it I may
have also removed the microsoft query that allows my Excel 2007 to query my
SQL Database.

How to I reinstall the microsoft query tool in Excel?

When I do this is tells me all my tables are empty... yet, I can use the
other option of 'import data from unlisted source'.. then select ODBC and my
tables are there.

"Adnan" wrote:

Jenn,

I don't think you have removed the Query since this query feature is
part of the office suite and not part of the third party tools (Analyser,
etc...), to ensure you have it, with your workbook excel opened, try
motivating to Data menu Import External Data New Database Query MS
Access Database then locate your database or external source...

Hope this helps,
Adnan



"jenn" wrote:

I no longer needed my microsoft query analyzer program. In removing it I may
have also removed the microsoft query that allows my Excel 2007 to query my
SQL Database.

How to I reinstall the microsoft query tool in Excel?

I'm sure this is too basic for a lot of you. Perhaps a bit silly.

I have a Excel file and I'd like to 1) to change the format of one column in
the Excel file. For example, to change 31 Jan 88 to 31/01/1998. (I've tried
to change this in Excel but I can't). 2) still in Excel, to compare the
content of two columns (A and C) and to fill a third column's (C) content
into a new column (D). For example, Column A is 31 Dec 2001. Although 31 Dec
2001 is a date, 31 Dec 2001 actually means the period from 01 Dec 2001 to 31
Dec 2001. Both dates, 01 Dec 2001 and 31 Dec 2001, are included. Column B has
the amount of debt in sterling pounds (3.1124) in corresponding to column B.
Column C shows the issuing dates of the debt (31 Dec 2001). Because 31 Dec
2001 is within the period of 01 Dec 2001 and 31 Dec 2001 in column A, the
content of column B (3.1124) is put into column D.

Do you think that I can achieve above by writing a small program in the SQL
window within Microsoft Query? Or I have to have the software Microsoft SQL
Server 2000 :Developer Edition and a server to do this?

If I can do this in Microsoft Query, how can I link my Excel file (say
named: handling) to Microsoft Query in order to write SQL, please?

Thank you so much!


"jenn" wrote:

When I do this is tells me all my tables are empty... yet, I can use the
other option of 'import data from unlisted source'.. then select ODBC and my
tables are there.

"Adnan" wrote:

Jenn,

I don't think you have removed the Query since this query feature is
part of the office suite and not part of the third party tools (Analyser,
etc...), to ensure you have it, with your workbook excel opened, try
motivating to Data menu Import External Data New Database Query MS
Access Database then locate your database or external source...

Hope this helps,
Adnan



"jenn" wrote:

I no longer needed my microsoft query analyzer program. In removing it I may
have also removed the microsoft query that allows my Excel 2007 to query my
SQL Database.

How to I reinstall the microsoft query tool in Excel?

you've hijacked another question her ehaven't you?

if you excel sheet is populated from a datatase, then its likely that the
date you see 31 Jan 88 is actually text. so you need to convert this to a
"proper" date using =DATEVALUE() function. you can then format the cell to
show dd/mm/yy or whatever.
you can drop this into columns D and F, pointimng at A and B to convert both
columns.

I'm not sure why you'd want to do this in SQL, unless that's what you did to
generate the data in the first place?


"Greenwind" wrote in message
...
I'm sure this is too basic for a lot of you. Perhaps a bit silly.

I have a Excel file and I'd like to 1) to change the format of one column
in
the Excel file. For example, to change 31 Jan 88 to 31/01/1998. (I've
tried
to change this in Excel but I can't). 2) still in Excel, to compare the
content of two columns (A and C) and to fill a third column's (C) content
into a new column (D). For example, Column A is 31 Dec 2001. Although 31
Dec
2001 is a date, 31 Dec 2001 actually means the period from 01 Dec 2001 to
31
Dec 2001. Both dates, 01 Dec 2001 and 31 Dec 2001, are included. Column B
has
the amount of debt in sterling pounds (3.1124) in corresponding to column
B.
Column C shows the issuing dates of the debt (31 Dec 2001). Because 31 Dec
2001 is within the period of 01 Dec 2001 and 31 Dec 2001 in column A, the
content of column B (3.1124) is put into column D.

Do you think that I can achieve above by writing a small program in the
SQL
window within Microsoft Query? Or I have to have the software Microsoft
SQL
Server 2000 :Developer Edition and a server to do this?

If I can do this in Microsoft Query, how can I link my Excel file (say
named: handling) to Microsoft Query in order to write SQL, please?

Thank you so much!


"jenn" wrote:

When I do this is tells me all my tables are empty... yet, I can use the
other option of 'import data from unlisted source'.. then select ODBC
and my
tables are there.

"Adnan" wrote:

Jenn,

I don't think you have removed the Query since this query feature
is
part of the office suite and not part of the third party tools
(Analyser,
etc...), to ensure you have it, with your workbook excel opened, try
motivating to Data menu Import External Data New Database Query
MS
Access Database then locate your database or external source...

Hope this helps,
Adnan



"jenn" wrote:

I no longer needed my microsoft query analyzer program. In removing
it I may
have also removed the microsoft query that allows my Excel 2007 to
query my
SQL Database.

How to I reinstall the microsoft query tool in Excel?

Hi Patrick,

Thanks very much for the reply. Sorry. Somehow I cannot post any new
question (starting a thread) here.

I have to do programming because my data is a bit complicated. For
simplicity reasons, I only gave a short example in my last email. My data is
in an Excel file named handling. Id like to compare Column A and D then fill
column F.

My data: There are 3 groups in my data. Group 1: Column A and Column B.
Group 2: Column C and D. Group 3: Column E and F. Column A has monthly data
from Jan 1988 to Jan 2006. Notice although 31 Jan 88 is a date, it means a
period from Jan 01, 1988 to Jan 31, 1988. Both dates, Jan 01, 1988 and Jan
31, 1988 are included. Column B is the debt of the corresponding month. e.g.
From Jan 01, 1988 to Jan 31, 1988, the debt is 2.3343. Column C and D are the
companies names and debt issuing dates respectively. In column C, there are
848 different firms. However, the starting and end dates are different in
these two columns. That is, according to column D, company A0004 does not
have issuing dates during 1988-2000. The issuing dates for A0004 only exist
during 2001 and 2005. There is another example: A0005s issuing dates are
from 1989 to 2005. Column E has 848 firms although firms names are not
listed in column E. Each firm has a fixed format which starts with 1988 and
finishes with 2005 in column E.

Column A Column B Column C Column D Column E Column F
Time Debt Company Issuing date Year Arranged Debt
31 Jan 88 2.3343 A0004 31/12/2001 1988 3.0045
28 Feb 88 3.5674 A0004 30/04/2002 1989 3.0482
31 March 4.5711 A0004 30/09/2003 €¦
€¦ €¦ A0004 01/04/2004 €¦
31 Dec 88 3.0045 A0004 30/09/2005 €¦
€¦ €¦ A005 31/12/1989 €¦
31 Dec 89 3.0482 A005 31/12/1990 €¦
31 Dec 01 3.1124 €¦ €¦ €¦
€¦ €¦ €¦ €¦ €¦
31 Dec 05 4.5711 €¦ €¦ 2001 3.1124
€¦ €¦ €¦ €¦ €¦
€¦ €¦ €¦ €¦ 2005
€¦ €¦ €¦ €¦ 1988
€¦ €¦ €¦ €¦ €¦
.... ... A005 31/12/2005 2001
€¦ €¦ €¦

€¦ €¦ 2005
€¦ €¦ €¦
In order to compare Column A and D, I have to change them to the same
format. Although you kindly suggested the function, Im afraid that I still
need a program, I guess. Because 31/12/2001 corresponds to 31 Dec 01 in
column A, 3.1124 should be placed after the 2001 in column E for firm A0004.
As for the 1st and the 2nd cell in column F, the year-end-values in column B
are used. Based on column D, there is no issuing date for A0004 during
1988-2000. Therefore, we assume the issuing date for A0004 in 1998 is the
last date of 1988, i.e. 31 Dec 88. Hence 3.0045 is put in the 1st cell in
column F. Similarly, 3.0482 is placed after 1989 in column E.

Now I know how to link my Excel file (say named: handling) to Microsoft
Query in order to write SQL. I also know programming in the SQL window within
Microsoft Query is possible to solve my problem. The remaining problem is
how, I think.

I tried the first step: UPDATE Time
SET Time = 31/01/1988
WHERE Time = 31 Jan 88
The error message is: Syntax error in €œUPDATE Time€�.

Thank you so much!


"Patrick Molloy" wrote:

you've hijacked another question her ehaven't you?

if you excel sheet is populated from a datatase, then its likely that the
date you see 31 Jan 88 is actually text. so you need to convert this to a
"proper" date using =DATEVALUE() function. you can then format the cell to
show dd/mm/yy or whatever.
you can drop this into columns D and F, pointimng at A and B to convert both
columns.

I'm not sure why you'd want to do this in SQL, unless that's what you did to
generate the data in the first place?


"Greenwind" wrote in message
...
I'm sure this is too basic for a lot of you. Perhaps a bit silly.

I have a Excel file and I'd like to 1) to change the format of one column
in
the Excel file. For example, to change 31 Jan 88 to 31/01/1998. (I've
tried
to change this in Excel but I can't). 2) still in Excel, to compare the
content of two columns (A and C) and to fill a third column's (C) content
into a new column (D). For example, Column A is 31 Dec 2001. Although 31
Dec
2001 is a date, 31 Dec 2001 actually means the period from 01 Dec 2001 to
31
Dec 2001. Both dates, 01 Dec 2001 and 31 Dec 2001, are included. Column B
has
the amount of debt in sterling pounds (3.1124) in corresponding to column
B.
Column C shows the issuing dates of the debt (31 Dec 2001). Because 31 Dec
2001 is within the period of 01 Dec 2001 and 31 Dec 2001 in column A, the
content of column B (3.1124) is put into column D.

Do you think that I can achieve above by writing a small program in the
SQL
window within Microsoft Query? Or I have to have the software Microsoft
SQL
Server 2000 :Developer Edition and a server to do this?

If I can do this in Microsoft Query, how can I link my Excel file (say
named: handling) to Microsoft Query in order to write SQL, please?

Thank you so much!


"jenn" wrote:

When I do this is tells me all my tables are empty... yet, I can use the
other option of 'import data from unlisted source'.. then select ODBC
and my
tables are there.

"Adnan" wrote:

Jenn,

I don't think you have removed the Query since this query feature
is
part of the office suite and not part of the third party tools
(Analyser,
etc...), to ensure you have it, with your workbook excel opened, try
motivating to Data menu Import External Data New Database Query
MS
Access Database then locate your database or external source...

Hope this helps,
Adnan



"jenn" wrote:

I no longer needed my microsoft query analyzer program. In removing
it I may
have also removed the microsoft query that allows my Excel 2007 to
query my
SQL Database.

How to I reinstall the microsoft query tool in Excel?

Hi Patrick,

Thanks very much for the reply. Sorry. Somehow I cannot post any new
question (starting a thread) here.

I have to do programming because my data is a bit complicated. For
simplicity reasons, I only gave a short example in my last email. My data is
in an Excel file named handling. Id like to compare Column A and D then fill
column F.

My data: There are 3 groups in my data. Group 1: Column A and Column B.
Group 2: Column C and D. Group 3: Column E and F. Column A has monthly data
from Jan 1988 to Jan 2006. Notice although 31 Jan 88 is a date, it means a
period from Jan 01, 1988 to Jan 31, 1988. Both dates, Jan 01, 1988 and Jan
31, 1988 are included. Column B is the debt of the corresponding month. e.g.
From Jan 01, 1988 to Jan 31, 1988, the debt is 2.3343. Column C and D are the
companies names and debt issuing dates respectively. In column C, there are
848 different firms. However, the starting and end dates are different in
these two columns. That is, according to column D, company A0004 does not
have issuing dates during 1988-2000. The issuing dates for A0004 only exist
during 2001 and 2005. There is another example: A0005s issuing dates are
from 1989 to 2005. Column E has 848 firms although firms names are not
listed in column E. Each firm has a fixed format which starts with 1988 and
finishes with 2005 in column E.

Column A Column B Column C Column D Column E Column F
Time Debt Company Issuing date Year Arranged Debt
31 Jan 88 2.3343 A0004 31/12/2001 1988 3.0045
28 Feb 88 3.5674 A0004 30/04/2002 1989 3.0482
31 March 4.5711 A0004 30/09/2003 €¦
€¦ €¦ A0004 01/04/2004 €¦
31 Dec 88 3.0045 A0004 30/09/2005 €¦
€¦ €¦ A005 31/12/1989 €¦
31 Dec 89 3.0482 A005 31/12/1990 €¦
31 Dec 01 3.1124 €¦ €¦ €¦
€¦ €¦ €¦ €¦ €¦
31 Dec 05 4.5711 €¦ €¦ 2001 3.1124
€¦ €¦ €¦ €¦ €¦
€¦ €¦ €¦ €¦ 2005
€¦ €¦ €¦ €¦ 1988
€¦ €¦ €¦ €¦ €¦
.... ... A005 31/12/2005 2001
€¦ €¦ €¦

€¦ €¦ 2005
€¦ €¦ €¦
In order to compare Column A and D, I have to change them to the same
format. Although you kindly suggested the function, Im afraid that I still
need a program, I guess. Because 31/12/2001 corresponds to 31 Dec 01 in
column A, 3.1124 should be placed after the 2001 in column E for firm A0004.
As for the 1st and the 2nd cell in column F, the year-end-values in column B
are used. Based on column D, there is no issuing date for A0004 during
1988-2000. Therefore, we assume the issuing date for A0004 in 1998 is the
last date of 1988, i.e. 31 Dec 88. Hence 3.0045 is put in the 1st cell in
column F. Similarly, 3.0482 is placed after 1989 in column E.

Now I know how to link my Excel file (say named: handling) to Microsoft
Query in order to write SQL. I also know programming in the SQL window within
Microsoft Query is possible to solve my problem. The remaining problem is
how, I think.

I tried the first step: UPDATE Time
SET Time = 31/01/1988
WHERE Time = 31 Jan 88
The error message is: Syntax error in €œUPDATE Time€�.

Thank you so much!


"Patrick Molloy" wrote:

you've hijacked another question her ehaven't you?

if you excel sheet is populated from a datatase, then its likely that the
date you see 31 Jan 88 is actually text. so you need to convert this to a
"proper" date using =DATEVALUE() function. you can then format the cell to
show dd/mm/yy or whatever.
you can drop this into columns D and F, pointimng at A and B to convert both
columns.

I'm not sure why you'd want to do this in SQL, unless that's what you did to
generate the data in the first place?


"Greenwind" wrote in message
...
I'm sure this is too basic for a lot of you. Perhaps a bit silly.

I have a Excel file and I'd like to 1) to change the format of one column
in
the Excel file. For example, to change 31 Jan 88 to 31/01/1998. (I've
tried
to change this in Excel but I can't). 2) still in Excel, to compare the
content of two columns (A and C) and to fill a third column's (C) content
into a new column (D). For example, Column A is 31 Dec 2001. Although 31
Dec
2001 is a date, 31 Dec 2001 actually means the period from 01 Dec 2001 to
31
Dec 2001. Both dates, 01 Dec 2001 and 31 Dec 2001, are included. Column B
has
the amount of debt in sterling pounds (3.1124) in corresponding to column
B.
Column C shows the issuing dates of the debt (31 Dec 2001). Because 31 Dec
2001 is within the period of 01 Dec 2001 and 31 Dec 2001 in column A, the
content of column B (3.1124) is put into column D.

Do you think that I can achieve above by writing a small program in the
SQL
window within Microsoft Query? Or I have to have the software Microsoft
SQL
Server 2000 :Developer Edition and a server to do this?

If I can do this in Microsoft Query, how can I link my Excel file (say
named: handling) to Microsoft Query in order to write SQL, please?

Thank you so much!


"jenn" wrote:

When I do this is tells me all my tables are empty... yet, I can use the
other option of 'import data from unlisted source'.. then select ODBC
and my
tables are there.

"Adnan" wrote:

Jenn,

I don't think you have removed the Query since this query feature
is
part of the office suite and not part of the third party tools
(Analyser,
etc...), to ensure you have it, with your workbook excel opened, try
motivating to Data menu Import External Data New Database Query
MS
Access Database then locate your database or external source...

Hope this helps,
Adnan



"jenn" wrote:

I no longer needed my microsoft query analyzer program. In removing
it I may
have also removed the microsoft query that allows my Excel 2007 to
query my
SQL Database.

How to I reinstall the microsoft query tool in Excel?

so basically the "dates" in A and D should only be month/year as the day
number is irrelevent?

this my work....change "A" to "D" to repeat for the 2nd set of dates ..or
code it

rw=1
do until cells(rw,"A").value=""
cells(rw,"A").Value = format$(cells(rw,"A").Value,"MMMYY")
rw=rw+1
loop



"Greenwind" wrote in message
...
Hi Patrick,

Thanks very much for the reply. Sorry. Somehow I cannot post any new
question (starting a thread) here.

I have to do programming because my data is a bit complicated. For
simplicity reasons, I only gave a short example in my last email. My data
is
in an Excel file named handling. Id like to compare Column A and D then
fill
column F.

My data: There are 3 groups in my data. Group 1: Column A and Column B.
Group 2: Column C and D. Group 3: Column E and F. Column A has monthly
data
from Jan 1988 to Jan 2006. Notice although 31 Jan 88 is a date, it means
a
period from Jan 01, 1988 to Jan 31, 1988. Both dates, Jan 01, 1988 and Jan
31, 1988 are included. Column B is the debt of the corresponding month.
e.g.
From Jan 01, 1988 to Jan 31, 1988, the debt is 2.3343. Column C and D are
the
companies names and debt issuing dates respectively. In column C, there
are
848 different firms. However, the starting and end dates are different in
these two columns. That is, according to column D, company A0004 does not
have issuing dates during 1988-2000. The issuing dates for A0004 only
exist
during 2001 and 2005. There is another example: A0005s issuing dates are
from 1989 to 2005. Column E has 848 firms although firms names are not
listed in column E. Each firm has a fixed format which starts with 1988
and
finishes with 2005 in column E.

Column A Column B Column C Column D Column E Column F
Time Debt Company Issuing date Year Arranged Debt
31 Jan 88 2.3343 A0004 31/12/2001 1988 3.0045
28 Feb 88 3.5674 A0004 30/04/2002 1989 3.0482
31 March 4.5711 A0004 30/09/2003 €¦
€¦ €¦ A0004 01/04/2004 €¦
31 Dec 88 3.0045 A0004 30/09/2005 €¦
€¦ €¦ A005 31/12/1989 €¦
31 Dec 89 3.0482 A005 31/12/1990 €¦
31 Dec 01 3.1124 €¦ €¦ €¦
€¦ €¦ €¦ €¦ €¦
31 Dec 05 4.5711 €¦ €¦ 2001 3.1124
€¦ €¦ €¦ €¦ €¦
€¦ €¦ €¦ €¦ 2005
€¦ €¦ €¦ €¦ 1988
€¦ €¦ €¦ €¦ €¦
... ... A005 31/12/2005 2001
€¦ €¦ €¦

€¦ €¦ 2005
€¦ €¦ €¦
In order to compare Column A and D, I have to change them to the same
format. Although you kindly suggested the function, Im afraid that I
still
need a program, I guess. Because 31/12/2001 corresponds to 31 Dec 01 in
column A, 3.1124 should be placed after the 2001 in column E for firm
A0004.
As for the 1st and the 2nd cell in column F, the year-end-values in column
B
are used. Based on column D, there is no issuing date for A0004 during
1988-2000. Therefore, we assume the issuing date for A0004 in 1998 is the
last date of 1988, i.e. 31 Dec 88. Hence 3.0045 is put in the 1st cell in
column F. Similarly, 3.0482 is placed after 1989 in column E.

Now I know how to link my Excel file (say named: handling) to Microsoft
Query in order to write SQL. I also know programming in the SQL window
within
Microsoft Query is possible to solve my problem. The remaining problem is
how, I think.

I tried the first step: UPDATE Time
SET Time = 31/01/1988
WHERE Time = 31 Jan 88
The error message is: Syntax error in €œUPDATE Time€�.

Thank you so much!


"Patrick Molloy" wrote:

you've hijacked another question her ehaven't you?

if you excel sheet is populated from a datatase, then its likely that the
date you see 31 Jan 88 is actually text. so you need to convert this to a
"proper" date using =DATEVALUE() function. you can then format the cell
to
show dd/mm/yy or whatever.
you can drop this into columns D and F, pointimng at A and B to convert
both
columns.

I'm not sure why you'd want to do this in SQL, unless that's what you did
to
generate the data in the first place?


"Greenwind" wrote in message
...
I'm sure this is too basic for a lot of you. Perhaps a bit silly.

I have a Excel file and I'd like to 1) to change the format of one
column
in
the Excel file. For example, to change 31 Jan 88 to 31/01/1998. (I've
tried
to change this in Excel but I can't). 2) still in Excel, to compare the
content of two columns (A and C) and to fill a third column's (C)
content
into a new column (D). For example, Column A is 31 Dec 2001. Although
31
Dec
2001 is a date, 31 Dec 2001 actually means the period from 01 Dec 2001
to
31
Dec 2001. Both dates, 01 Dec 2001 and 31 Dec 2001, are included. Column
B
has
the amount of debt in sterling pounds (3.1124) in corresponding to
column
B.
Column C shows the issuing dates of the debt (31 Dec 2001). Because 31
Dec
2001 is within the period of 01 Dec 2001 and 31 Dec 2001 in column A,
the
content of column B (3.1124) is put into column D.

Do you think that I can achieve above by writing a small program in the
SQL
window within Microsoft Query? Or I have to have the software Microsoft
SQL
Server 2000 :Developer Edition and a server to do this?

If I can do this in Microsoft Query, how can I link my Excel file (say
named: handling) to Microsoft Query in order to write SQL, please?

Thank you so much!


"jenn" wrote:

When I do this is tells me all my tables are empty... yet, I can use
the
other option of 'import data from unlisted source'.. then select ODBC
and my
tables are there.

"Adnan" wrote:

Jenn,

I don't think you have removed the Query since this query
feature
is
part of the office suite and not part of the third party tools
(Analyser,
etc...), to ensure you have it, with your workbook excel opened, try
motivating to Data menu Import External Data New Database Query

MS
Access Database then locate your database or external source...

Hope this helps,
Adnan



"jenn" wrote:

I no longer needed my microsoft query analyzer program. In
removing
it I may
have also removed the microsoft query that allows my Excel 2007 to
query my
SQL Database.

How to I reinstall the microsoft query tool in Excel?

so basically the "dates" in A and D should only be month/year as the day
number is irrelevent?

this my work....change "A" to "D" to repeat for the 2nd set of dates ..or
code it

rw=1
do until cells(rw,"A").value=""
cells(rw,"A").Value = format$(cells(rw,"A").Value,"MMMYY")
rw=rw+1
loop



"Greenwind" wrote in message
...
Hi Patrick,

Thanks very much for the reply. Sorry. Somehow I cannot post any new
question (starting a thread) here.

I have to do programming because my data is a bit complicated. For
simplicity reasons, I only gave a short example in my last email. My data
is
in an Excel file named handling. Id like to compare Column A and D then
fill
column F.

My data: There are 3 groups in my data. Group 1: Column A and Column B.
Group 2: Column C and D. Group 3: Column E and F. Column A has monthly
data
from Jan 1988 to Jan 2006. Notice although 31 Jan 88 is a date, it means
a
period from Jan 01, 1988 to Jan 31, 1988. Both dates, Jan 01, 1988 and Jan
31, 1988 are included. Column B is the debt of the corresponding month.
e.g.
From Jan 01, 1988 to Jan 31, 1988, the debt is 2.3343. Column C and D are
the
companies names and debt issuing dates respectively. In column C, there
are
848 different firms. However, the starting and end dates are different in
these two columns. That is, according to column D, company A0004 does not
have issuing dates during 1988-2000. The issuing dates for A0004 only
exist
during 2001 and 2005. There is another example: A0005s issuing dates are
from 1989 to 2005. Column E has 848 firms although firms names are not
listed in column E. Each firm has a fixed format which starts with 1988
and
finishes with 2005 in column E.

Column A Column B Column C Column D Column E Column F
Time Debt Company Issuing date Year Arranged Debt
31 Jan 88 2.3343 A0004 31/12/2001 1988 3.0045
28 Feb 88 3.5674 A0004 30/04/2002 1989 3.0482
31 March 4.5711 A0004 30/09/2003 €¦
€¦ €¦ A0004 01/04/2004 €¦
31 Dec 88 3.0045 A0004 30/09/2005 €¦
€¦ €¦ A005 31/12/1989 €¦
31 Dec 89 3.0482 A005 31/12/1990 €¦
31 Dec 01 3.1124 €¦ €¦ €¦
€¦ €¦ €¦ €¦ €¦
31 Dec 05 4.5711 €¦ €¦ 2001 3.1124
€¦ €¦ €¦ €¦ €¦
€¦ €¦ €¦ €¦ 2005
€¦ €¦ €¦ €¦ 1988
€¦ €¦ €¦ €¦ €¦
... ... A005 31/12/2005 2001
€¦ €¦ €¦

€¦ €¦ 2005
€¦ €¦ €¦
In order to compare Column A and D, I have to change them to the same
format. Although you kindly suggested the function, Im afraid that I
still
need a program, I guess. Because 31/12/2001 corresponds to 31 Dec 01 in
column A, 3.1124 should be placed after the 2001 in column E for firm
A0004.
As for the 1st and the 2nd cell in column F, the year-end-values in column
B
are used. Based on column D, there is no issuing date for A0004 during
1988-2000. Therefore, we assume the issuing date for A0004 in 1998 is the
last date of 1988, i.e. 31 Dec 88. Hence 3.0045 is put in the 1st cell in
column F. Similarly, 3.0482 is placed after 1989 in column E.

Now I know how to link my Excel file (say named: handling) to Microsoft
Query in order to write SQL. I also know programming in the SQL window
within
Microsoft Query is possible to solve my problem. The remaining problem is
how, I think.

I tried the first step: UPDATE Time
SET Time = 31/01/1988
WHERE Time = 31 Jan 88
The error message is: Syntax error in €œUPDATE Time€�.

Thank you so much!


"Patrick Molloy" wrote:

you've hijacked another question her ehaven't you?

if you excel sheet is populated from a datatase, then its likely that the
date you see 31 Jan 88 is actually text. so you need to convert this to a
"proper" date using =DATEVALUE() function. you can then format the cell
to
show dd/mm/yy or whatever.
you can drop this into columns D and F, pointimng at A and B to convert
both
columns.

I'm not sure why you'd want to do this in SQL, unless that's what you did
to
generate the data in the first place?


"Greenwind" wrote in message
...
I'm sure this is too basic for a lot of you. Perhaps a bit silly.

I have a Excel file and I'd like to 1) to change the format of one
column
in
the Excel file. For example, to change 31 Jan 88 to 31/01/1998. (I've
tried
to change this in Excel but I can't). 2) still in Excel, to compare the
content of two columns (A and C) and to fill a third column's (C)
content
into a new column (D). For example, Column A is 31 Dec 2001. Although
31
Dec
2001 is a date, 31 Dec 2001 actually means the period from 01 Dec 2001
to
31
Dec 2001. Both dates, 01 Dec 2001 and 31 Dec 2001, are included. Column
B
has
the amount of debt in sterling pounds (3.1124) in corresponding to
column
B.
Column C shows the issuing dates of the debt (31 Dec 2001). Because 31
Dec
2001 is within the period of 01 Dec 2001 and 31 Dec 2001 in column A,
the
content of column B (3.1124) is put into column D.

Do you think that I can achieve above by writing a small program in the
SQL
window within Microsoft Query? Or I have to have the software Microsoft
SQL
Server 2000 :Developer Edition and a server to do this?

If I can do this in Microsoft Query, how can I link my Excel file (say
named: handling) to Microsoft Query in order to write SQL, please?

Thank you so much!


"jenn" wrote:

When I do this is tells me all my tables are empty... yet, I can use
the
other option of 'import data from unlisted source'.. then select ODBC
and my
tables are there.

"Adnan" wrote:

Jenn,

I don't think you have removed the Query since this query
feature
is
part of the office suite and not part of the third party tools
(Analyser,
etc...), to ensure you have it, with your workbook excel opened, try
motivating to Data menu Import External Data New Database Query

MS
Access Database then locate your database or external source...

Hope this helps,
Adnan



"jenn" wrote:

I no longer needed my microsoft query analyzer program. In
removing
it I may
have also removed the microsoft query that allows my Excel 2007 to
query my
SQL Database.

How to I reinstall the microsoft query tool in Excel?

Hi Parick,

You're right. The dates doesn't matter. I'll give it a try and let you know
how do I get on.

By the way, could you take my last post and give it a new name as, say,
"SQL in Microsoft Query", please? I still cannot begin a new thread after I
emailed Microsoft support.

Thanks a lot!
"Patrick Molloy" wrote:

so basically the "dates" in A and D should only be month/year as the day
number is irrelevent?

this my work....change "A" to "D" to repeat for the 2nd set of dates ..or
code it

rw=1
do until cells(rw,"A").value=""
cells(rw,"A").Value = format$(cells(rw,"A").Value,"MMMYY")
rw=rw+1
loop



"Greenwind" wrote in message
...
Hi Patrick,

Thanks very much for the reply. Sorry. Somehow I cannot post any new
question (starting a thread) here.

I have to do programming because my data is a bit complicated. For
simplicity reasons, I only gave a short example in my last email. My data
is
in an Excel file named handling. Id like to compare Column A and D then
fill
column F.

My data: There are 3 groups in my data. Group 1: Column A and Column B.
Group 2: Column C and D. Group 3: Column E and F. Column A has monthly
data
from Jan 1988 to Jan 2006. Notice although 31 Jan 88 is a date, it means
a
period from Jan 01, 1988 to Jan 31, 1988. Both dates, Jan 01, 1988 and Jan
31, 1988 are included. Column B is the debt of the corresponding month.
e.g.
From Jan 01, 1988 to Jan 31, 1988, the debt is 2.3343. Column C and D are
the
companies names and debt issuing dates respectively. In column C, there
are
848 different firms. However, the starting and end dates are different in
these two columns. That is, according to column D, company A0004 does not
have issuing dates during 1988-2000. The issuing dates for A0004 only
exist
during 2001 and 2005. There is another example: A0005s issuing dates are
from 1989 to 2005. Column E has 848 firms although firms names are not
listed in column E. Each firm has a fixed format which starts with 1988
and
finishes with 2005 in column E.

Column A Column B Column C Column D Column E Column F
Time Debt Company Issuing date Year Arranged Debt
31 Jan 88 2.3343 A0004 31/12/2001 1988 3.0045
28 Feb 88 3.5674 A0004 30/04/2002 1989 3.0482
31 March 4.5711 A0004 30/09/2003 €¦
€¦ €¦ A0004 01/04/2004 €¦
31 Dec 88 3.0045 A0004 30/09/2005 €¦
€¦ €¦ A005 31/12/1989 €¦
31 Dec 89 3.0482 A005 31/12/1990 €¦
31 Dec 01 3.1124 €¦ €¦ €¦
€¦ €¦ €¦ €¦ €¦
31 Dec 05 4.5711 €¦ €¦ 2001 3.1124
€¦ €¦ €¦ €¦ €¦
€¦ €¦ €¦ €¦ 2005
€¦ €¦ €¦ €¦ 1988
€¦ €¦ €¦ €¦ €¦
... ... A005 31/12/2005 2001
€¦ €¦ €¦

€¦ €¦ 2005
€¦ €¦ €¦
In order to compare Column A and D, I have to change them to the same
format. Although you kindly suggested the function, Im afraid that I
still
need a program, I guess. Because 31/12/2001 corresponds to 31 Dec 01 in
column A, 3.1124 should be placed after the 2001 in column E for firm
A0004.
As for the 1st and the 2nd cell in column F, the year-end-values in column
B
are used. Based on column D, there is no issuing date for A0004 during
1988-2000. Therefore, we assume the issuing date for A0004 in 1998 is the
last date of 1988, i.e. 31 Dec 88. Hence 3.0045 is put in the 1st cell in
column F. Similarly, 3.0482 is placed after 1989 in column E.

Now I know how to link my Excel file (say named: handling) to Microsoft
Query in order to write SQL. I also know programming in the SQL window
within
Microsoft Query is possible to solve my problem. The remaining problem is
how, I think.

I tried the first step: UPDATE Time
SET Time = 31/01/1988
WHERE Time = 31 Jan 88
The error message is: Syntax error in €œUPDATE Time€�.

Thank you so much!


"Patrick Molloy" wrote:

you've hijacked another question her ehaven't you?

if you excel sheet is populated from a datatase, then its likely that the
date you see 31 Jan 88 is actually text. so you need to convert this to a
"proper" date using =DATEVALUE() function. you can then format the cell
to
show dd/mm/yy or whatever.
you can drop this into columns D and F, pointimng at A and B to convert
both
columns.

I'm not sure why you'd want to do this in SQL, unless that's what you did
to
generate the data in the first place?


"Greenwind" wrote in message
...
I'm sure this is too basic for a lot of you. Perhaps a bit silly.

I have a Excel file and I'd like to 1) to change the format of one
column
in
the Excel file. For example, to change 31 Jan 88 to 31/01/1998. (I've
tried
to change this in Excel but I can't). 2) still in Excel, to compare the
content of two columns (A and C) and to fill a third column's (C)
content
into a new column (D). For example, Column A is 31 Dec 2001. Although
31
Dec
2001 is a date, 31 Dec 2001 actually means the period from 01 Dec 2001
to
31
Dec 2001. Both dates, 01 Dec 2001 and 31 Dec 2001, are included. Column
B
has
the amount of debt in sterling pounds (3.1124) in corresponding to
column
B.
Column C shows the issuing dates of the debt (31 Dec 2001). Because 31
Dec
2001 is within the period of 01 Dec 2001 and 31 Dec 2001 in column A,
the
content of column B (3.1124) is put into column D.

Do you think that I can achieve above by writing a small program in the
SQL
window within Microsoft Query? Or I have to have the software Microsoft
SQL
Server 2000 :Developer Edition and a server to do this?

If I can do this in Microsoft Query, how can I link my Excel file (say
named: handling) to Microsoft Query in order to write SQL, please?

Thank you so much!


"jenn" wrote:

When I do this is tells me all my tables are empty... yet, I can use
the
other option of 'import data from unlisted source'.. then select ODBC
and my
tables are there.

"Adnan" wrote:

Jenn,

I don't think you have removed the Query since this query
feature
is
part of the office suite and not part of the third party tools
(Analyser,
etc...), to ensure you have it, with your workbook excel opened, try
motivating to Data menu Import External Data New Database Query

MS
Access Database then locate your database or external source...

Hope this helps,
Adnan



"jenn" wrote:

I no longer needed my microsoft query analyzer program. In
removing
it I may
have also removed the microsoft query that allows my Excel 2007 to
query my
SQL Database.

How to I reinstall the microsoft query tool in Excel?

Hi Parick,

You're right. The dates doesn't matter. I'll give it a try and let you know
how do I get on.

By the way, could you take my last post and give it a new name as, say,
"SQL in Microsoft Query", please? I still cannot begin a new thread after I
emailed Microsoft support.

Thanks a lot!
"Patrick Molloy" wrote:

so basically the "dates" in A and D should only be month/year as the day
number is irrelevent?

this my work....change "A" to "D" to repeat for the 2nd set of dates ..or
code it

rw=1
do until cells(rw,"A").value=""
cells(rw,"A").Value = format$(cells(rw,"A").Value,"MMMYY")
rw=rw+1
loop



"Greenwind" wrote in message
...
Hi Patrick,

Thanks very much for the reply. Sorry. Somehow I cannot post any new
question (starting a thread) here.

I have to do programming because my data is a bit complicated. For
simplicity reasons, I only gave a short example in my last email. My data
is
in an Excel file named handling. Id like to compare Column A and D then
fill
column F.

My data: There are 3 groups in my data. Group 1: Column A and Column B.
Group 2: Column C and D. Group 3: Column E and F. Column A has monthly
data
from Jan 1988 to Jan 2006. Notice although 31 Jan 88 is a date, it means
a
period from Jan 01, 1988 to Jan 31, 1988. Both dates, Jan 01, 1988 and Jan
31, 1988 are included. Column B is the debt of the corresponding month.
e.g.
From Jan 01, 1988 to Jan 31, 1988, the debt is 2.3343. Column C and D are
the
companies names and debt issuing dates respectively. In column C, there
are
848 different firms. However, the starting and end dates are different in
these two columns. That is, according to column D, company A0004 does not
have issuing dates during 1988-2000. The issuing dates for A0004 only
exist
during 2001 and 2005. There is another example: A0005s issuing dates are
from 1989 to 2005. Column E has 848 firms although firms names are not
listed in column E. Each firm has a fixed format which starts with 1988
and
finishes with 2005 in column E.

Column A Column B Column C Column D Column E Column F
Time Debt Company Issuing date Year Arranged Debt
31 Jan 88 2.3343 A0004 31/12/2001 1988 3.0045
28 Feb 88 3.5674 A0004 30/04/2002 1989 3.0482
31 March 4.5711 A0004 30/09/2003 €¦
€¦ €¦ A0004 01/04/2004 €¦
31 Dec 88 3.0045 A0004 30/09/2005 €¦
€¦ €¦ A005 31/12/1989 €¦
31 Dec 89 3.0482 A005 31/12/1990 €¦
31 Dec 01 3.1124 €¦ €¦ €¦
€¦ €¦ €¦ €¦ €¦
31 Dec 05 4.5711 €¦ €¦ 2001 3.1124
€¦ €¦ €¦ €¦ €¦
€¦ €¦ €¦ €¦ 2005
€¦ €¦ €¦ €¦ 1988
€¦ €¦ €¦ €¦ €¦
... ... A005 31/12/2005 2001
€¦ €¦ €¦

€¦ €¦ 2005
€¦ €¦ €¦
In order to compare Column A and D, I have to change them to the same
format. Although you kindly suggested the function, Im afraid that I
still
need a program, I guess. Because 31/12/2001 corresponds to 31 Dec 01 in
column A, 3.1124 should be placed after the 2001 in column E for firm
A0004.
As for the 1st and the 2nd cell in column F, the year-end-values in column
B
are used. Based on column D, there is no issuing date for A0004 during
1988-2000. Therefore, we assume the issuing date for A0004 in 1998 is the
last date of 1988, i.e. 31 Dec 88. Hence 3.0045 is put in the 1st cell in
column F. Similarly, 3.0482 is placed after 1989 in column E.

Now I know how to link my Excel file (say named: handling) to Microsoft
Query in order to write SQL. I also know programming in the SQL window
within
Microsoft Query is possible to solve my problem. The remaining problem is
how, I think.

I tried the first step: UPDATE Time
SET Time = 31/01/1988
WHERE Time = 31 Jan 88
The error message is: Syntax error in €œUPDATE Time€�.

Thank you so much!


"Patrick Molloy" wrote:

you've hijacked another question her ehaven't you?

if you excel sheet is populated from a datatase, then its likely that the
date you see 31 Jan 88 is actually text. so you need to convert this to a
"proper" date using =DATEVALUE() function. you can then format the cell
to
show dd/mm/yy or whatever.
you can drop this into columns D and F, pointimng at A and B to convert
both
columns.

I'm not sure why you'd want to do this in SQL, unless that's what you did
to
generate the data in the first place?


"Greenwind" wrote in message
...
I'm sure this is too basic for a lot of you. Perhaps a bit silly.

I have a Excel file and I'd like to 1) to change the format of one
column
in
the Excel file. For example, to change 31 Jan 88 to 31/01/1998. (I've
tried
to change this in Excel but I can't). 2) still in Excel, to compare the
content of two columns (A and C) and to fill a third column's (C)
content
into a new column (D). For example, Column A is 31 Dec 2001. Although
31
Dec
2001 is a date, 31 Dec 2001 actually means the period from 01 Dec 2001
to
31
Dec 2001. Both dates, 01 Dec 2001 and 31 Dec 2001, are included. Column
B
has
the amount of debt in sterling pounds (3.1124) in corresponding to
column
B.
Column C shows the issuing dates of the debt (31 Dec 2001). Because 31
Dec
2001 is within the period of 01 Dec 2001 and 31 Dec 2001 in column A,
the
content of column B (3.1124) is put into column D.

Do you think that I can achieve above by writing a small program in the
SQL
window within Microsoft Query? Or I have to have the software Microsoft
SQL
Server 2000 :Developer Edition and a server to do this?

If I can do this in Microsoft Query, how can I link my Excel file (say
named: handling) to Microsoft Query in order to write SQL, please?

Thank you so much!


"jenn" wrote:

When I do this is tells me all my tables are empty... yet, I can use
the
other option of 'import data from unlisted source'.. then select ODBC
and my
tables are there.

"Adnan" wrote:

Jenn,

I don't think you have removed the Query since this query
feature
is
part of the office suite and not part of the third party tools
(Analyser,
etc...), to ensure you have it, with your workbook excel opened, try
motivating to Data menu Import External Data New Database Query

MS
Access Database then locate your database or external source...

Hope this helps,
Adnan



"jenn" wrote:

I no longer needed my microsoft query analyzer program. In
removing
it I may
have also removed the microsoft query that allows my Excel 2007 to
query my
SQL Database.

How to I reinstall the microsoft query tool in Excel?

seems you did start a new thread ;)


"Greenwind" wrote in message
...
Hi Parick,

You're right. The dates doesn't matter. I'll give it a try and let you
know
how do I get on.

By the way, could you take my last post and give it a new name as, say,
"SQL in Microsoft Query", please? I still cannot begin a new thread after
I
emailed Microsoft support.

Thanks a lot!
"Patrick Molloy" wrote:

so basically the "dates" in A and D should only be month/year as the day
number is irrelevent?

this my work....change "A" to "D" to repeat for the 2nd set of dates ..or
code it

rw=1
do until cells(rw,"A").value=""
cells(rw,"A").Value = format$(cells(rw,"A").Value,"MMMYY")
rw=rw+1
loop



"Greenwind" wrote in message
...
Hi Patrick,

Thanks very much for the reply. Sorry. Somehow I cannot post any new
question (starting a thread) here.

I have to do programming because my data is a bit complicated. For
simplicity reasons, I only gave a short example in my last email. My
data
is
in an Excel file named handling. Id like to compare Column A and D
then
fill
column F.

My data: There are 3 groups in my data. Group 1: Column A and Column B.
Group 2: Column C and D. Group 3: Column E and F. Column A has monthly
data
from Jan 1988 to Jan 2006. Notice although 31 Jan 88 is a date, it
means
a
period from Jan 01, 1988 to Jan 31, 1988. Both dates, Jan 01, 1988 and
Jan
31, 1988 are included. Column B is the debt of the corresponding month.
e.g.
From Jan 01, 1988 to Jan 31, 1988, the debt is 2.3343. Column C and D
are
the
companies names and debt issuing dates respectively. In column C,
there
are
848 different firms. However, the starting and end dates are different
in
these two columns. That is, according to column D, company A0004 does
not
have issuing dates during 1988-2000. The issuing dates for A0004 only
exist
during 2001 and 2005. There is another example: A0005s issuing dates
are
from 1989 to 2005. Column E has 848 firms although firms names are not
listed in column E. Each firm has a fixed format which starts with 1988
and
finishes with 2005 in column E.

Column A Column B Column C Column D Column E Column F
Time Debt Company Issuing date Year Arranged Debt
31 Jan 88 2.3343 A0004 31/12/2001 1988 3.0045
28 Feb 88 3.5674 A0004 30/04/2002 1989 3.0482
31 March 4.5711 A0004 30/09/2003 €¦
€¦ €¦ A0004 01/04/2004 €¦
31 Dec 88 3.0045 A0004 30/09/2005 €¦
€¦ €¦ A005 31/12/1989 €¦
31 Dec 89 3.0482 A005 31/12/1990 €¦
31 Dec 01 3.1124 €¦ €¦ €¦
€¦ €¦ €¦ €¦ €¦
31 Dec 05 4.5711 €¦ €¦ 2001 3.1124
€¦ €¦ €¦ €¦ €¦
€¦ €¦ €¦ €¦ 2005
€¦ €¦ €¦ €¦ 1988
€¦ €¦ €¦ €¦ €¦
... ... A005 31/12/2005 2001
€¦ €¦ €¦

€¦ €¦ 2005
€¦ €¦ €¦
In order to compare Column A and D, I have to change them to the same
format. Although you kindly suggested the function, Im afraid that I
still
need a program, I guess. Because 31/12/2001 corresponds to 31 Dec 01
in
column A, 3.1124 should be placed after the 2001 in column E for firm
A0004.
As for the 1st and the 2nd cell in column F, the year-end-values in
column
B
are used. Based on column D, there is no issuing date for A0004 during
1988-2000. Therefore, we assume the issuing date for A0004 in 1998 is
the
last date of 1988, i.e. 31 Dec 88. Hence 3.0045 is put in the 1st cell
in
column F. Similarly, 3.0482 is placed after 1989 in column E.

Now I know how to link my Excel file (say named: handling) to Microsoft
Query in order to write SQL. I also know programming in the SQL window
within
Microsoft Query is possible to solve my problem. The remaining problem
is
how, I think.

I tried the first step: UPDATE Time
SET Time = 31/01/1988
WHERE Time = 31 Jan 88
The error message is: Syntax error in €œUPDATE Time€�.

Thank you so much!


"Patrick Molloy" wrote:

you've hijacked another question her ehaven't you?

if you excel sheet is populated from a datatase, then its likely that
the
date you see 31 Jan 88 is actually text. so you need to convert this
to a
"proper" date using =DATEVALUE() function. you can then format the
cell
to
show dd/mm/yy or whatever.
you can drop this into columns D and F, pointimng at A and B to
convert
both
columns.

I'm not sure why you'd want to do this in SQL, unless that's what you
did
to
generate the data in the first place?


"Greenwind" wrote in message
...
I'm sure this is too basic for a lot of you. Perhaps a bit silly.

I have a Excel file and I'd like to 1) to change the format of one
column
in
the Excel file. For example, to change 31 Jan 88 to 31/01/1998.
(I've
tried
to change this in Excel but I can't). 2) still in Excel, to compare
the
content of two columns (A and C) and to fill a third column's (C)
content
into a new column (D). For example, Column A is 31 Dec 2001.
Although
31
Dec
2001 is a date, 31 Dec 2001 actually means the period from 01 Dec
2001
to
31
Dec 2001. Both dates, 01 Dec 2001 and 31 Dec 2001, are included.
Column
B
has
the amount of debt in sterling pounds (3.1124) in corresponding to
column
B.
Column C shows the issuing dates of the debt (31 Dec 2001). Because
31
Dec
2001 is within the period of 01 Dec 2001 and 31 Dec 2001 in column
A,
the
content of column B (3.1124) is put into column D.

Do you think that I can achieve above by writing a small program in
the
SQL
window within Microsoft Query? Or I have to have the software
Microsoft
SQL
Server 2000 :Developer Edition and a server to do this?

If I can do this in Microsoft Query, how can I link my Excel file
(say
named: handling) to Microsoft Query in order to write SQL, please?

Thank you so much!


"jenn" wrote:

When I do this is tells me all my tables are empty... yet, I can
use
the
other option of 'import data from unlisted source'.. then select
ODBC
and my
tables are there.

"Adnan" wrote:

Jenn,

I don't think you have removed the Query since this query
feature
is
part of the office suite and not part of the third party tools
(Analyser,
etc...), to ensure you have it, with your workbook excel opened,
try
motivating to Data menu Import External Data New Database
Query

MS
Access Database then locate your database or external source...

Hope this helps,
Adnan



"jenn" wrote:

I no longer needed my microsoft query analyzer program. In
removing
it I may
have also removed the microsoft query that allows my Excel 2007
to
query my
SQL Database.

How to I reinstall the microsoft query tool in Excel?

seems you did start a new thread ;)


"Greenwind" wrote in message
...
Hi Parick,

You're right. The dates doesn't matter. I'll give it a try and let you
know
how do I get on.

By the way, could you take my last post and give it a new name as, say,
"SQL in Microsoft Query", please? I still cannot begin a new thread after
I
emailed Microsoft support.

Thanks a lot!
"Patrick Molloy" wrote:

so basically the "dates" in A and D should only be month/year as the day
number is irrelevent?

this my work....change "A" to "D" to repeat for the 2nd set of dates ..or
code it

rw=1
do until cells(rw,"A").value=""
cells(rw,"A").Value = format$(cells(rw,"A").Value,"MMMYY")
rw=rw+1
loop



"Greenwind" wrote in message
...
Hi Patrick,

Thanks very much for the reply. Sorry. Somehow I cannot post any new
question (starting a thread) here.

I have to do programming because my data is a bit complicated. For
simplicity reasons, I only gave a short example in my last email. My
data
is
in an Excel file named handling. Id like to compare Column A and D
then
fill
column F.

My data: There are 3 groups in my data. Group 1: Column A and Column B.
Group 2: Column C and D. Group 3: Column E and F. Column A has monthly
data
from Jan 1988 to Jan 2006. Notice although 31 Jan 88 is a date, it
means
a
period from Jan 01, 1988 to Jan 31, 1988. Both dates, Jan 01, 1988 and
Jan
31, 1988 are included. Column B is the debt of the corresponding month.
e.g.
From Jan 01, 1988 to Jan 31, 1988, the debt is 2.3343. Column C and D
are
the
companies names and debt issuing dates respectively. In column C,
there
are
848 different firms. However, the starting and end dates are different
in
these two columns. That is, according to column D, company A0004 does
not
have issuing dates during 1988-2000. The issuing dates for A0004 only
exist
during 2001 and 2005. There is another example: A0005s issuing dates
are
from 1989 to 2005. Column E has 848 firms although firms names are not
listed in column E. Each firm has a fixed format which starts with 1988
and
finishes with 2005 in column E.

Column A Column B Column C Column D Column E Column F
Time Debt Company Issuing date Year Arranged Debt
31 Jan 88 2.3343 A0004 31/12/2001 1988 3.0045
28 Feb 88 3.5674 A0004 30/04/2002 1989 3.0482
31 March 4.5711 A0004 30/09/2003 €¦
€¦ €¦ A0004 01/04/2004 €¦
31 Dec 88 3.0045 A0004 30/09/2005 €¦
€¦ €¦ A005 31/12/1989 €¦
31 Dec 89 3.0482 A005 31/12/1990 €¦
31 Dec 01 3.1124 €¦ €¦ €¦
€¦ €¦ €¦ €¦ €¦
31 Dec 05 4.5711 €¦ €¦ 2001 3.1124
€¦ €¦ €¦ €¦ €¦
€¦ €¦ €¦ €¦ 2005
€¦ €¦ €¦ €¦ 1988
€¦ €¦ €¦ €¦ €¦
... ... A005 31/12/2005 2001
€¦ €¦ €¦

€¦ €¦ 2005
€¦ €¦ €¦
In order to compare Column A and D, I have to change them to the same
format. Although you kindly suggested the function, Im afraid that I
still
need a program, I guess. Because 31/12/2001 corresponds to 31 Dec 01
in
column A, 3.1124 should be placed after the 2001 in column E for firm
A0004.
As for the 1st and the 2nd cell in column F, the year-end-values in
column
B
are used. Based on column D, there is no issuing date for A0004 during
1988-2000. Therefore, we assume the issuing date for A0004 in 1998 is
the
last date of 1988, i.e. 31 Dec 88. Hence 3.0045 is put in the 1st cell
in
column F. Similarly, 3.0482 is placed after 1989 in column E.

Now I know how to link my Excel file (say named: handling) to Microsoft
Query in order to write SQL. I also know programming in the SQL window
within
Microsoft Query is possible to solve my problem. The remaining problem
is
how, I think.

I tried the first step: UPDATE Time
SET Time = 31/01/1988
WHERE Time = 31 Jan 88
The error message is: Syntax error in €œUPDATE Time€�.

Thank you so much!


"Patrick Molloy" wrote:

you've hijacked another question her ehaven't you?

if you excel sheet is populated from a datatase, then its likely that
the
date you see 31 Jan 88 is actually text. so you need to convert this
to a
"proper" date using =DATEVALUE() function. you can then format the
cell
to
show dd/mm/yy or whatever.
you can drop this into columns D and F, pointimng at A and B to
convert
both
columns.

I'm not sure why you'd want to do this in SQL, unless that's what you
did
to
generate the data in the first place?


"Greenwind" wrote in message
...
I'm sure this is too basic for a lot of you. Perhaps a bit silly.

I have a Excel file and I'd like to 1) to change the format of one
column
in
the Excel file. For example, to change 31 Jan 88 to 31/01/1998.
(I've
tried
to change this in Excel but I can't). 2) still in Excel, to compare
the
content of two columns (A and C) and to fill a third column's (C)
content
into a new column (D). For example, Column A is 31 Dec 2001.
Although
31
Dec
2001 is a date, 31 Dec 2001 actually means the period from 01 Dec
2001
to
31
Dec 2001. Both dates, 01 Dec 2001 and 31 Dec 2001, are included.
Column
B
has
the amount of debt in sterling pounds (3.1124) in corresponding to
column
B.
Column C shows the issuing dates of the debt (31 Dec 2001). Because
31
Dec
2001 is within the period of 01 Dec 2001 and 31 Dec 2001 in column
A,
the
content of column B (3.1124) is put into column D.

Do you think that I can achieve above by writing a small program in
the
SQL
window within Microsoft Query? Or I have to have the software
Microsoft
SQL
Server 2000 :Developer Edition and a server to do this?

If I can do this in Microsoft Query, how can I link my Excel file
(say
named: handling) to Microsoft Query in order to write SQL, please?

Thank you so much!


"jenn" wrote:

When I do this is tells me all my tables are empty... yet, I can
use
the
other option of 'import data from unlisted source'.. then select
ODBC
and my
tables are there.

"Adnan" wrote:

Jenn,

I don't think you have removed the Query since this query
feature
is
part of the office suite and not part of the third party tools
(Analyser,
etc...), to ensure you have it, with your workbook excel opened,
try
motivating to Data menu Import External Data New Database
Query

MS
Access Database then locate your database or external source...

Hope this helps,
Adnan



"jenn" wrote:

I no longer needed my microsoft query analyzer program. In
removing
it I may
have also removed the microsoft query that allows my Excel 2007
to
query my
SQL Database.

How to I reinstall the microsoft query tool in Excel?

Hi Patrick,

It didn't work out but thank you very much anyway. rw cannot be recognized,
I guess. I'll try my luck in other posts. Thank you!
--
Tatiana


"Patrick Molloy" wrote:

seems you did start a new thread ;)


"Greenwind" wrote in message
...
Hi Parick,

You're right. The dates doesn't matter. I'll give it a try and let you
know how do I get on.

By the way, could you take my last post and give it a new name as, say,
"SQL in Microsoft Query", please? I still cannot begin a new thread after
I
emailed Microsoft support.

Thanks a lot!
"Patrick Molloy" wrote:

so basically the "dates" in A and D should only be month/year as the day
number is irrelevent?

this my work....change "A" to "D" to repeat for the 2nd set of dates ..or
code it

rw=1
do until cells(rw,"A").value=""
cells(rw,"A").Value = format$(cells(rw,"A").Value,"MMMYY")
rw=rw+1
loop



"Greenwind" wrote in message
...
Hi Patrick,

Thanks very much for the reply. Sorry. Somehow I cannot post any new
question (starting a thread) here.

I have to do programming because my data is a bit complicated. For
simplicity reasons, I only gave a short example in my last email. My
data
is
in an Excel file named handling. Id like to compare Column A and D
then
fill
column F.

My data: There are 3 groups in my data. Group 1: Column A and Column B.
Group 2: Column C and D. Group 3: Column E and F. Column A has monthly
data
from Jan 1988 to Jan 2006. Notice although 31 Jan 88 is a date, it
means
a
period from Jan 01, 1988 to Jan 31, 1988. Both dates, Jan 01, 1988 and
Jan
31, 1988 are included. Column B is the debt of the corresponding month.
e.g.
From Jan 01, 1988 to Jan 31, 1988, the debt is 2.3343. Column C and D
are
the
companies names and debt issuing dates respectively. In column C,
there
are
848 different firms. However, the starting and end dates are different
in
these two columns. That is, according to column D, company A0004 does
not
have issuing dates during 1988-2000. The issuing dates for A0004 only
exist
during 2001 and 2005. There is another example: A0005s issuing dates
are
from 1989 to 2005. Column E has 848 firms although firms names are not
listed in column E. Each firm has a fixed format which starts with 1988
and
finishes with 2005 in column E.

Column A Column B Column C Column D Column E Column F
Time Debt Company Issuing date Year Arranged Debt
31 Jan 88 2.3343 A0004 31/12/2001 1988 3.0045
28 Feb 88 3.5674 A0004 30/04/2002 1989 3.0482
31 March 4.5711 A0004 30/09/2003 €¦
€¦ €¦ A0004 01/04/2004 €¦
31 Dec 88 3.0045 A0004 30/09/2005 €¦
€¦ €¦ A005 31/12/1989 €¦
31 Dec 89 3.0482 A005 31/12/1990 €¦
31 Dec 01 3.1124 €¦ €¦ €¦
€¦ €¦ €¦ €¦ €¦
31 Dec 05 4.5711 €¦ €¦ 2001 3.1124
€¦ €¦ €¦ €¦ €¦
€¦ €¦ €¦ €¦ 2005
€¦ €¦ €¦ €¦ 1988
€¦ €¦ €¦ €¦ €¦
... ... A005 31/12/2005 2001
€¦ €¦ €¦

€¦ €¦ 2005
€¦ €¦ €¦
In order to compare Column A and D, I have to change them to the same
format. Although you kindly suggested the function, Im afraid that I
still
need a program, I guess. Because 31/12/2001 corresponds to 31 Dec 01
in
column A, 3.1124 should be placed after the 2001 in column E for firm
A0004.
As for the 1st and the 2nd cell in column F, the year-end-values in
column
B
are used. Based on column D, there is no issuing date for A0004 during
1988-2000. Therefore, we assume the issuing date for A0004 in 1998 is
the
last date of 1988, i.e. 31 Dec 88. Hence 3.0045 is put in the 1st cell
in
column F. Similarly, 3.0482 is placed after 1989 in column E.

Now I know how to link my Excel file (say named: handling) to Microsoft
Query in order to write SQL. I also know programming in the SQL window
within
Microsoft Query is possible to solve my problem. The remaining problem
is
how, I think.

I tried the first step: UPDATE Time
SET Time = 31/01/1988
WHERE Time = 31 Jan 88
The error message is: Syntax error in €œUPDATE Time€�.

Thank you so much!


"Patrick Molloy" wrote:

you've hijacked another question her ehaven't you?

if you excel sheet is populated from a datatase, then its likely that
the
date you see 31 Jan 88 is actually text. so you need to convert this
to a
"proper" date using =DATEVALUE() function. you can then format the
cell
to
show dd/mm/yy or whatever.
you can drop this into columns D and F, pointimng at A and B to
convert
both
columns.

I'm not sure why you'd want to do this in SQL, unless that's what you
did
to
generate the data in the first place?


"Greenwind" wrote in message
...
I'm sure this is too basic for a lot of you. Perhaps a bit silly.

I have a Excel file and I'd like to 1) to change the format of one
column
in
the Excel file. For example, to change 31 Jan 88 to 31/01/1998.
(I've
tried
to change this in Excel but I can't). 2) still in Excel, to compare
the
content of two columns (A and C) and to fill a third column's (C)
content
into a new column (D). For example, Column A is 31 Dec 2001.
Although
31
Dec
2001 is a date, 31 Dec 2001 actually means the period from 01 Dec
2001
to
31
Dec 2001. Both dates, 01 Dec 2001 and 31 Dec 2001, are included.
Column
B
has
the amount of debt in sterling pounds (3.1124) in corresponding to
column
B.
Column C shows the issuing dates of the debt (31 Dec 2001). Because
31
Dec
2001 is within the period of 01 Dec 2001 and 31 Dec 2001 in column
A,
the
content of column B (3.1124) is put into column D.

Do you think that I can achieve above by writing a small program in
the
SQL
window within Microsoft Query? Or I have to have the software
Microsoft
SQL
Server 2000 :Developer Edition and a server to do this?

If I can do this in Microsoft Query, how can I link my Excel file
(say
named: handling) to Microsoft Query in order to write SQL, please?

Thank you so much!


"jenn" wrote:

When I do this is tells me all my tables are empty... yet, I can
use
the
other option of 'import data from unlisted source'.. then select
ODBC
and my
tables are there.

"Adnan" wrote:

Jenn,

I don't think you have removed the Query since this query
feature
is
part of the office suite and not part of the third party tools
(Analyser,
etc...), to ensure you have it, with your workbook excel opened,
try
motivating to Data menu Import External Data New Database
Query

MS
Access Database then locate your database or external source...

Hope this helps,
Adnan



"jenn" wrote:

I no longer needed my microsoft query analyzer program. In
removing
it I may
have also removed the microsoft query that allows my Excel 2007
to
query my
SQL Database.

How to I reinstall the microsoft query tool in Excel?

DIM rw as Long


"Greenwind" wrote in message
...
Hi Patrick,

It didn't work out but thank you very much anyway. rw cannot be
recognized,
I guess. I'll try my luck in other posts. Thank you!
--
Tatiana


"Patrick Molloy" wrote:

seems you did start a new thread ;)


"Greenwind" wrote in message
...
Hi Parick,

You're right. The dates doesn't matter. I'll give it a try and let you
know how do I get on.

By the way, could you take my last post and give it a new name as, say,
"SQL in Microsoft Query", please? I still cannot begin a new thread
after
I
emailed Microsoft support.

Thanks a lot!
"Patrick Molloy" wrote:

so basically the "dates" in A and D should only be month/year as the
day
number is irrelevent?

this my work....change "A" to "D" to repeat for the 2nd set of dates
..or
code it

rw=1
do until cells(rw,"A").value=""
cells(rw,"A").Value = format$(cells(rw,"A").Value,"MMMYY")
rw=rw+1
loop



"Greenwind" wrote in message
...
Hi Patrick,

Thanks very much for the reply. Sorry. Somehow I cannot post any new
question (starting a thread) here.

I have to do programming because my data is a bit complicated. For
simplicity reasons, I only gave a short example in my last email. My
data
is
in an Excel file named handling. Id like to compare Column A and D
then
fill
column F.

My data: There are 3 groups in my data. Group 1: Column A and Column
B.
Group 2: Column C and D. Group 3: Column E and F. Column A has
monthly
data
from Jan 1988 to Jan 2006. Notice although 31 Jan 88 is a date, it
means
a
period from Jan 01, 1988 to Jan 31, 1988. Both dates, Jan 01, 1988
and
Jan
31, 1988 are included. Column B is the debt of the corresponding
month.
e.g.
From Jan 01, 1988 to Jan 31, 1988, the debt is 2.3343. Column C and
D
are
the
companies names and debt issuing dates respectively. In column C,
there
are
848 different firms. However, the starting and end dates are
different
in
these two columns. That is, according to column D, company A0004
does
not
have issuing dates during 1988-2000. The issuing dates for A0004
only
exist
during 2001 and 2005. There is another example: A0005s issuing
dates
are
from 1989 to 2005. Column E has 848 firms although firms names are
not
listed in column E. Each firm has a fixed format which starts with
1988
and
finishes with 2005 in column E.

Column A Column B Column C Column D Column E Column
F
Time Debt Company Issuing date Year Arranged Debt
31 Jan 88 2.3343 A0004 31/12/2001 1988 3.0045
28 Feb 88 3.5674 A0004 30/04/2002 1989 3.0482
31 March 4.5711 A0004 30/09/2003 €¦
€¦ €¦ A0004 01/04/2004 €¦
31 Dec 88 3.0045 A0004 30/09/2005 €¦
€¦ €¦ A005 31/12/1989 €¦
31 Dec 89 3.0482 A005 31/12/1990 €¦
31 Dec 01 3.1124 €¦ €¦ €¦
€¦ €¦ €¦ €¦ €¦
31 Dec 05 4.5711 €¦ €¦ 2001 3.1124
€¦ €¦ €¦ €¦ €¦
€¦ €¦ €¦ €¦ 2005
€¦ €¦ €¦ €¦ 1988
€¦ €¦ €¦ €¦ €¦
... ... A005 31/12/2005 2001
€¦ €¦ €¦

€¦ €¦ 2005
€¦ €¦ €¦
In order to compare Column A and D, I have to change them to the
same
format. Although you kindly suggested the function, Im afraid that
I
still
need a program, I guess. Because 31/12/2001 corresponds to 31 Dec
01
in
column A, 3.1124 should be placed after the 2001 in column E for
firm
A0004.
As for the 1st and the 2nd cell in column F, the year-end-values in
column
B
are used. Based on column D, there is no issuing date for A0004
during
1988-2000. Therefore, we assume the issuing date for A0004 in 1998
is
the
last date of 1988, i.e. 31 Dec 88. Hence 3.0045 is put in the 1st
cell
in
column F. Similarly, 3.0482 is placed after 1989 in column E.

Now I know how to link my Excel file (say named: handling) to
Microsoft
Query in order to write SQL. I also know programming in the SQL
window
within
Microsoft Query is possible to solve my problem. The remaining
problem
is
how, I think.

I tried the first step: UPDATE Time
SET Time = 31/01/1988
WHERE Time = 31 Jan 88
The error message is: Syntax error in €œUPDATE Time€�.

Thank you so much!


"Patrick Molloy" wrote:

you've hijacked another question her ehaven't you?

if you excel sheet is populated from a datatase, then its likely
that
the
date you see 31 Jan 88 is actually text. so you need to convert
this
to a
"proper" date using =DATEVALUE() function. you can then format the
cell
to
show dd/mm/yy or whatever.
you can drop this into columns D and F, pointimng at A and B to
convert
both
columns.

I'm not sure why you'd want to do this in SQL, unless that's what
you
did
to
generate the data in the first place?


"Greenwind" wrote in message
...
I'm sure this is too basic for a lot of you. Perhaps a bit silly.

I have a Excel file and I'd like to 1) to change the format of
one
column
in
the Excel file. For example, to change 31 Jan 88 to 31/01/1998.
(I've
tried
to change this in Excel but I can't). 2) still in Excel, to
compare
the
content of two columns (A and C) and to fill a third column's (C)
content
into a new column (D). For example, Column A is 31 Dec 2001.
Although
31
Dec
2001 is a date, 31 Dec 2001 actually means the period from 01 Dec
2001
to
31
Dec 2001. Both dates, 01 Dec 2001 and 31 Dec 2001, are included.
Column
B
has
the amount of debt in sterling pounds (3.1124) in corresponding
to
column
B.
Column C shows the issuing dates of the debt (31 Dec 2001).
Because
31
Dec
2001 is within the period of 01 Dec 2001 and 31 Dec 2001 in
column
A,
the
content of column B (3.1124) is put into column D.

Do you think that I can achieve above by writing a small program
in
the
SQL
window within Microsoft Query? Or I have to have the software
Microsoft
SQL
Server 2000 :Developer Edition and a server to do this?

If I can do this in Microsoft Query, how can I link my Excel file
(say
named: handling) to Microsoft Query in order to write SQL,
please?

Thank you so much!


"jenn" wrote:

When I do this is tells me all my tables are empty... yet, I can
use
the
other option of 'import data from unlisted source'.. then
select
ODBC
and my
tables are there.

"Adnan" wrote:

Jenn,

I don't think you have removed the Query since this query
feature
is
part of the office suite and not part of the third party tools
(Analyser,
etc...), to ensure you have it, with your workbook excel
opened,
try
motivating to Data menu Import External Data New Database
Query

MS
Access Database then locate your database or external
source...

Hope this helps,
Adnan



"jenn" wrote:

I no longer needed my microsoft query analyzer program. In
removing
it I may
have also removed the microsoft query that allows my Excel
2007
to
query my
SQL Database.

How to I reinstall the microsoft query tool in Excel?