John-
I assume that some of the table names remain the same from year to year -
like subtblHEDISMeasureID. For the tables that contain the year as part
of the name, think about temporarily dumping the "current" year into a
table with a generic name. For example, put the tblHEDIS2012-Percentiles
data in tblHEDISCurrent-Percentiles. Then build all your queries once
using the generic names. When you want to run the report for 2012, clear
out the "generic" tables and copy the 2012 data into them, then run your
queries unmodified.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: "jfakes.rm" <jfakes@rocketmail.com>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Wednesday, November 28, 2012 3:20 PM
To: <MS_Access_Professionals@yahoogroups.com>
Subject: [MS_AccessPros] Multi- year tables and many queries
I have a database that gets fed three tables each year from a vendor. The
tables contain nation wide data just for that year so you can guage your
performance against other companies in the US. Each year, I have to build
numerous queries to grab the data and display different views for 5 years.
The problem is, I have to prepare numerous reports and graphs so each
year I have to update at least 20 queries.
Here are some of the things I have to capture and group (I didn't make
these names up, I inherited this database and can't make changes):
1. HEDIS measure name rates for Commerical and Medicare.
2. I have to capture what my company scored and what the 50th, 75th, and
90th percentile is for the nation and how many points we need to reach the
next percentile.
3. HEDIS measures where my company is in the top ten in the nation.
This is just a small portion of what I have to add to the queries each
year.
My question is, is there someway I can build a table with the years I want
to pull (say 2008, 2009, 2010, 2011, and 2012) and grab and group the data
the way I need it? To display it in the various reports, I have to name
each years data like: 2008 Commercial Rate, 2008 Medicare rate etc. How
would I update each years field names so that it would group and sort the
way the user needs it?
This is just one of the many queries that I have to build each year to
capture data for the new year.
SELECT DISTINCT subtblHEDISMeasureID.[Measure ID],
tblCOMPANYSpecificRates.AccreditationID,
subtblHEDISMeasureID.RequiredForAccreditation, subtblEntity.EntityType,
[tblHEDIS2012-Percentiles].AverageID,
[tblHEDIS2012-Percentiles].AverageName,
subtblHEDISMeasureID.ActiveMeasure, subtblHEDISMeasureID.[Domain Name],
tblCOMPANYSpecificRates.ReportingYear,
f_qryCOMPANYExecutiveSummarySelectBestInCO2012.COMPANYShortName,
subtblHEDISMeasureID.ShortName, tblCOMPANYSpecificRates.LineOfBusiness,
tblCOMPANYSpecificRates.ServiceArea,
IIf(tblCOMPANYSpecificRates.ReportingYear=2012,tblCOMPANYSpecificRates.Rate
,0) AS 2012Rate, [tblHEDIS2012-Percentiles].[10thPercentile] AS 201210th,
[tblHEDIS2012-Percentiles].[25thPercentile] AS 201225th,
[tblHEDIS2012-Percentiles].[50thPercentile] AS 201250th,
[tblHEDIS2012-Percentiles].[75thPercentile] AS 201275th,
[tblHEDIS2012-Percentiles].[90thPercentile] AS 201290th,
f_qryCOMPANYExecutiveSummary2012GetInvertedRates.[2012CorrectedPercentile]
AS 2012Percentile,
f_qryCOMPANYExecutiveSummary2012GetInvertedRates.[2012CorrectedNextPercenti
le] AS 2012NextPercentileRate,
[f_qryCOMPANYExecutiveSummary2011-For2012].[2011Rate],
[f_qryCOMPANYExecutiveSummary2011-For2012].[2011CorrectedPercentile] AS
2011Percentile,
Val([f_qryCOMPANYExecutiveSummarySelectBestInCO2012].[Rate]) AS BestInCO,
f_qryCOMPANYExecutiveSummary2012SoCO.[2012SoCORate],
IIf([2012Percentile]="N/A" Or
[2011Percentile]="N/A","N/A",IIf([2012Percentile]="<10th" And
[2011Percentile]="10th","¡è",IIf([2012Percentile]>[2011Percentile],"¡è",IIf
([2012Percentile]<[2011Percentile],"¡é",IIf([2012Percentile]=[2011Percentil
e],"¡ê"))))) AS PercentileChange, subtblHPRRankingWeights.HPRCAHPSComm,
subtblHPRRankingWeights.HPRTreatmentComm,
subtblHPRRankingWeights.HPRPreventionComm,
subtblFiveStars.Medicare1StarRate, subtblFiveStars.Medicare2StarRate,
subtblFiveStars.Medicare3StarRate, subtblFiveStars.Medicare4StarRate,
subtblFiveStars.Medicare5StarRate, IIf([HPRTreatmentComm]=True Or
[HPRPreventionComm]=True Or [HPRCAHPSComm]=True,"*") AS
HPRRankingEntityComm, IIf([HPRTreatmentMedicare]=True Or
[HPRPreventionMedicare]=True Or [HPRCAHPSMedicare]=True,"*") AS
HPRRankingEntityMed, subtblHEDISMeasureID.RequiredForFiveStar, "0" AS
RequiredForAccredComm, "0" AS RequiredForAccredMedicare INTO
tblCOMPANYExecutiveSummary
FROM (((subtblHEDISMeasureID RIGHT JOIN (((((tblCOMPANYSpecificRates LEFT
JOIN [tblHEDIS2012-Percentiles] ON tblCOMPANYSpecificRates.MeasureID =
[tblHEDIS2012-Percentiles].MeasureID) LEFT JOIN
f_qryCOMPANYExecutiveSummarySelectBestInCO2012 ON
[tblHEDIS2012-Percentiles].MeasureID =
f_qryCOMPANYExecutiveSummarySelectBestInCO2012.MeasureID) LEFT JOIN
f_qryCOMPANYExecutiveSummary2012GetInvertedRates ON
tblCOMPANYSpecificRates.MeasureID =
f_qryCOMPANYExecutiveSummary2012GetInvertedRates.MeasureID) LEFT JOIN
[f_qryCOMPANYExecutiveSummary2011-For2012] ON
tblCOMPANYSpecificRates.MeasureID =
[f_qryCOMPANYExecutiveSummary2011-For2012].[Measure ID]) LEFT JOIN
f_qryCOMPANYExecutiveSummary2012SoCO ON tblCOMPANYSpecificRates.MeasureID
= f_qryCOMPANYExecutiveSummary2012SoCO.[Measure ID]) ON
subtblHEDISMeasureID.[Measure ID] = tblCOMPANYSpecificRates.MeasureID)
LEFT JOIN subtblHPRRankingWeights ON
tblCOMPANYSpecificRates.AccreditationID =
subtblHPRRankingWeights.AccreditationID) LEFT JOIN subtblFiveStars ON
tblCOMPANYSpecificRates.AccreditationID = subtblFiveStars.AccreditationID)
LEFT JOIN subtblEntity ON tblCOMPANYSpecificRates.AccreditationID =
subtblEntity.AccreditationID
WHERE (((subtblEntity.EntityType)="Executive Summary") AND
(([tblHEDIS2012-Percentiles].AverageID)=1) AND
(([tblHEDIS2012-Percentiles].AverageName)="National - All LOBs: Average")
AND ((subtblHEDISMeasureID.ActiveMeasure)=True) AND
((tblCOMPANYSpecificRates.ReportingYear)=2012) AND
((tblCOMPANYSpecificRates.LineOfBusiness)="Commercial") AND
((tblCOMPANYSpecificRates.ServiceArea)="Regional (all service areas)"))
ORDER BY subtblHEDISMeasureID.[Measure ID], subtblHEDISMeasureID.ShortName;
John F
------------------------------------
Yahoo! Groups Links
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
Tidak ada komentar:
Posting Komentar