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

John V,
Each year I get four tables (a new one was added in 2012):
tblHEDIS2012-AllMeasures (around 90,000 records).
tblHEDIS2012-AllMeasuresMedicare (around 280,000 records).
tblHEDIS2012-Percentiles (around 1,400 records).
tblHEDIS2012-PercentilesMedicare (around 4,500 records).

I just took over this database and am trying to make my life a little easier by eliminating making 20 new queries every year.

Yes the columns are always the same.

I'm beginning to see what you are saying to do, however, several of the queries deal with sorting and ranking top ten measures and it already takes at least 5 minutes to run some of my queries. I'm afraid if I make the tables bigger, it will take 15 or even 20 minutes to run.

Thanks for helping out, I'm going to start updating the queries in the next few weeks so I'll start testing and see how it comes out.

John F.

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> 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@...>
> 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