If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Looking up across date ranges in Access
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.?? |
#2
|
|||
|
|||
Looking up across date ranges in Access
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 wrote: 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.?? |
#3
|
|||
|
|||
Looking up across date ranges in Access
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 "John Spencer" wrote: 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 wrote: 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.?? . |
Thread Tools | |
Display Modes | |
|
|