Current and Previous Date

[DThreadgill]

Not sure how to begin with this one. My table consists of:

Branch# (number, double) 
EntryDate (datetime, mm/dd/yyyy hh:mm:ss am/pm)

One branch can have many entry dates (i.e, Branch # 76 has 10 entry dates).

I'm trying to show the most recent entry date & the previous entry date on a report. I know how to get the most recent entry date using Max. How would I get to the previous entry date? (Current is 2/14/2008 2:26:07 PM and the one before that is 1/30/2008 12:25:58 PM).

(or even a ranking would do i.e, Branch 76 would have a ranking of 1-10 based on entry_date and I could use 1 as current and 2 as previous).

I might mention that there are multiple branch #'s and I need to get the current & previous entry date for each branch (or rank by entry date for each branch).

Any help is appreciated!

[Stewart Ross]

Hi. The branch dates can be obtained using two relatively simple queries. Open the Access query designer, select View, SQL View and Paste in the first SQL query below. Change the name of the [BranchEntries] table placeholder to the name of your actual table throughout, save the query under the name BranchMaxEntry.

Paste the SQL for the second query, again changing the name of the [BranchEntries] table to the actual name you are using. Save this under the name BranchCurrentDa tes.

You can then add this query to your report query and join it on the branch#. This will repeat on all rows the previous and latest entry dates. You are then able to add these to whatever part of your report is best.

Please note that if a branch has just one date entry it will not show in the BranchCurrentDa tes query. For this reason you may have to use a left-join with your report query in order to preserve entries for branches with just one date.

Test data shown below.

Hope this helps.

-Stewart

Query BranchMaxEntry
[code=sql]SELECT [BranchEntries].[Branch#], Max([BranchEntries].EntryDate) AS MaxEntry
FROM [BranchEntries]
GROUP BY [BranchEntries].[Branch#];[/code]
Query BranchCurrentDa tes
[code=sql]SELECT [BranchEntries].[Branch#], Max([BranchEntries].EntryDate) AS [Prev Entry], BranchMaxEntry. MaxEntry AS [Latest Entry]
FROM [BranchEntries] INNER JOIN BranchMaxEntry ON [BranchEntries].[Branch#] = BranchMaxEntry.[Branch#]
WHERE (((BranchMaxEnt ry.MaxEntry)<>[EntryDate]))
GROUP BY [BranchEntries].[Branch#], BranchMaxEntry. MaxEntry;[/code]

Test Data (uk date format shown - dd/mm/yyyy)

BranchEntries Table
Branch#..EntryDate
.76......01/03/2008
.76......15/03/2008
.76......04/04/2008
100......10/03/2008
100......05/04/2008
110......01/04/2008
110......03/04/2008
110......05/04/2008
110......08/04/2008
 
BranchmaxEntry Query
Branch#..MaxEntry
.76......04/04/2008
100......05/04/2008
110......08/04/2008
 
BranchCurrentDates Query
Branch#..Prev Entry...Latest Entry
.76......15/03/2008...04/04/2008
100......10/03/2008...05/04/2008
110......05/04/2008...08/04/2008

[DThreadgill]

Thank you so much!!! I haven't had the chance incorporate into my database but will let you know the results!