calculating chemical content of paint products

[Guest]

I am trying to track purchases of paint products thoughout the year add the
quantities of each different product and calculate the VOC content. what
would be the easiest way to do this.....

 

[Guest]

by the way we are using access 2003 and we already track the products and I
already have the calculations with the products that i need to track set up
in excel, the problem is that my current method is very time consuming and I
would like to set the access database up so i could just query or run a
report or..........any suggestion would be great.

 

[Joseph Meehan]

I am trying to track purchases of paint products thoughout the year
add the quantities of each different product and calculate the VOC
content. what would be the easiest way to do this.....

If others here are like me, I don't know much about paint chemistry or
even what VOC (volatile OC ?) is. So I am not at all sure what you are
planning to do.

How about some samples like

Item 1 green indoor paint
water
green color
zinc oxide

And would each Item 1 use the same sort of stuff in the same ratios
Will you have more than one Item 1? etc.

 

[Ken Snell [MVP]]

I have Ph.D. in Chemistry and have worked in the paint business (automotive
OEM coatings). Perhaps I can assist you if you provide a few more details
about the manner and frequency for entering data.

 

[Guest]

We already have a database that tracks all of our purchases. What I need to
do is extract from that database only products that we use in our spray
booths, then I need a total Gallon amount for each of those products,
calculate the VOC totals for each product and add for an over all total.

I already have all of the MSDS informational sheets so I have the numbers
and calculations for the VOC (volatile organic content) set up in an excel
spread sheet - for instance:
Vendor Name UOM ID Description QTY MSDS GAL
LB/GL Total
Sherwin-Will Gallon 354 Acetone 15 Acetone-SW 15
0 0
Sherwin-Will Pt 3784 BleachLiteNWood 1 500N 1
0.125 0 Sherwin-Will Gallon 7226 Sanding Sealer 10 B44VJ101
10 5.53 55.3
Sherwin-Will Gallon 7883 Lacquer semi-glos 80 T77C30 80
5.46 436.8
Sherwin-Will Gallon 357 "Thinner, Lacquer" 35 K119-SW 35
5.32 186.2
Sherwin-Will Gallon 7493 Lacquer (Satin) 20 T77C30 20
5.46 109.2
Sherwin-Will Qt 9275 Stain whitewash 4 A49W207 1 4.38 4.38
Sherwin-Will Gallon 9275 Stain Whitewash 2 A49W208 2 4.38 8.76
Sherwin-Will Gallon 9216 Universal Dye 1 S61R503 1 6.09
6.09
170 294 122.8 911.44

so in my spread sheet I have, it already has all of the products that i
need to track. I enter the quantity of the order (6qts, 8oz, 1Pt.) and it
converts it to Gallons and multiplies that by the lbs/GL of VOC and I have my
total. The problem is that I need to track this on a monthly, quarterly, and
yearly basis for all of the material between 2 shops so it gets very time
consuming tedious and riddled with human error. so my goal is to automate as
much of the process as possible by using the database that we already have in
place I am just not sure what the best direction to go in access is.....at
this point we do not have any of the actual MSDS info in our database just
basic product info.

 

[Ken Snell [MVP]]

Let me recap....

You have a database (ACCESS?) that contains purchase information already.

You have an EXCEL spreadsheet that contains the data needed to calculate VOC
values for each purchased material, and that EXCEL file has formulas (or
something) to do the calculations.

You want to use the purchase data from the existing database, and also use
the data that are in the EXCEL spreadsheet to have a database do the
calculations for what has been purchased and what the VOC amounts for
varying time windows.

Correct so far?

Can you do this additional work in the existing database? Or do you need to
create a completely new database, and then get the data from the other
database to the new one?

What you'll need is to build tables in a database that will contain
chemicals and the data associated to each chemical:

tblChemicals (note: each UOM for a chemical means a different record)
ChemicalID
SupplierID
UnitOfMeasureID
SupplierChemicalID
ChemicalDescription
VOC_Content

tblSuppliers
SupplierID
SupplierName

tblUnitOfMeasures
UnitOfMeasureID
UnitOfMeasureName

The above tables will hold the "static" data that appears to be most of what
is in your spreadsheet.

You then would need a table for purchases:

tblPurchases
ChemicalID
PurchaseDate
PurchaseQty

You then can run a query that would claculate and sum data based on the
tblPurchases table (getting the 'static' data from the other tables by
"joins") for the desired time window.

Does this help?
--

Ken Snell
<MS ACCESS MVP>