Solved

ACCESS: Need to show a Performance Measure listing that is limited to only the latest data entered.

Posted on 2013-12-12
9
414 Views
Last Modified: 2013-12-16
I have an ACCESS query that joins two tables.  -One to many-  The many is a history table of the dated data collected related to the listed Performance Measures in the 1st table.*
On the many table I have two+ entries of the same record (MsrCd –unique to each Measure- with 2+ different dates.
I  want the report to show me, along with other descriptive info from the 1st table,  just the latest date for every MsrCd.
I have been trying various syntax’ without success:
Ie:    Max( [MsrCd] ! {Entry Date]  )

*-Generated is a report listing of over a hundred Performance Measures showing the Target amount, the data input for a specific date and the resulting Status for that Measure.
The list is showing the same Measure more than once for each new data entry.  I want it to show each Measure only once – the once being the latest MsrCd' data entered.
Eventually, I have to show the latest data entered in each quarter.
0
Comment
Question by:willjx
  • 5
  • 4
9 Comments
 
LVL 39

Expert Comment

by:als315
ID: 39714835
You need 2 queries:
In first (only MsrCd and Entry Date fields) - select max Entry date and group by MsrCd
In second - join your table and first query by MsCd and Entry Date
0
 

Author Comment

by:willjx
ID: 39715533
Did not work:
"In first (only MsrCd and Entry Date fields) - select max Entry date and group by MsrCd"
This did Work to limit to only one MsrCd,
However, when I included this query with the other query that had MeasureDesc linked to TmsrAmt (the history table), it still kept showing the old MsrCd and the new MsrCd.
--disregarding various cardinality changes.
0
 
LVL 39

Expert Comment

by:als315
ID: 39715744
"This did Work to limit to only one MsrCd" - no, you will have 2 columns: one with MsrCd, Second - with Max([Entry Date]) and each MsrCd will have own maximal Enrtry Date.

May be you can prepare sample DB with dummy data?
0
 

Author Comment

by:willjx
ID: 39717456
The query you supplied did limit to the single latest MsrCd and Max([Entry date]) with each MsrCd having its own maximal Enrtry Date.
That is not the issue.
The problem occurs when I take the above query and I join it to the history table, the result goes back to displaying the old and the new MsrCd, when i want it to just display the new MsrCd.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 39

Expert Comment

by:als315
ID: 39717489
Upload sample DB and show expected result
0
 

Author Comment

by:willjx
ID: 39717890
SELECT Data.Level, Data.Programs, Data.Measure, TmsrAmt.MsrCd, TmsrAmt.Target, IIf(([Do we want to see an increase in this Measure?]=True And ([Data]-[TmsrAmt]![Target])/[TmsrAmt]![Target]>=0) Or ([Do we want to see an increase in this Measure?]=False And ([Data]-[TmsrAmt]![Target])/[TmsrAmt]![Target]<=0),"Green",IIf(([Do we want to see an increase in this Measure?]=True And ([Data]-[TmsrAmt]![Target])/[TmsrAmt]![Target]>=-0.1) Or ([Do we want to see an increase in this Measure?]=False And ([Data]-[TmsrAmt]![Target])/[TmsrAmt]![Target]<=0.1),"Yellow",IIf(([Do we want to see an increase in this Measure?]=True And ([Data]-[TmsrAmt]![Target])/[TmsrAmt]![Target]<-0.1) Or ([Do we want to see an increase in this Measure?]=False And ([Data]-[TmsrAmt]![Target])/[TmsrAmt]![Target]>0.1),"Red","Blue"))) AS Status, TmsrAmt.Data, Data.[Outcome Owner], TmsrAmt.[Data Source], Data.[Key Insights], Data.[Next Action], Max(QpmStatusProgramSpcfkGrp.[MaxOfEntry Date]) AS [MaxOfMaxOfEntry Date]
FROM (Data INNER JOIN QpmStatusProgramSpcfkGrp ON Data.MsrCd = QpmStatusProgramSpcfkGrp.MsrCd) INNER JOIN TmsrAmt ON QpmStatusProgramSpcfkGrp.MsrCd = TmsrAmt.MsrCd
GROUP BY Data.Level, Data.Programs, Data.Measure, TmsrAmt.MsrCd, TmsrAmt.Target, IIf(([Do we want to see an increase in this Measure?]=True And ([Data]-[TmsrAmt]![Target])/[TmsrAmt]![Target]>=0) Or ([Do we want to see an increase in this Measure?]=False And ([Data]-[TmsrAmt]![Target])/[TmsrAmt]![Target]<=0),"Green",IIf(([Do we want to see an increase in this Measure?]=True And ([Data]-[TmsrAmt]![Target])/[TmsrAmt]![Target]>=-0.1) Or ([Do we want to see an increase in this Measure?]=False And ([Data]-[TmsrAmt]![Target])/[TmsrAmt]![Target]<=0.1),"Yellow",IIf(([Do we want to see an increase in this Measure?]=True And ([Data]-[TmsrAmt]![Target])/[TmsrAmt]![Target]<-0.1) Or ([Do we want to see an increase in this Measure?]=False And ([Data]-[TmsrAmt]![Target])/[TmsrAmt]![Target]>0.1),"Red","Blue"))), TmsrAmt.Data, Data.[Outcome Owner], TmsrAmt.[Data Source], Data.[Key Insights], Data.[Next Action]
HAVING (((Data.Level)="program") AND ((Data.Programs) Like [Type 1st couple of letters of the spcfk Program] & "*"));
0
 

Author Comment

by:willjx
ID: 39717910
More simply stated SQL  where QpmStatusProgramSpcfkGrp is the initial Query " (only MsrCd and Entry Date fields) - select max Entry date and group by MsrCd"


SELECT TmsrAmt.MsrCd, TmsrAmt.Data, TmsrAmt.Target, QpmStatusProgramSpcfkGrp.[MaxOfEntry Date]
FROM TmsrAmt INNER JOIN QpmStatusProgramSpcfkGrp ON TmsrAmt.MsrCd = QpmStatusProgramSpcfkGrp.MsrCd
GROUP BY TmsrAmt.MsrCd, TmsrAmt.Data, TmsrAmt.Target, QpmStatusProgramSpcfkGrp.[MaxOfEntry Date];

I expect to see a list of the "SELECT fields, but displaying only the last entry for a given MsrCd.
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 39718645
"join your table and first query by MsrCd and Entry Date"
You should join both fields from QpmStatusProgramSpcfkGrp: MsrCd and Entry Date (QpmStatusProgramSpcfkGrp.[MaxOfEntry Date]) and should not use max again
0
 

Author Closing Comment

by:willjx
ID: 39722222
Thanks much.
I finally got it, that I needed to join both MsrCd and Entry Date
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now