Date lookup in Table

[JGal01]

I have two tables. In one table, there are lines with various dates which
contain information that I will eventually summarize based on Fiscal Months.
In another table, I have the the start and end dates for the fiscal months.

Table 1
Item Date Qty
A 02/01/06 20
A 02/18/06 10
A 03/04/06 15

Table 2
Monthstart Month End Fiscal Month
12/30/05 01/28/06 5
01/29/06 02/28/06 6
03/01/06 03/28/06 7

In the query, I want to lookup the date in table 1, in table 2 and return
the Fiscal Month number relating to the range that this falls in.

Table 1
Item Date Qty Fiscal Month
A 02/01/06 20 6
A 02/18/06 10 6
A 03/04/06 15 7

Any help would be appreciated

 

[KARL DEWEY]

Table1 should not use Date as a field name as it is a reserved word in
Access. Below I used ActionDate. Create a column in query design view like
--

Fiscal Month: IIf([ActionDate] Between [Monthstart] and [Month End], [Fiscal
Month], “Errorâ€)

This was not tested and you may need to use the table names like –
Fiscal Month: IIf([Table1].[ActionDate] Between [Table2].[Monthstart] and
[Table2].[Month End], [Table2].[Fiscal Month], “Errorâ€)

 

[JGal01]

It seems to work. Thanks.

Table1 should not use Date as a field name as it is a reserved word in
Access. Below I used ActionDate. Create a column in query design view like
--

Fiscal Month: IIf([ActionDate] Between [Monthstart] and [Month End], [Fiscal
Month], “Errorâ€)

This was not tested and you may need to use the table names like –
Fiscal Month: IIf([Table1].[ActionDate] Between [Table2].[Monthstart] and
[Table2].[Month End], [Table2].[Fiscal Month], “Errorâ€)

I have two tables. In one table, there are lines with various dates which
contain information that I will eventually summarize based on Fiscal Months.
In another table, I have the the start and end dates for the fiscal months.

Table 1
Item Date Qty
A 02/01/06 20
A 02/18/06 10
A 03/04/06 15

Table 2
Monthstart Month End Fiscal Month
12/30/05 01/28/06 5
01/29/06 02/28/06 6
03/01/06 03/28/06 7

In the query, I want to lookup the date in table 1, in table 2 and return
the Fiscal Month number relating to the range that this falls in.

Table 1
Item Date Qty Fiscal Month
A 02/01/06 20 6
A 02/18/06 10 6
A 03/04/06 15 7

Any help would be appreciated