• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 480
  • Last Modified:

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

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
willjx
Asked:
willjx
  • 5
  • 4
1 Solution
 
als315Commented:
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
 
willjxAuthor Commented:
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
 
als315Commented:
"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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
willjxAuthor Commented:
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
 
als315Commented:
Upload sample DB and show expected result
0
 
willjxAuthor Commented:
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
 
willjxAuthor Commented:
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
 
als315Commented:
"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
 
willjxAuthor Commented:
Thanks much.
I finally got it, that I needed to join both MsrCd and Entry Date
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now