Help with normalization


D

dan dungan

Hi,

I'm using Excel or Access 2000 on Windows XP.

The boss wants the data entered in a spreadsheet and then copied to a
separate sheet for each electroplating tank.

Request:

I’m unclear if this project may be better managed in a database.
Can you help me decide to use a spreadsheet or database?
If a database would work, I'd like feedback about normalizing the
table structure.
Also, I'd like help to see if there are other questions I need to ask
my boss.

Thanks for your feedback, Dan

Project Description:
I have a task to develop a spreadsheet for tracking the pH, Titration,
and Oz/gallon on plating tanks in our shop. The manager wants to see a
report that shows each tank by week. He wants to see if all the tanks
are operating in similar constraints. And he wants to see a yearly
report.

The boss is not available for questions--he believes he gave me a
simple task, and there should be no more questions.

Project Details:

We have several tanks of a cadmium solution--and other solutions that
I have no information on at this time--that we must test weekly. We do
this for two reasons. If the solution isn't consistent over time, the
plating quality can suffer, and there is a regulation for documenting
how we use the chemicals.

We need to test each tank for pH and temperature.

We test each tank for some chemical properties:

Here are the chemicals:
Cadmium
Sodium cyanide
Caustic soda
Cadmium oxide
Isobrite 541
Isobrite 542

Here are the properties:
Titration-this is a decimal number--like 6.3--that represents how much
of a chemical needs to be added to the tank to keep the proper
balance.

Oz/gal-this is a decimal number-like 1.3--that represents how much of
the chemical is in the solution.

After the test, we need to monitor the amount in pounds of each
chemical that was added to each tank


Proposed table structure:

tblChemistry
Columns
Name Type
ChemistryID Long Integer
Chemical Text
ChemicalElementsymbol Text
OptOz/Gal Single

Relationships One-To-Many
tblChemistry tblTest
ChemistryID ChemistryID

tblTank
Columns
TankId Long Integer
TankNumber Text
Size Long Integer
TankType Long Integer

Relationships One-To-Many
tblTank tblTest
TankId TankId
tblTankType tblTank
TankTypeId TankType

tblTankType
Columns
TankTypeId Long Integer
TankTypeDescription Text
OptTemp Long Integer
OptpH Single

Relationships One-To-Many
tblTankType tblTank
TankTypeId TankType

tblTest
Columns
TestId Long Integer
TankId Long Integer
TankTempuratureResult Long Integer
TankpHResult Text
ChemistryID Long Integer
SampleDate Date/Time
Vol Single
Oz/Gal Single
AdditionDate Date/Time
AmountAdded Single

Relationships One-To-Many
tblChemistry tblTest
ChemistryID ChemistryID
tblTank tblTest
TankId TankId
 
Ad

Advertisements

J

Jeff Boyce

Dan

Have you searched online to see if there's a template or an existing
application that does what you need?

Another consideration in building the "application" is who will be using it?
And what level of experience do they have?

For folks considering building an Access application, I point out that there
are at least 4 separate learning curves you'll need to work through ...
sounds like you may already have one down:
1) normalization/relational database design (Access expects normalized
data)
2) Access tips/tricks (how Access does it is not the same as how Excel
does it)
3) Graphical user interface design (if it isn't easy to understand/use, it
won't get used!)
4) application development (if you've never built a house, where do you
start?!)

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hi,

I'm using Excel or Access 2000 on Windows XP.

The boss wants the data entered in a spreadsheet and then copied to a
separate sheet for each electroplating tank.

Request:

I’m unclear if this project may be better managed in a database.
Can you help me decide to use a spreadsheet or database?
If a database would work, I'd like feedback about normalizing the
table structure.
Also, I'd like help to see if there are other questions I need to ask
my boss.

Thanks for your feedback, Dan

Project Description:
I have a task to develop a spreadsheet for tracking the pH, Titration,
and Oz/gallon on plating tanks in our shop. The manager wants to see a
report that shows each tank by week. He wants to see if all the tanks
are operating in similar constraints. And he wants to see a yearly
report.

The boss is not available for questions--he believes he gave me a
simple task, and there should be no more questions.

Project Details:

We have several tanks of a cadmium solution--and other solutions that
I have no information on at this time--that we must test weekly. We do
this for two reasons. If the solution isn't consistent over time, the
plating quality can suffer, and there is a regulation for documenting
how we use the chemicals.

We need to test each tank for pH and temperature.

We test each tank for some chemical properties:

Here are the chemicals:
Cadmium
Sodium cyanide
Caustic soda
Cadmium oxide
Isobrite 541
Isobrite 542

Here are the properties:
Titration-this is a decimal number--like 6.3--that represents how much
of a chemical needs to be added to the tank to keep the proper
balance.

Oz/gal-this is a decimal number-like 1.3--that represents how much of
the chemical is in the solution.

After the test, we need to monitor the amount in pounds of each
chemical that was added to each tank


Proposed table structure:

tblChemistry
Columns
Name Type
ChemistryID Long Integer
Chemical Text
ChemicalElementsymbol Text
OptOz/Gal Single

Relationships One-To-Many
tblChemistry tblTest
ChemistryID ChemistryID

tblTank
Columns
TankId Long Integer
TankNumber Text
Size Long Integer
TankType Long Integer

Relationships One-To-Many
tblTank tblTest
TankId TankId
tblTankType tblTank
TankTypeId TankType

tblTankType
Columns
TankTypeId Long Integer
TankTypeDescription Text
OptTemp Long Integer
OptpH Single

Relationships One-To-Many
tblTankType tblTank
TankTypeId TankType

tblTest
Columns
TestId Long Integer
TankId Long Integer
TankTempuratureResult Long Integer
TankpHResult Text
ChemistryID Long Integer
SampleDate Date/Time
Vol Single
Oz/Gal Single
AdditionDate Date/Time
AmountAdded Single

Relationships One-To-Many
tblChemistry tblTest
ChemistryID ChemistryID
tblTank tblTest
TankId TankId
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top