ACCESS FEED: Re: [MS_AccessPros] Multi- year tables and many queries

John F.-

What, for example, are the names of the 2012 tables? The 2013 tables?
Are the columns always the same?

The problem is you appear to have separate tables for each year, which is
going to be a management nightmare over time. When you import the new
data, why not dump it into a table that has the data from ALL the years
and add a DataYear column that you plug with a constant? Then use the
combined table (you can even throw away the individual annual data tables)
and dynamically filter for the years you want.

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: Thursday, November 29, 2012 3:54 PM
To: <MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] Multi- year tables and many queries

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.Rat
>e
> ,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.[2012CorrectedNextPercent
>i
> 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],"¡è",II
>f
>
>([2012Percentile]<[2011Percentile],"¡é",IIf([2012Percentile]=[2011Percenti
>l
> 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