Calculations with Units

[js22]

Hello,

I'm creating a database for a laboratory that I work in to keep track of our chemical inventory.

Each year we have to print reports of our hazardous chemicals and an approximate amount of what we have in stock. My problem deals with the different number of units that we use. Mostly its grams, milligrams, or kilograms. (or liters or milliliters for liquids)

Is there anyway to have a clause in the calculation to look at the units and multiply it by a factor? Since we only use metric units we should be able to just multiply by a power of 10. I've attached what I've come up with so far.

I would appreciate any help into this problem

thanks

js22

Amount_Summary.fp7.zip

[sbg2]

Something like the attached?

z_Amount_Summary.zip

[js22]

Thanks, This is what I was looking for. I think instead of kilo_units I will just put everything into grams and milliliters. That is the units the bulk of our chemicals are reported in so there will be less calculations.

Thanks,

js22

[js22]

Hello again,

I ran into a snag. Your data conversions worked great and I incorporated that into my database, however I'm now having some problems summing.

I have a field that displays the total amount of a supply that we have in stock. On the first template you gave me it worked great. But when I applied it to my database I found the problem. It sums up the amount we could order, not how much we have in stock. (I've attached another template to help you visualize.)

My database has three tables: Supplies, Order Info, and Inventory. "Supplies" is for vital information about the supply (molecular weight, safety ...) "Order Info" is where we get it from and how much it costs and the most important part is how much we get. "Inventory" show what we have in stock and where it is stored.

The count of how many containers we have in stock for the supply works great. However the total amount is not calculating how much we have in stock but the amount we would have if we had 1 in stock from each order ID.

I don't know how to go about this. My thought was to somehow do a count to figure out how much of order ID@ we have for the substance and then multiply it by the standardized units but I don't know how to do that.

If anyone has any ideas I would be most grateful. If you need any clarification I will get back to you as soon as I can. Hopefully the template will help.

Thanks

js22

Amount_Summary_2.fp7.zip

[js22]

In case anyone else has the same problem, I figured it out.

I had to do a series of calculations:

1) calculate how many of each order id you have.

2) multiply the standardized amount by the count of the order id and store as total order amount

3) Under the supplies I count up the total order amounts for every order of the supply.

If you need any clarifications take a look at the template or post and I'll help out.

Also if anyone knows and easier way let me know as well.

Thanks

js22

Amount_Summary_3.fp7.zip