By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,091 Members | 1,917 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,091 IT Pros & Developers. It's quick & easy.

Current and Previous Date

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

Expand|Select|Wrap|Line Numbers
  1. Branch# (number, double) 
  2. 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!
Apr 8 '08 #1
Share this Question
Share on Google+
2 Replies


Expert Mod 2.5K+
P: 2,545
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 BranchCurrentDates.

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 BranchCurrentDates 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
Expand|Select|Wrap|Line Numbers
  1. SELECT [BranchEntries].[Branch#], Max([BranchEntries].EntryDate) AS MaxEntry
  2. FROM [BranchEntries]
  3. GROUP BY [BranchEntries].[Branch#];
Query BranchCurrentDates
Expand|Select|Wrap|Line Numbers
  1. SELECT [BranchEntries].[Branch#], Max([BranchEntries].EntryDate) AS [Prev Entry], BranchMaxEntry.MaxEntry AS [Latest Entry]
  2. FROM [BranchEntries] INNER JOIN BranchMaxEntry ON [BranchEntries].[Branch#] = BranchMaxEntry.[Branch#]
  3. WHERE (((BranchMaxEntry.MaxEntry)<>[EntryDate]))
  4. GROUP BY [BranchEntries].[Branch#], BranchMaxEntry.MaxEntry;
Test Data (uk date format shown - dd/mm/yyyy)
Expand|Select|Wrap|Line Numbers
  1. BranchEntries Table
  2. Branch#..EntryDate
  3. .76......01/03/2008
  4. .76......15/03/2008
  5. .76......04/04/2008
  6. 100......10/03/2008
  7. 100......05/04/2008
  8. 110......01/04/2008
  9. 110......03/04/2008
  10. 110......05/04/2008
  11. 110......08/04/2008
  12.  
  13. BranchmaxEntry Query
  14. Branch#..MaxEntry
  15. .76......04/04/2008
  16. 100......05/04/2008
  17. 110......08/04/2008
  18.  
  19. BranchCurrentDates Query
  20. Branch#..Prev Entry...Latest Entry
  21. .76......15/03/2008...04/04/2008
  22. 100......10/03/2008...05/04/2008
  23. 110......05/04/2008...08/04/2008
Apr 9 '08 #2

P: 57
Thank you so much!!! I haven't had the chance incorporate into my database but will let you know the results!
Apr 9 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.