Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    denver1717 is offline Novice
    Windows XP Access 2000
    Join Date
    Oct 2012
    Posts
    16

    Translating Excel formula to Access formula (in query)

    Someone from excelforum.com configured a formula that forces a rank of 1-5 (the columns represent months, so a value of 1 through 5 is assigned to 5 out of the 12 months) based on percentages by month in a table.



    The Percentages by month table is the first table below. The second table are the resulting ranks that are configured using the following Excel formula in columns 01-12: =TEXT(COUNTIF($D14:$O14,">"&D14)+COUNTIF($D14:$O14 ,D14),"[>5]""0"";0")+0

    (The D would be month 01, and O would be month 12)

    Does anyone have an idea of how I go about setting up a query in Access using the first table (below) to get the results in the second table?

    Thank you for your help!!! (I tried to get the grid lines to copy over but it wouldn't work)

    Type Service 01 02 03 04 05 06 07 08 09 10 11 12
    IP Acute 0.0818 0.0846 0.0808 0.0872 0.0805 0.0796 0.0885 0.0768 0.0854 0.0853 0.0867 0.0828
    OP Dental Van 0.0918 0.1111 0.0966 0.0918 0.1014 0.0966 0.1063 0.1014 0.1014 0.1016
    OP NumDays 0.084 0.084 0.084 0.084 0.076 0.084 0.08 0.08 0.092 0.084 0.08 0.088
    Type Service 01 02 03 04 05 06 07 08 09 10 11 12
    IP Acute 2 1 4 5 3
    OP Dental Van 1 4 2 5 3
    OP NumDays 3 4 5 1 2

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,143
    That would be a rather long and complicated expression, probably too long and complicated. I think this will require a custom function in VBA. Review http://allenbrowne.com/func-09.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Ajax is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,140
    if the first table is created as a crosstab from a normalised table, then you can just use another crosstab based on a simple query to create the ranking . If the first table is how you actually have your data then it not normalised so as June7 says, you would require a long and complicated expression

  4. #4
    denver1717 is offline Novice
    Windows XP Access 2000
    Join Date
    Oct 2012
    Posts
    16
    Quote Originally Posted by Ajax View Post
    if the first table is created as a crosstab from a normalised table, then you can just use another crosstab based on a simple query to create the ranking . If the first table is how you actually have your data then it not normalised so as June7 says, you would require a long and complicated expression
    Ajax,

    My first table is an actual table and not a crosstab from another. I didn't set up primary keys, but if I had they would have been for the "Type" and "Service" columns. There are no duplicated combinations of "Type" and "Service" in my rows, though multiple rows share the same "Type".

    I had a feeling this would be too complicated for Access. I was stumped on how to write it in Excel. The data is spread percents for budgeting total units across 12 months of the year, and a lot of the percents are the same in each rows. Ranking them seems feasible, but figuring out how to rank only 1-5 and have the columns 01-12 mutually exclusive of values 1, 2, 3, 4, and 5 (with the remaining being zero) it beyond me. I was thrilled someone knew how to write it in Excel.

    June,

    I will reference your link. I have never worked with custom functions in VBA but I would like to learn.

    Thank you both for your help!

  5. #5
    Ajax is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,140
    If your table was like this

    tblData
    Type Text
    Service text
    Month Text
    PCents Double

    Type...Service...Month...PCents
    IP.......Acute.....01........0.0818
    IP.......Acute.....02........0.0846
    IP.......Acute.....03........0.0808
    ...
    ...

    it would be easy in Access

  6. #6
    denver1717 is offline Novice
    Windows XP Access 2000
    Join Date
    Oct 2012
    Posts
    16
    Ajax,

    I have my table in the format you gave 07-07-2015, 06:40 AM, how do I go about getting my ranks in a query? Again I want them to be 1, 2, 3, 4, 5 (no dups - which makes it difficult since multiple months have equal percents), with zeros for the other 7 months).

    Thanks for your help.

  7. #7
    Ajax is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,140
    I have renamed Type as ServType and Month as ServMth since Type and Month are reserved words. And I've called the table myTable. I've also treated month as text

    to produce your first table is pretty straightforward.
    Code:
    TRANSFORM First(myTable.PCents) AS FirstOfPCents
    SELECT myTable.ServType, myTable.Service
    FROM myTable
    GROUP BY myTable.ServType, myTable.Service
    ORDER BY myTable.ServMth
    PIVOT myTable.ServMth
    just working on your second query but need to clarify your rule - using numdays as an example, you have selected Months 1,2 and 3 for 3rd, 4th and 5th - why these as opposed to months 4, 6 and 10 which have the same value? And lets say the top value (0.092) was repeated twice - what basis do you use to decide which is first

  8. #8
    denver1717 is offline Novice
    Windows XP Access 2000
    Join Date
    Oct 2012
    Posts
    16
    I am going to work the code you wrote into my database! It is definitely more sophisticated than the SQL I write (which I copy out of Access SQL view and edit in Word and repaste to Acess).

    The purpose of the table you are helping me create is to remedy rounding of units when I do the monthly spread. I start with total units, and then spread total units using my monthly spread percents, rounding to zero decimals. I have found the max/min variance to be 5/-5 units to total units when summed across months. I then have a query where I allocate the units based on absolute value using the table you are helping me produce (I currently have a hard coded table that I compile off line using the spread percents and paste into Excel - but I am hopeful your table will work, because then it will automatically update if spread percents change). So I don't have a preference as to which month to use if a spread percent is in the highest 5(+) months and the same, but I need the ranks to be mutually exclusive (and only 5).

    Thanks again for your help!

    If it is helpful, here is my query allocating the unit variance to total units after applying my monthly spread percents. The table you are helping me build is Table_SpreadUnitVariance

    SELECT VolSprd_26_UnitVar.Service, VolSprd_26_UnitVar.Type, VolSprd_26_UnitVar.CC, VolSprd_26_UnitVar.DayType, VolSprd_26_UnitVar.VarUnit, IIf([VarUnit]=0,0,IIf(Table_SpreadUnitVariance![01]=0,0,IIf([VarUnit]>=Table_SpreadUnitVariance![01],1,IIf([VarUnit]<0 And Abs([VarUnit])>=Table_SpreadUnitVariance![01],-1,0)))) AS Assign01, IIf([VarUnit]=0,0,IIf(Table_SpreadUnitVariance![02]=0,0,IIf([VarUnit]>=Table_SpreadUnitVariance![02],1,IIf([VarUnit]<0 And Abs([VarUnit])>=Table_SpreadUnitVariance![02],-1,0)))) AS Assign02, IIf([VarUnit]=0,0,IIf(Table_SpreadUnitVariance![03]=0,0,IIf([VarUnit]>=Table_SpreadUnitVariance![03],1,IIf([VarUnit]<0 And Abs([VarUnit])>=Table_SpreadUnitVariance![03],-1,0)))) AS Assign03, IIf([VarUnit]=0,0,IIf(Table_SpreadUnitVariance![04]=0,0,IIf([VarUnit]>=Table_SpreadUnitVariance![04],1,IIf([VarUnit]<0 And Abs([VarUnit])>=Table_SpreadUnitVariance![04],-1,0)))) AS Assign04, IIf([VarUnit]=0,0,IIf(Table_SpreadUnitVariance![05]=0,0,IIf([VarUnit]>=Table_SpreadUnitVariance![05],1,IIf([VarUnit]<0 And Abs([VarUnit])>=Table_SpreadUnitVariance![05],-1,0)))) AS Assign05, IIf([VarUnit]=0,0,IIf(Table_SpreadUnitVariance![06]=0,0,IIf([VarUnit]>=Table_SpreadUnitVariance![06],1,IIf([VarUnit]<0 And Abs([VarUnit])>=Table_SpreadUnitVariance![06],-1,0)))) AS Assign06, IIf([VarUnit]=0,0,IIf(Table_SpreadUnitVariance![07]=0,0,IIf([VarUnit]>=Table_SpreadUnitVariance![07],1,IIf([VarUnit]<0 And Abs([VarUnit])>=Table_SpreadUnitVariance![07],-1,0)))) AS Assign07, IIf([VarUnit]=0,0,IIf(Table_SpreadUnitVariance![08]=0,0,IIf([VarUnit]>=Table_SpreadUnitVariance![08],1,IIf([VarUnit]<0 And Abs([VarUnit])>=Table_SpreadUnitVariance![08],-1,0)))) AS Assign08, IIf([VarUnit]=0,0,IIf(Table_SpreadUnitVariance![09]=0,0,IIf([VarUnit]>=Table_SpreadUnitVariance![09],1,IIf([VarUnit]<0 And Abs([VarUnit])>=Table_SpreadUnitVariance![09],-1,0)))) AS Assign09, IIf([VarUnit]=0,0,IIf(Table_SpreadUnitVariance![10]=0,0,IIf([VarUnit]>=Table_SpreadUnitVariance![10],1,IIf([VarUnit]<0 And Abs([VarUnit])>=Table_SpreadUnitVariance![10],-1,0)))) AS Assign10, IIf([VarUnit]=0,0,IIf(Table_SpreadUnitVariance![11]=0,0,IIf([VarUnit]>=Table_SpreadUnitVariance![11],1,IIf([VarUnit]<0 And Abs([VarUnit])>=Table_SpreadUnitVariance![11],-1,0)))) AS Assign11, IIf([VarUnit]=0,0,IIf(Table_SpreadUnitVariance![12]=0,0,IIf([VarUnit]>=Table_SpreadUnitVariance![12],1,IIf([VarUnit]<0 And Abs([VarUnit])>=Table_SpreadUnitVariance![12],-1,0)))) AS Assign12, [Assign01]+[Assign02]+[Assign03]+[Assign04]+[Assign05]+[Assign06]+[Assign07]+[Assign08]+[Assign09]+[Assign10]+[Assign11]+[Assign12] AS TotAll, [VarUnit]-[TotAll] AS ck
    FROM VolSprd_26_UnitVar INNER JOIN Table_SpreadUnitVariance ON (VolSprd_26_UnitVar.Service = Table_SpreadUnitVariance.Service) AND (VolSprd_26_UnitVar.Type = Table_SpreadUnitVariance.Type);

  9. #9
    Ajax is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,140
    OK well, just finished based on how you are selecting at the moment (i.e. earliest months first for same values)

    what you need to do is to make another table (I've called it myTable2) from the existing table - and note that each row in the original table needs an autonumber primary key (I've called it PK) so reocrds can be matched exactly.

    Note if
    1. all your values were unique for a given service
    2. or you didn't mind having '3rd equal' spread over (in the example quoted) months 4, 6 and 10 as well
    3. or you just wanted to know the top 5

    it would be much, much simpler and probably would not need another table made!

    Use this SQL to make the table - the bit in green calculates the rank.
    Code:
    SELECT myTable.PK, myTable.ServType, myTable.Service, myTable.ServMth, myTable.PCents, Switch([PK] In (SELECT TOP 1 PK FROM myTable As T WHERE Service=myTable.Service ORDER BY PCents DESC, servmth),1,[PK] Not In (SELECT TOP 1 PK FROM myTable As T WHERE Service=myTable.Service ORDER BY PCents DESC, servmth) And [pk] In (SELECT TOP 2 PK FROM myTable As T WHERE Service=myTable.Service ORDER BY PCents DESC, servmth),2,[PK] Not In (SELECT TOP 2 PK FROM myTable As T WHERE Service=myTable.Service ORDER BY PCents DESC, servmth) And [pk] In (SELECT TOP 3 PK FROM myTable As T WHERE Service=myTable.Service ORDER BY PCents DESC, servmth),3,[PK] Not In (SELECT TOP 3 PK FROM myTable As T WHERE Service=myTable.Service ORDER BY PCents DESC, servmth) And [pk] In (SELECT TOP 4 PK FROM myTable As T WHERE Service=myTable.Service ORDER BY PCents DESC, servmth),4,[PK] Not In (SELECT TOP 4 PK FROM myTable As T WHERE Service=myTable.Service ORDER BY PCents DESC, servmth) And [pk] In (SELECT TOP 5 PK FROM myTable As T WHERE Service=myTable.Service ORDER BY PCents DESC, servmth),5) AS Rank INTO mytable2
    FROM myTable
    then a simple crosstab, similar to the first one (as a process, you could run the first crosstab off the new table instead since it has the same percent values)

    Code:
    TRANSFORM First(myTable2.Rank) AS FirstOfRank
    SELECT myTable2.ServType, myTable2.Service
    FROM myTable2
    GROUP BY myTable2.ServType, myTable2.Service
    ORDER BY myTable2.ServMth
    PIVOT myTable2.ServMth

  10. #10
    denver1717 is offline Novice
    Windows XP Access 2000
    Join Date
    Oct 2012
    Posts
    16
    I knew it wouldn't be simple by the complexity of that excel formula - without your help I would not have any idea how to get the same results in Access - or even just ranks without the mutual exclusivity!

    Is there an autonumber function I can build in to my make table query for myTable2? I set up the make table query with PK blank, is there an expression to build autonumbers? If not I know I can do it by opening the table and adding the primary key there.

    (Thank you again!!!)

    SELECT "" AS PK, zzRankHelp02.ServType, zzRankHelp02.Service, zzRankHelp02.[01], zzRankHelp02.[02], zzRankHelp02.[03], zzRankHelp02.[04], zzRankHelp02.[05], zzRankHelp02.[06], zzRankHelp02.[07], zzRankHelp02.[08], zzRankHelp02.[09], zzRankHelp02.[10], zzRankHelp02.[11], zzRankHelp02.[12] INTO myTable2
    FROM zzRankHelp02;

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,143
    Instead of MAKE TABLE, suggest the 'temp' table is actually permanent but records are temporary. Run DELETE action then run INSERT action to clear and populate table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Ajax is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,140
    Is there an autonumber function I can build in to my make table query for myTable2
    No need, set the field type as autonumber and it will populate automatically. Don't worry about it being consecutive or always starting from 1 if you follow June7's suggestion - it is only required to uniquely identify the record.

  13. #13
    Ajax is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,140
    not sure what your maketable sql is - it's nothing like I suggested

  14. #14
    denver1717 is offline Novice
    Windows XP Access 2000
    Join Date
    Oct 2012
    Posts
    16
    I thought I had to add the primary key (pk) field to the table I currently have?

    I built out the table you suggested that is laid out as follows:
    Type...Service...Month...PCents
    IP.......Acute.....01........0.0818
    IP.......Acute.....02........0.0846
    IP.......Acute.....03........0.0808

    I so far have 3 queries built on that table:

    Query 1:

    SELECT MT_SprdMonCol.Type AS ServType, MT_SprdMonCol.Mon AS ServMth, MT_SprdMonCol.Service, MT_SprdMonCol.Percent AS PCents INTO myTable
    FROM MT_SprdMonCol;


    Query 2:

    TRANSFORM First(myTable.PCents) AS FirstOfPCents
    SELECT myTable.ServType, myTable.Service
    FROM myTable
    GROUP BY myTable.ServType, myTable.Service
    ORDER BY myTable.ServMth
    PIVOT myTable.ServMth;

    Query 3: (this is where I am at - trying to build query 2 into a table with a pk)

    SELECT "" AS PK, zzRankHelp02.ServType, zzRankHelp02.Service, zzRankHelp02.[01], zzRankHelp02.[02], zzRankHelp02.[03], zzRankHelp02.[04], zzRankHelp02.[05], zzRankHelp02.[06], zzRankHelp02.[07], zzRankHelp02.[08], zzRankHelp02.[09], zzRankHelp02.[10], zzRankHelp02.[11], zzRankHelp02.[12] INTO myTable2
    FROM zzRankHelp02;

    I thought I had to add the primary key before I could use the code you wrote to get the ranks.

    Also, I have never used a temp table, where do I select that when I am in design view?

    Ty both!

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,143
    A 'temp' table is just a table. It is called 'temporary' because the records are temporary and are purged at start or end of a process. The table is permanent.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 41
    Last Post: 05-29-2015, 02:58 PM
  2. Converting Excel Formula to Access Query Format
    By Guy Winfield in forum Queries
    Replies: 2
    Last Post: 05-11-2015, 06:21 AM
  3. Using an Excel Formula in Access
    By BEI77 in forum Access
    Replies: 6
    Last Post: 05-08-2014, 05:08 PM
  4. Excel Formula into Access Query
    By dr4ke in forum Queries
    Replies: 7
    Last Post: 06-25-2012, 06:46 AM
  5. Replies: 0
    Last Post: 09-03-2009, 01:58 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 - Senior Forums