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. I’d 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: A0005’s 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, I’m 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!