[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Easy way to display the most recent status entry.

Posted on 2008-11-10
7
Medium Priority
?
357 Views
Last Modified: 2012-05-05
Easy way to display the most recent status entry.

I started doing this in Excel, but could do this either in Access or Excel  -- whichever is the simplest to implement and easiest run on a daily basis.

Attached is an example of the kind of status report I'm keeping at work for a new group of projects (not actual status).  The list could grow to maybe 500 rows and 100 different Projects.

Status information is entered by Date, Project No and Status comment.  There can be multiple Rows for the same Project in any day.

Date will just show the mm/dd/yy.  I don't want to enter time of day.  (in Excel, I am currently automatically copying the date from the previous row until I manually change it).

The problem to solve is how can I easily display just the most recent Excel row (or Access Record) information for each Project.
Sample-Status-List.gif
0
Comment
Question by:WaterStreet
  • 4
  • 3
7 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 22924474
Either store the time as well (store automatically but dont show it!) and so ordering by date/time should do it or within Access ensure your table has a primary key (an autonumber will suffice). You can then create a query and order by the id but in descending order.

select id, mydate, projectno, status
from mytable
order by id desc
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 22924511
You could create a number field in excel also then sort the data by that column but in descending order. A simple way to create a autonumber possibly in excel?
http://www.techonthenet.com/excel/formulas/autonumber.php


0
 
LVL 18

Author Comment

by:WaterStreet
ID: 22924852
rockiroads,
I tried your suggestion in the first posting, but it shows all the records for each project.  I just want to show the most recent record for each project (which will be the latest)
I'm using the autonumber as the "id," so what I need is to show is only the record for each project that has the highest id.
Thanks
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 65

Expert Comment

by:rockiroads
ID: 22925085
Ok, gotcha

I got to go out , will be back on online in about 40mins. I will post the solution then.

The query will need a subquery to get the max id's per project then use that as a table and link in the main table

from memory as I havent touched access nor sql for ages, and I dont have access on hand to verify it - will do in 40mins or so though

select a.datefield, a.projectnumber, a.status
from mytable as a, (select max(id) as maxid, projectnumber
                                from mytable
                               group by projectnumber) as b
where a.id = b.maxid


0
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 22927115
Thats the query, I gave it a quick test to confirm

Created a table called ProjStatus and contains three fields  ID, ProjectNumber and Status and here is my query

SELECT A.ProjectNo, A.Status
FROM ProjStatus as A, (SELECT Max(ID) as MaxID, ProjectNo FROM ProjStatus GROUP BY ProjectNo) as B
WHERE A.ID = B.MaxID

Returns the last record for each project number

0
 
LVL 18

Author Comment

by:WaterStreet
ID: 22927348
rockiroads,

Thank you very much.  It worked and I modified it for my own purposes -- fyi, see insert below.
Where were you when I need this eight years ago?  :-))
I can now emulate this technique for my other uses.

By the way,  the field name "ProjectNumber" in the table should be "ProjectNo"  right?  If it's okay with you I'll edit it for the change so that your answer in the PAQ works immediately for anyone else who uses it.

Thanks again

WaterStreet,
Core Zone Advisor - Other


Final-Design-Views.jpg
0
 
LVL 18

Author Closing Comment

by:WaterStreet
ID: 31515194
Perfect - taught me a little more SQL and a techniques I can apply again
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question