date ranges in queries

  • Thread starter Thread starter Brassstar
  • Start date Start date
B

Brassstar

I have a data base that i have multiple entries by date. I need a query to
pull the last date entered in the date field I have been unable to find this
in the book Help
 
If you are looking for the most recent date, enable the "Totals" line
in the query design (View-->Totals), and set the total on your Date
field to Max. It will return the "biggest" date.
 
Not enough information for a comprehensive answer.

Use the VBA DMax function:
DMax("SomeField","SomeTable")

or a use a query that looks like the following

SELECT Max([SomeField]) as Largest FROM SomeTable

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
thanks a lot for the info I tried it but it had no effect. The table in
question has multible dates of rank on individuals Ineed to pull the most
recent date of rank. I cnat find the answer in the several books i have. Up
to now I have solved the problem by using a current rank field as yes and no
entries sure could use some help
 
ive designed a table that has multible entries for rank sgt, lt, captain etc.
with the date of rank. I need to find a way to pull the most current rank
from the field in reports I sure could use some help
--
phil dantoni


John Spencer said:
Not enough information for a comprehensive answer.

Use the VBA DMax function:
DMax("SomeField","SomeTable")

or a use a query that looks like the following

SELECT Max([SomeField]) as Largest FROM SomeTable

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

I have a data base that i have multiple entries by date. I need a query to
pull the last date entered in the date field I have been unable to find this
in the book Help
Click to expand...
Click to expand...
 
A generic query -- change the generic names to your real field and table
names.

SELECT Tablename.*
FROM Tablename
WHERE Tablename.DateField=
(SELECT MAX(T.DateField)
FROM Tablename AS T
WHERE T.PrimaryKey = Tablename.PrimaryKey);
 
Still rather sparse on the structure of the table.
Assumptions: A table with the structure
tblPersonRank - table name
PersonID
Rank
DateOfRank (Date field)

Create a query and save it as qCurrentRank
SELECT PersonID, Max(DateOfRank) as CurrentRankDate
FROM tblPersonRank
GROUP BY PersonId

Now use that query and the Persons and the tblPersonRank table

Select Persons.*, qCurrentRank
FROM (Persons INNER JOIN tblPersonRank
ON Person.PersonId = TblPersonRank.Personid)
Inner JOIN qCurrentRank
ON TblPersonRank.PersonID = qCurrentRank.PersonID
And tblPersonRank.DateOfRank = qCurrentRank.CurrentRankDate

In the query design window (1st query) qCurrentRank
-- add the rank table
-- add the personid field and the date of rank field
-- Select View: Totals from the menu
-- Change GROUP BY to Max under the Date of rank
-- save the query as qCurrentDOR (or whatever name you want)

NOW, open a new query
-- Add the persons table and the ranks table and the above query to your
query
-- Set up the relationships. Drag from the personid on the persons
table to the personid in the ranks table. Now Drag from the rank table
personid field to the query person id and then drag from the rank table
date of rank field to the query max date of rank field.
-- Select the fields you want returned.

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