Sub Ranking In A Query

[PC User]

I have two tables: tblChemicalProperties and tblHazardousComponents. I've been trying to give rank values to the hazardous components using the percent weight field. Each chemical should have its components ranked by percent weight. I've attached a prototype database to show you my situation. Can anyone help me on this?

Thanks,
PC

 

[David Eagar]

Does this fit the bill?

 

[CraigDolphin]

Well, if you post an AC2000 mdb format file I'd have a look at what you posted but I can't open ACCDB.

Some basic thoughts though.

From your post it seems that a 'chemical' contains one or more 'components'. Some of these components are considered hazardous and some are not.

Now, this is where things need to get a bit more specific.

Is the weight of a component always the same, and the percentage represented by that weight depends on what other components are also present in the 'chemical'? For example, a molecule is composed of atoms. Atoms have a fixed weight (Atomic Weight) but the percentage weight of a type of atom in a molecule depends on the number and types of other atoms that also are present in the molecule.

Or is it more like a cooking recipe where a chemical is analogous to a 'recipe' and the components are analogous to 'ingredients' where the quantity of each component in the mix varies depending on the recipe (chemical)?

My guess is probably the latter.

If so, then you're probably going to have a structure something like:

tblChemical
ChemicalID (pk, auto)
ChemicalName

tblComponents
ComponentID (pk, auto)
ComponentName
ComponentHazardous (y/n)

tblChemicalComponents
ChemicalComponentID (pk, auto)
ChemicalID (fk)
ComponentID (fk)
ChemicalComponentWeight_Percentage

Then, to obtain a ranking, you'd use a select query and a subquery to create a sequential number.

Have a look at the attached example to see how it works.

EDIT: Oops...David posted while I was working up the example. Sorry David.

 

[David Eagar]

Oops...David posted while I was working up the example. Sorry David.

No problem, you have added good info to improve the db

 

[PC User]

Craig,


Sorry that I forgot to save my db in A2K format. I'm currently running Access 2007 (A2K7). I've converted the file and its attached to this message.Thanks very much for your design. I can get some ideas from it. The tables were taken from an existing large database; so I can present my request. There are two tables and your sample has three. Should I make an intermediate table or query and populate them? See attachment.

David,

I see that you grouped and sorted my data, but I need to rank them too. Can this be done with the same table or do I need an intermediate table or query? I appreciate your help.

Thanks guys,

PC

 

[CraigDolphin]

Hmm. For some reason the file will not open in AC2K. Very odd. Did you by chance save it in AC2003 by accident?

In any case, logically, I do think you will need an intermediate table because you have a potential many-to-many relationship between chemicals and components. Several chemicals may share a particular component, and several components come together to form a particular chemical.

You /could/ avoid it by having a chemical table and a ChemicalComponentdetails table which violates table normalization rules by repeating information for components every time they are linked to another chemical. However, such a design is ripe for data integrity corruption (mis-spellings, etc) that will make your life miserable down the track, as well as increase your data-entry burden (why enter the same information more than once when you don't have to?) And it will also increase the filesize of your database which could be problematic if you have a large amount of data.

If you did go down that road (not recommended) you will note that the example I made has two queries. One essentially create the denormalized table that you would have. You would simply replace all the references to that query, which exist in the ranking query, with references to your denormalized table.

 

[PC User]

I'm just getting familiar with A2K7 after about 5 years of using A2K; so I guess I didn't convert the file right. I think I did it this time; so see the attachment.

As for the components table being denormalized, you're right. This is a small part of a large database that I seemed to have normalized the chemicals with the inventory, but got too busy to normalize the components. I'll have to make time later to clean that up, like you said. Thanks for pointing that out.

Since the tables are not normalized, right now some chemicals might have as many as 5 components and some may have just 2 components. They are all independently linked to their parent chemical through a key field ChemicalID. If there is repetition of components, they are not linked to more than one chemical. I need to get in the habit of normalizing my tables. Right now it could be a big task to straightening this out. Can you help me with the way I have it now and I can fix it later?

As for an intermediate table, would an intermediate query be just as good?

Thanks,
PC

 

[CraigDolphin]

Well, this time the format works fine so that's a step forward.

The problem you have goes beyond 'just' needing to normalize your table data. Although you definitely do need to do that. [And as proof of concept of what I was talking about, check out the spelling of the Chemical Name for HazardousComponentID's 75 and 111, or 472 and 372 or 186 and 86. Life in the denormalized world is a pain in the rear! ]

But the actual issue you absolutely must fix to accomplish your goal is that your % Weight field contains mixed data types and even multiple values. For ranking purposes, this field should be strictly a numeric field with only one value in it, or a text field also with only one value in it and strict rules about how that text value can be enetered that will allow for a meaningful sort.
Try sorting the following 'values' in an excel spreadsheet and see what happens:
01
1
<5
10-20
N/S
0 - 100
0.5 Max
<5%
Balance
14
014
160.
75-85

You can sort these 'values' but all you will get is going to be strictly alphabetical order, not increasing quantity.

I know you want a quick and dirty 'fix' but I'm not going to be able to oblige given this type of data corruption issue. A db is not a spreadhsheet, if the data you want to enter does not fit the box, then chances are that you have not designed the db correctly. Fix the box (or make more boxes) to accept all the data you wish to enter.

For example, you might consider that instead of your % Weight field you might need four or more fields and a separate table since you have potentially, multiple values per chemical component (in your current schema):
tblWeightObservations
-WeightObservationID (pk,auto)
-HazardousComponentID (FK)
-WeightType (Low-Range/Minimum, Average, Hi-Range/Maximum) (probably should store an fk integer linked to a WeightTypes lookup table)
-WeightValue (Numeric only)
-WeightValueQualifier (less-than, equals, greater-than) (probably should store an fk integer linked to a WeightValueQualifiers lookup table)

And then you need to structure a query to return only one of those values for each hazardouscomponentID. How you do that is likely to involve something like a switch function, or an iif function. This derived field is then going to be used as the basis of your ranking query. You'll also need to explictly handle greater thans and less thans since these exist in your data.

I really think you need to bite the bullet and normalize/fix the database structure or else accept your ranking goal issue is just one of the unhappy things you're going to have to live with.

You could also go through the data and edit it to provide a single numerical number manually, then convert the field to a suitable numerical type, then we could discuss how to proceed from there.

The only other thing I can think of is to write a vba module that will take the text string and parse it to provide a single numerical value for sorting. That's a task that I wouldn't try myself since the logic is bound to be tortuous at best and you need some kind of control over what the user input will look like for that approach to be successful.

Sorry I can't offer you better news than that.

 

[PC User]

Thank you for your wise advice. I see that there are problems to be fixed before going much further on this. Our temporary worker who entered the data into my fledgling database didn't know to warn me of the data changing type (i.e. from text to numeric). The ultimate purpose of this is to create a flat file to upload into a state agency's website. That is, this is part of a multi-step process that I'm working on to un-normalize my data for upload.

The components have to be on the same record as the parent chemical. Once I have ranked my data, I am to use a crosstab query to place the components into the records of the parent chemical and then link the results with the chemical inventory using the key field ChemicalID. An example of this flat file format is attached to this message.

This is a messy process, but I will be able to upload my data to the state's online database whenever an update is necesssary. As for now, I'll have to follow your advice and cleanup the data and normalize the chemical tables.

Thanks for your time,
PC

 

[gemma-the-husky]

ive not read all this thread in detail, but if a chemical is composed of hazardous components, it doesnt mean the compound is still hazardous does it?

ie sodium plus chlorine (surely hazardous) = table salt

or am i missing something here

---------------
with regard to table design

in craigdolphins structure, he has

tblChemicalComponents
ChemicalComponentID (pk, auto)
ChemicalID (fk)
ComponentID (fk)
ChemicalComponentWeight_Percentage

rather than storing the weightpercentage here, surely you should go back to first principles and store a periodic table, with weights attributed to each element, and use that to derive the weight of any compound, and the elements included within it. After all the periodic table is the ultimate denormalised (atomic!) data structure.

eg the weight of 1 atom of oxygen will be the same, whether its a free gas, or contained in a molecule of copper sulphate.

by the same token given a quantity of copper sulphate, you KNOW what the actual content of copper, sulphur and oxygen is, based on the relative weights in the periodic table.

 

[PC User]

Sorry if I'm confusing when I'm using the term "Hazardous Materials". This is actually a regulatory definition with reporting requirements. I see some responses to my posting with reference to atomic weights etc.; however, state regulatory agencies have interpretations that are more on the user level rather than the scientific level. The scientific information is used as supporting information to develop the regulations, but the reporting information requirements are for fire fighter's who respond to emergencies. Emergency responders are generally more interested in the user information rather than the scientific analysis. This is the perspective inwhich I am working with in this database. For a little more background, here is some more information.

California Health & Safety Code
http://www.leginfo.ca.gov/cgi-bin/waisgate?WAISdocID=16930923587+0+0+0&WAISaction=retrieve

California H&S Code Section 25501 (o)

"Hazardous material" means any material that, because of its quantity, concentration, or physical or chemical characteristics, poses a significant present or potential hazard to human health and safety or to the environment if released into the workplace or the environment. "Hazardous materials" include, but are not limited to, hazardous substances, hazardous waste, and any material that a handler or the administering agency has a reasonable basis for believing that it would be injurious to the health and safety of persons or harmful to the environment if released into the workplace or the environment.

California H&S Code Section 25501 (f)(4)

The most recently submitted annual inventory form contains the information required by Section 11022 of Title 42 of the United States Code.

Department of Toxic Substances Control
http://www.dtsc.ca.gov/

In early 2009 the online database to which I am referring, will be turned over to Cal-EPA for local agencies to share information.

California Environmental Protection Agency
http://www.calepa.ca.gov/
http://www.calepa.ca.gov/LawsRegs/

California Code of Regulations
http://ccr.oal.ca.gov/linkedslice/default.asp?SP=CCR-1000&Action=HOME

You're right that not all components of a hazardous chemical are hazardous and as my data collection for our reports to the administering agencies improves, I can rank the chemicals not only by their % weight, but also how hazardous they may be. Your input is valuable for improving my reports. I sincerely thank your for your responses and help.

PC

 

[CraigDolphin]

The ultimate purpose of this is to create a flat file to upload into a state agency's website. That is, this is part of a multi-step process that I'm working on to un-normalize my data for upload.

The components have to be on the same record as the parent chemical.

I face a similar issue with exporting water quality data to an EPA database. However, don't forget that your database tables can be perfectly normalized and you can still export a flat-file structure simply by making a query that builds your flat-file structure on-the-fly. It is just as easy to export a query as it is a table.

rather than storing the weightpercentage here, surely you should go back to first principles and store a periodic table, with weights attributed to each element,

I would concurr with this if 'chemical' were always just a synonym for molecule, and component were a synonym for atom/element, as in your Sodium Chloride example. However, in this case it looks to me that 'chemical' is a synonym for a recipe of 'ingredients'. Some ingredients may be elemental, but some may also be molecular. For example, he lists a chemical name as 'Shield Gas' which has two components which are Argon (elemental gas) and Carbon Dioxide (a molecule). Or the 'chemical' 'Parts Cleaner' which is a cocktail of Methyl Alcohol, Toulene [sic], Xylene, Methyl Ethyl Ketone, and Methyl Isobutyl Ketone. All of which are compounds/molecules.

Which means that to carry through your periodic table/atomic weight scheme you'd also need to start tracking the atomic composition of all ingredients (which may be molecules or elements). Not impossible to do, but it would certainly add a new level of data entry burden. Of course, it would be really nice solution to determining the relative weight for each ingredient (component) in the recipe (chemical).

The issue might arise that some chemical ingredient exact structures could be trade secrets and the weight then difficult to calculate. If so, you might be left with only being able to use manufacturer supplied weights for each ingredient such as <5%

Whatever happens, PC User has to sort out his data to provide a single numerical value which he can then use to form the basis of his ranking.

 

[gemma-the-husky]

craig, i see what you mean about something described as shield gas, especially if the legal regulations permit variable mixes

i would have thought if you were bothered about specific weights, then analysing molecular formulae would still be apposite.

However if its just something more like general info, (in UK we have COSHH - Control of Substances Harmful to Health) and manufacturers have to issue suitable product data sheets where appropriate, then I agree we are talking about something different.

 

[PC User]

Yes, in the US we call them MSDS or Material Safety Data Sheets which have chemical properties and chemical safety information. All the chemical properties necessary for reporting are contained there. See attachment. Also attached are the forms that I use for one of the agencies. Look at the "Chemical Information" form which has the fields that I use in my database.

~~ PC