Results 1 to 9 of 9
  1. #1
    tmcrouse is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    169

    Help with query update 1008 times

    I have a database with 3984 rows total. I will show the datahave and dataneed in a few seconds. The way I was going to do this is with an update query. However after calculating the number of states, products and measures, it would mean 1008 total update queries. There has to be a better way.


    This is an example of my queries I began writing:
    update
    comprank set ranking ='5 out of 10'wheremarket
    ='ca'and prodabbrev ='hmo'and measureid ='12'and competitor ='California dba HMO/POS';
    update
    comprank set ranking ='4 out of 10'where
    market
    ='ca'and prodabbrev ='hmo'and measureid ='24'and competitor ='California dba HMO/POS';
    If there is a better way that would be great.

    Data Have Example:
    hedismeasure measureid competitor market prodabbrev hedisrate rank ranking
    Breast Cancer Screening - BCS TOTAL 24 Aetna Health of California, Inc.-HMO/POS CA HMO 0.6774 10 NULL
    Breast Cancer Screening - BCS TOTAL 24 California dba HMO/POS CA HMO 0.7729 4 NULL
    Breast Cancer Screening - BCS TOTAL 24 Blue Shield of California-HMO/POS CA HMO 0.7516 8 NULL
    Breast Cancer Screening - BCS TOTAL 24 Sharp Health Plan-HMO CA HMO 0.816 3 NULL
    Breast Cancer Screening - BCS TOTAL 24 UnitedHealthcare of California-HMO CA HMO 0.7596 7 NULL
    Breast Cancer Screening - BCS TOTAL 24 Western Health Advantage-HMO CA HMO 0.7688 5 NULL
    Breast Cancer Screening - BCS TOTAL 24 Health Net of California, Inc.-HMO/POS CA HMO 0.7367 9 NULL
    Breast Cancer Screening - BCS TOTAL 24 Kaiser Foundation Health Plan Inc. - Southern California-HMO CA HMO 0.872 1 NULL
    Breast Cancer Screening - BCS TOTAL 24 Kaiser Foundation Health Plan, Inc. - Northern California-HMO CA HMO 0.871 2 NULL
    Breast Cancer Screening - BCS TOTAL 24 CIGNA HealthCare of California, Inc.-HMO/POS CA HMO 0.7627 6 NULL
    Breast Cancer Screening - BCS TOTAL 24 Aetna Life Insurance Company California-PPO CA PPO 0.6966 3 NULL
    Breast Cancer Screening - BCS TOTAL 24 California dba HMO/POS CA PPO 0.6988 2 NULL
    Breast Cancer Screening - BCS TOTAL 24 Blue Shield of California-PPO CA PPO 0.6916 4 NULL
    Breast Cancer Screening - BCS TOTAL 24 Sierra Health & Life California-PPO CA PPO 0.6302 6 NULL
    Breast Cancer Screening - BCS TOTAL 24 UnitedHealthcare Insurance Company California-PPO CA PPO 0.6852 5 NULL
    Breast Cancer Screening - BCS TOTAL 24 CIGNA - California-PPO CA PPO 0.705 1 NULL
    Data Need Example
    hedismeasure measureid competitor market prodabbrev hedisrate rank ranking
    Breast Cancer Screening - BCS TOTAL 24 Aetna Health of California, Inc.-HMO/POS CA HMO 0.6774 10
    Breast Cancer Screening - BCS TOTAL 24 California dba HMO/POS CA HMO 0.7729 4 4 out of 10
    Breast Cancer Screening - BCS TOTAL 24 Blue Shield of California-HMO/POS CA HMO 0.7516 8
    Breast Cancer Screening - BCS TOTAL 24 Sharp Health Plan-HMO CA HMO 0.816 3
    Breast Cancer Screening - BCS TOTAL 24 UnitedHealthcare of California-HMO CA HMO 0.7596 7
    Breast Cancer Screening - BCS TOTAL 24 Western Health Advantage-HMO CA HMO 0.7688 5
    Breast Cancer Screening - BCS TOTAL 24 Health Net of California, Inc.-HMO/POS CA HMO 0.7367 9
    Breast Cancer Screening - BCS TOTAL 24 Kaiser Foundation Health Plan Inc. - Southern California-HMO CA HMO 0.872 1
    Breast Cancer Screening - BCS TOTAL 24 Kaiser Foundation Health Plan, Inc. - Northern California-HMO CA HMO 0.871 2
    Breast Cancer Screening - BCS TOTAL 24 CIGNA HealthCare of California, Inc.-HMO/POS CA HMO 0.7627 6
    Breast Cancer Screening - BCS TOTAL 24 Aetna Life Insurance Company California-PPO CA PPO 0.6966 3
    Breast Cancer Screening - BCS TOTAL 24 California dba HMO/POS CA PPO 0.6988 2 2 out of 6
    Breast Cancer Screening - BCS TOTAL 24 Blue Shield of California-PPO CA PPO 0.6916 4
    Breast Cancer Screening - BCS TOTAL 24 Sierra Health & Life California-PPO CA PPO 0.6302 6
    Breast Cancer Screening - BCS TOTAL 24 UnitedHealthcare Insurance Company California-PPO CA PPO 0.6852 5
    Breast Cancer Screening - BCS TOTAL 24 CIGNA - California-PPO CA PPO 0.705 1

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    1 - where does the ' 4 out of 10' come from? if that was known, perhaps 1 query is all that is required
    2 - why update? why not just use a query when required?
    3 - your two queries could be combined into 1 if it was written like this

    Code:
    update
    comprank set ranking =switch(measureID=12,'5 out of 10',measureID=24,"4 out of 10") where market 
    ='ca'and prodabbrev ='hmo' and competitor ='California dba HMO/POS';


  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    How about using VBA:
    Create a table for the criteria

    tblCriteria (or what ever name you want)
    ranking........Text
    market .......Text

    prodabbrev...Text
    measureid....Text (if field in Main table is text)
    competitor...Text

    Enter all of the criteria


    Then, in VBA, (pseudo code)
    Open record set on table tblCriteria
    do while not EOF
    read fields
    execute the update query
    DoEvents
    move next
    Loop
    close record set


  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You don't run 1 query for each state and 1 query for each product totaling to 1008.
    you run 1 query that updates everything.
    tho I still don't know what you're wanting to do.

  5. #5
    tmcrouse is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    169
    There is one competitor that I am doing the ranking for in each measure, each state, each product. I know who that competitor is in each area. They are the only ones that are assigned a ranking. So, if they are 4 out of the 10 possible they get 4 out of 10. And the ranking came from my code that ranks them by their hedisrate. So that is where I got the ranking from in the first place.

  6. #6
    tmcrouse is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    169
    I need to tank the competitor where I have the name of that competitor by measureid, by product, by market. There are 36 measures in all, 2 different products, and 14 different markets. So I want the competitor name I was supplied with that gets the ranking 1 out of 10 or whatever to be the only 1 update. I was thinking I could just update the ranking for all so it would be like 1 out of 10, 2 out of 10, etc and then delete those where the competitor name is not the one in my list but then thought that is dumb. Why not just deal with the competitor that gets the ranking information.

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If you get the info to update in a text file/Excel, you could import to the criteria table.
    Attached is a demo - just one method to do the update.
    Attached Files Attached Files

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    so '4 out of 10'

    you are saying that for the state of CA there are 10 competitors for a specific product - HMO in your example. And they have a ranking which has been determined from the hedisrate which you have already ranked.

    If that interpretation is correct it can be done in one query

    qry1
    Code:
    SELECT measureID, competitor, market, prodabbrev max(Rank) as maxRank
    FROM tblData
    GROUP BY measureID, competitor, market, prodabbrev
    qry2 - the final result for all permutations
    Code:
    SELECT T.measureID, T.competitor, T.market, T.prodabbrev, T.hedisrate, T.Rank, TRank & " out of " & maxRank AS Ranking
    FROM myData T INNER JOIN qr1 Q ON T.market = Q.market AND T.measureID= Q.measureID AND T.prodabbrev=Q.prodabbrev AND T.competitor=Q.competitor
    ORDER BY T.measureID, T.competitor, T.market, T.prodabbrev, T.hedisrate

  9. #9
    tmcrouse is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    169
    Yes, this is actually right on the money with your interpretation. I used your query for the most part but made a few alterations. This is what I did and I did that because there is 1 competitor in each market, each product, and for each measure that I want to rank. 1 out of 10 or 2 out of 10 or whatever it is. Happens to be my org because that is all they care about. So I did it like this:

    <code.SELECT
    distinct measureID, market, prodabbrev,max(rank)as maxrank intocompranka
    FROM
    comprank
    GROUP
    BY measureID, market, prodabbrev;

    select
    distinct competitor, measureid, market, prodabbrev,RANKintocomprankb fromcomprank
    where
    competitor in('the names of the competitors','broken out that I only need','by market','by product','there are 28 of them in all because there are','14 markets','and 2 products');


    alter
    tablecomprankb add ranking varchar(25);

    update
    comprankb set comprankb.ranking =cast(a.rankasvarchar(25))+' out of '+cast(b.maxrank asvarchar(25))fromcomprankb a innerjoincompranka b on
    a
    .measureid = b.measureid and a.market = b.market and a.prodabbrev = b.prodabbrev;
    </code>

    Thank you so much for the help on this. By you putting this code out there it got my brain working.


Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-28-2016, 09:49 PM
  2. query between two dates and times
    By robnsd in forum Queries
    Replies: 5
    Last Post: 10-22-2015, 10:21 AM
  3. Replies: 2
    Last Post: 02-25-2015, 05:02 PM
  4. Replies: 3
    Last Post: 10-09-2014, 01:40 PM
  5. Query: How many times does a value exist?
    By hognabbt in forum Queries
    Replies: 1
    Last Post: 01-10-2010, 01:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums