Lookup Query

[CT Witter]

I'm trying to create a lookup query to transform some data.

Table1 has item# and date as mm/dd/yyyy
Table2 has startdate as mm/dd/yyyy, enddate as mm/dd/yyyy, month as number 1 - 12

I would like to convert the date in table 1 to the month in table2. Table2 is based on a different calendar system.

How can I do this?

Thanks,
CT

 

[Andrew Fergus]

Hi

What do you mean by this?

Table2 is based on a different calendar system.

Are you wanting to get a match on the month portion of the date from Table 1 to, in your own words, "month as number 1 - 12", from Table 2?

Andrew

 

[CT Witter]

Table1 has dates as normal US Calendar dates.
Table2 has dates as a fiscal calendar.

IE Table2
1/1/2004 - 1/25/2004 = 1
1/26/2004-2/25/2004 = 2

etc.

I want to take the date from table1 and have a column tell me the new month

IE 1/24/04 = 1, 1/25/04 =1,1/26/04=2

Thanks,
CT

 

[Andrew Fergus]

Hi, sorry for not getting back to you sooner.

I have tested some data and got it to work by adding the 2 tables into a new query, I did not link the 2 tables, from table 1 I selected the "date" field and from table 2 I selected the "new month number", under the "date" field I entered the following criteria : ">=[start_date] And <=[end_date]" (without the quotes).

The SQL, if it is any help to you, looks like this :

• SELECT table1.date, table2.month_number
  FROM table1, table2
  WHERE (((table1.date)>=[start_date] And (table1.date)<=[end_date]));

but remember to use your variable and table names.

HTH, Andrew.

 

[CT Witter]

Andrew-

Works GREAT!! Thanks for your Help!!