none
Trying to create percentages in access report (not as easy as it sounds look below) RRS feed

  • Question

  • Hey Everyone,

                   I have a report in Microsoft Access that is giving me a headache.  I am trying to find the percentages of a total amount but let me lay out the information and give you an idea of what I am trying to do.  The report looks like this when I am done before the calculations are in it.

    Room#        Asset#           Description              Activity at Sample Time                 CFUs

    123               1234             Water Bath             2 media bottles                              0

    321               4321             DI Water                 Run for 1 minute                            2

    123               1234             Water Bath             5 media bottles                              2

    What I am trying to do is get the percentage of times the CFU count is above zero for each description.  Example for Water Bath would be 50% but for DI would be 100%.  I know I need a formula for each but I cannot come up with a formula that does not read error when I am finished.  Please help.

    Wednesday, August 12, 2015 6:06 PM

Answers

  • Hi. Are you saying the first record above (water bath) should also show 50%? If so, you should be able to use DCount() expressions (I think).
    Wednesday, August 12, 2015 6:23 PM
  • If your report is dependent on a query, you can place the following expression in the query as one of the columns, or you can add a control to your report and insert it as the control source. 

    =DCount("<RecordID>","<Your Table Name>","CFUs>0 AND Description" = & [Description]) / DCount("<RecordID>","<Your Table Name>","Description" = & [Description])

    DCount has these arguments

    • Expression (Any field name)
    • Domain (The table name)
    • Criteria (One or more SQL WHERE conditions)








    Wednesday, August 12, 2015 6:27 PM

All replies

  • Hi. Are you saying the first record above (water bath) should also show 50%? If so, you should be able to use DCount() expressions (I think).
    Wednesday, August 12, 2015 6:23 PM
  • If your report is dependent on a query, you can place the following expression in the query as one of the columns, or you can add a control to your report and insert it as the control source. 

    =DCount("<RecordID>","<Your Table Name>","CFUs>0 AND Description" = & [Description]) / DCount("<RecordID>","<Your Table Name>","Description" = & [Description])

    DCount has these arguments

    • Expression (Any field name)
    • Domain (The table name)
    • Criteria (One or more SQL WHERE conditions)








    Wednesday, August 12, 2015 6:27 PM