Share via

Microsoft Access: How to convert units of measurement for final calculation in Report

Anonymous
2019-09-18T15:35:01+00:00

Hello,

I have a report where I am reporting the following: Item, Quantity (specifies unit of measurement), Cost (per unit of measurement), and Total Cost.  From here on out I'm shortening Unit of Measurement to UOM.

My Report's fields look like this:

Item Quantity From Table1 UOM From Table 1 CostPerUOM from Table 2 UOM From Table 2 TotalCost

(If I created this report initially I would have set it up much differently; however this is what I have to work with.)

My issue is that the units of measurement for UOM From Table 1 and UOM From Table 2 do not always match because they belong to different tables.  Sometimes I get a unit of measurement per item being Liters in the same row as the cost per unit item being milliliters, for example.  This looks messy on a final report to present.

Question:  How can I convert all "CostPerUOM From Table 2" units to the same base unit of measurement from Table 1 for the same item?  Would I have to go back to the tables these numbers belong to and convert them to all the same base UOM or is there some VBA script or function in SQL I can use?

Basically, I want to go from this:

Item Quantity UOM Cost/UOM UOM TotalCost
Salt 400 Gram $0.40 Milligram $160,000.00

To this:

Item Quantity UOM Cost/UOM UOM TotalCost
Salt 400 Gram $400.00 Gram $160,000.00

Thank you!

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2019-09-18T16:28:05+00:00

Create a conversion table of three columns, the first being the UOM as used in Table1, the second being the UOM as used by Table2, and the third being the conversion factor of the first to the second UOM.  Base the report on a query which joins the conversion table to Table1 on the first column and Table2 on the second column.  Return the factor column in the query.

You can then use the factor to convert the values from Table2 to equate with those from Table1, returning the converted values either in computed columns in the query, or in computed controls in the report.

Where the unit in the first and second columns is the same in a row in the conversion table, the factor will of course be 1.

Was this answer helpful?

0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-09-18T22:08:11+00:00

    Would you be open to answering some questions if I run into any issues?

    Absolutely.  That's what we are here for.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2019-09-18T18:56:32+00:00

    Ken, I really like this idea.  I was working on a VBA script but I think I'll scrap it for your plan.

    I'll update with the results!  Would you be open to answering some questions if I run into any issues?

    Thanks much!

    Was this answer helpful?

    0 comments