John V,
Like I said, this was but one of many queries I need to build. The table names do stay constant year from year. So each year, I have to add code in my queries to add the current year. For example, I have numerous queries for 2012 for numerous reports and graphs. Next year, I will have to copy all the queries for 2013 and point everything to the new tables. What I would like to do, is take all my existing queries and rebuild them somehow to automatically add the data for 2013, 2014 etc automatically so I don't have to add new queries every year.
I have around 100 reports I produce from this database each year so as you can imagine, I have to do a lot of updating. I'd like to figure out the best way to update queries as well as reports to calculate everything automatically.
BTW I have been reading your book MS Office Access 2003 and have learned several slick tricks I wasn't aware of.
John F.
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> 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@...>
> 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
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (3) |
Tidak ada komentar:
Posting Komentar