Looking up across date ranges in Access

[Pete]

I am mapping financial data from one system to another and need to look up in
a query a dept based on a date range.

tblDeptMap reads
Dept FromDate To Date NewDept
1121 01/01/2009 31/03/2009 1125
1121 01/04/2009 31/05/2009 2332

Financial Data Table Looks Like this:
Dept Month Year Amount
1121 01 2009 32
1121 02 2009 41
1121 03 2009 50
1121 04 2009 52
1121 05 2009 25

I need a query that will read:
Dept Month Year Amount FinDate NewDept
1121 01 2009 32 01/01/2009 1125
1121 02 2009 41 01/02/2009 1125
1121 03 2009 50 01/03/2009 1125
1121 04 2009 52 01/04/2009 2332
1121 05 2009 25 01/05/2009 2332

The first step of this is straight forward. Raise a query on the financial
data table with a date formula to calculate FinDate.
The problem is how to link this query to look up the correct "NewDept" code.
Simple linking only returns exact matches, ie jan and apr records.

Anybody been here??

Also, is there a way of putting data integrity/input validation controls
into tblDeptMap so that all dates are included, ie the next fromdate for a
given dept should be ToDate Plus 1.??

 

[John Spencer]

SELECT FinancialData.Dept, FinancialData.Year, FinancialData.Month,
FinancialData.Amount, tblDeptMap.NewDept
FROM FinancialData INNER JOIN tblDeptMap
ON FinancialData.Dept = tblDeptMap.Dept
WHERE DateSerial(FinancialData.Year,FinancialData.Month,1) Between
TblDeptMap.FromDate and tblDeptMap.ToDate

You might be able to do the join as follows.

SELECT FinancialData.Dept, FinancialData.Year, FinancialData.Month,
FinancialData.Amount, tblDeptMap.NewDept
FROM FinancialData INNER JOIN tblDeptMap
ON FinancialData.Dept = tblDeptMap.Dept
AND (DateSerial(FinancialData.Year,FinancialData.Month,1) Between
TblDeptMap.FromDate and tblDeptMap.ToDate)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Pete]

Thanks John, Seems to do exactly what I asked.
Now lets have a go with 700,000 records across 130 depts!!!
Wish me luck
Thanks for the assist
Pete