how to query data as of a specific date

[Twotone]

I'm relatively new at access so I'm hoping someone might be able to give me
an efficient way to perform the following;

I'm trying to retrieve historical records as of a specific date from a
table. For example, I need to retrieve the status of a group of employees as
of 02/15/2006. However, an employee's history records for the status field
are, 01/01/2005 - FT, 02/01/2006 - PT, 01/15/2007 - FT . What's the best way
to query the tbl so that PT is returned when I run the query with an "as of"
date of 2/15/2006?

 

[John Spencer]

Base query would be as follows.

SELECT EmployeeID, Max(StatusDate) as TheTargetDate
FROM StatusTable
WHERE StatusDate <= #2/15/2006#
GROUP BY EmployeeID


If you wanted more than just that information, you could save the above
query (call it qBase) and then use it
SELECT StatusTable.*
FROM StatusTable INNER JOIN qBase
ON StatusTable.EmployeeID = qBase.EmployeeID
And StatusTable.StatusDate = qBase.TheTargetDate

In the query grid, build qOne
-- Add the status table
-- Select the employeeId field and the status date field and the statusDate
field again
-- Select View Totals from the menu
-- Change Group by in the totals to Max under one StatusDate and to WHERE
under the other
-- Under where enter <= yourcutoffDate
Save that

Use it if it were a table in a second query where you have the status table
and the query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[KARL DEWEY]

If I read the post correctly then they needs this ---
SELECT EmployeeID, Right([StatusDate],4) As Status
FROM StatusTable
WHERE Max(DateSerial(Right(Left([StatusDate],10),4), Left([StatusDate],2),
Right(Left([StatusDate],5),2))) <= #2/15/2006#
GROUP BY EmployeeID

 

[John Spencer]

Hmmmm. Now that I look at it you could be right. I was assuming that the
sample data was indicating two fields in the status table. One field being
the As of Date of the status and the other the status of FT or PT (guessing
that would be Full Time or Part Time). I see you read the sample as being
one text field with both the date and status included in the one field.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

If I read the post correctly then they needs this ---
SELECT EmployeeID, Right([StatusDate],4) As Status
FROM StatusTable
WHERE Max(DateSerial(Right(Left([StatusDate],10),4), Left([StatusDate],2),
Right(Left([StatusDate],5),2))) <= #2/15/2006#
GROUP BY EmployeeID

--
KARL DEWEY
Build a little - Test a little

Base query would be as follows.

SELECT EmployeeID, Max(StatusDate) as TheTargetDate
FROM StatusTable
WHERE StatusDate <= #2/15/2006#
GROUP BY EmployeeID


If you wanted more than just that information, you could save the above
query (call it qBase) and then use it
SELECT StatusTable.*
FROM StatusTable INNER JOIN qBase
ON StatusTable.EmployeeID = qBase.EmployeeID
And StatusTable.StatusDate = qBase.TheTargetDate

In the query grid, build qOne
-- Add the status table
-- Select the employeeId field and the status date field and the
statusDate
field again
-- Select View Totals from the menu
-- Change Group by in the totals to Max under one StatusDate and to WHERE
under the other
-- Under where enter <= yourcutoffDate
Save that

Use it if it were a table in a second query where you have the status
table
and the query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..