chemistry question! (table construction)

[chemist]

I know, I know...chemistry. *yawn*. But, can you help a chemist out?

Here's the situation:

I work in a chemistry lab in which I routinely make up drug standards and reagents that are diluted, mixed, combined, and used in various procedures.
I have been tasked with making an Access database in which we will be able to better track/inventory/order/use/locate our various standards and reagents.
More than just locating items and counting units, I really want to be able to use this db to do some powerful calculations down the road. So, I'm really trying to get it set up right from the start.

Here's my problem:

For simplicity's sake, let's say I have two types of entities I need to track in the database: Chemicals and Reagents

Chemicals: a substance that is made up of one component.
(examples: sodium chloride 99%, aspirin 100%, or ethanol 100%)

Reagents: a substance that is composed of more than one component AND/OR (this is critical) reagent
(examples:[25% aspirin in ethanol], or [25% asprin, 75% tylenol, in ethanol])

Now, if all Reagents were always composed of just Chemicals, the table relationship would be pretty easy to set up as a one-to-many.
However, it does happen in practice that Reagents may not only contain individual Chemicals, but also previously made Reagents. They may contain just more Chemicals, but they could also combine any number of previously made reagents, and any number of novel Chemicals. Or, a reagent may be composed of only two or more previously prepared Reagents, with no Chemicals.

When I went to set up the tables and the associated data entry forms, I found that I was having difficulty with making a new Reagent with previously made Reagents.
Specifically, it wasn't a big deal to have each Reagent have a subform data entry that allowed me to populate as many Chemicals as I desired within it.

However, when it came to designing the form so that a Reagent could have a subform that pointed to another Reagent as a component of it, I ran into trouble.

Isn't it self-referential to have the Reagents table pull data for a drop-down from that same Reagents table?

Another option that was suggested was to create a third type of entity, some sort of "super-reagent" category, in which I would be able to add both reagents and chemicals in separate drop down fields. However, intuitively (which may be misguided), this strikes me as not elegant and imposes a structural limitation on the number of times a reagent may be used to create a new reagent.

Hopefully this makes sense!

Any ideas? Query? Totally different table setup? 50mg of morphine IV every 4-6 hours?

Many thanks.

edit:
I'm unsure if the thread located here (you'll have to modify the web addy as I'm a new user):
programmers.co.uk/forums/showthread.php?t=204515
contains a similar problem? Still can't quite get my mind around it.

 

[David Eagar]

A simplified answer: It sounds very much like the ERP system I used to work with (we made printing inks) either from raw materials our previously blended products)

All products went into an Item table and had various flags set (Purchased/Manufactured etc)

For the Manufactured Items, a bill of material was set up in a seperate table and any Item could be used in the selection.

The only circular reference that could occur is when you try and use an item within itself - A nice, healthy system crash then usually occurred !!!!

 

[chemist]

Thank you for the rapid reply, and may you stay above water!

Your ink situation does sound quite similar.

So, perhaps my error is in creating a separate table in the first place?
If everything simply resided in one table, then I would be able to pull the appropriately flagged/categorized data into a dropdown for subsequent reagent creation. I'll have to try this...in the meantime I'm definitely open for any other feedback or approach.

 

[The_Doc_Man]

Hi, chemist. My screen name reflects a PhD in chemistry. Let's see what I can do for you.

First things first. One table for your entire inventory. One table for each formulation that is a reagent. I'm going to gloss over some stuff by naming it generically, but here is my take.

tblInvItem:
- StockNum (either autonumber or whatever you are using for item tracking numbers) - primary key, MUST BE UNIQUE (of course)
- Item name
- Yes/No flag: Yes if reagent, no if simple chemical.
- gloss-over items such as location in stockroom (perhaps shelf codes?), hazmat codes, etc.

tblReagentFormula:
- StockNum (foreign key to tblInvItem) that you track this reagent under
- LineItem (ordinal number for this element of the formulation, see discussion for how to form this number)
** The combination of StockNum and LineItem will be this table's compound primary key
- Component (foreign key to tblInvItem) that tells you what item from the inventory is a part of this formula
- CompAmount (some measure of the amount of the component you use)
- gloss-over items including possible pointers to mixing instructions or special handling codes.

Now, now do you list this stuff?

Make a query of tblInvItem that does an OUTER JOIN with tblReagentFormula across the StockItem field. This query MUST use the NZ function heavily to supply blank strings for any nulls that it finds, because it WILL find them.

Now you can make a report (for example) where you do a report header on the entry from tblInvItem and the detail section then lists the line items for that stock number. Where the inventory item is a chemical and not a reagent by your definition, you can test that flag from the inventory item table and suppress the detail section for that particular header.

Now, how do you generate the line item number that restarts with each new inventory item? The next number in sequence is

1 + DCount("[StockItem]", "tblReagentFormula", "[StockItem]= " & CStr( [tblInvItem]![StockItem] ) )

(You might have to play with that a little bit.)

OK, inventory? There are a gazillion posts in this forum about inventory. Once you have a unique inventory number, all you need is a transaction scheme for each thing you can do that adds or subtracts stock. Your transaction tables might read:

tblTransAct
- XACTNum - primary key, autonumber (definitely) - and you can use this number for tracking purposes after the fact, maybe.
- XACTType - Shipment In, Stock Issued, Breakage/spillage, Inventory Adjust Up, Inventory Adjust Down, ArchivePoint
- XACTAmount - the number of bottles/jugs/containers of the identified item.
- XACTInvItem - the Stock Number of the identified item
- XACTWhen - the date/time of the transaction
- gloss-over of who got it, charge number (if applicable), etc.

Now, the XACTType: That is a code that leads to a table of transaction codes and signs.

tblXACTCodes:
- XCode: The code for this transaction, and you can choose a number or a single letter of the alphabet or two letters as you wish. This is the primary key of this table.
- XEffect: Either +1 or -1
- XACTName: The name you have chosen for this transaction.

Some possible entries in this table:
"SI", +1, "Shipment In"
"SD", -1, "Stock Draw"
"IU", +1, "Inventory Adjust UP" (used if you realize that there is an error that needs adjusting in the Gain direction)
"ID", -1, "Inventory Adjust DOWN"
"MS", +1, "Made Some" (implied: From chemicals on hand - but don't forget to do Stock Draw transactions on the individual components in that case).
"SH", -1, "Shrinkage" (used when you discover a broken or defective bottle
"AL", +1, "Archive Level" - used when you archive your transactions. You take the sum of all transactions for the given stock item and create the pre-archiving total. Then write that total as an archive-level amount and copy the old inventory records - then DELETE them. This is the record type that would allow you to maintain a connection to previous transactions.

Now, your inventory on hand is a summation query of a JOIN of the tblInvItem JOINed to the transaction table and THEN joined to the transaction codes table. If you are good with SQL you can do this in a single query. Otherwise, you can join a query to another query. What you want is a query that results in showing you the stock number, transaction number and amount and code and date, and the XEffect value - plus whatever else you want to look at in your transactions.

The summation query must be grouped by the StockNum of the tblInvItem and ordered by the transaction date. The thing being summed is the XACTAmount value times the XEffect value. If you do not constrain the date, then the sum of that computed field for a single inventory stock number is the number currently on hand. If you needed to know how many were on hand at a given date/time, the same query is used but only for transactions before the given date and time.

This is a lot to look at. Draw it out. You will need to understand relational databases and normalization. You will need to digest this for a while unless you are VERY familiar with database design. In which case you would not have asked this question, so I repeat, read this for a while and see what sinks in.

By the way, this is explicitly a case of "divide and conquer." By basing the inventory ONLY on the Stock Numbers (and assuring that reagents have unique stock numbers differing from chemicals), you keep the inventory part pure. Then the formula part rears its ugly head only when you need to see what is actually in a given reagent.

One more fine point. Suppose that you buy a reagent pre-mixed, know what is in it, and happen to not separately stock one or more components. To make this work anyway, assign a fake number to the components you don't stock separately and just never assign any transactions to the fake numbers. If they have no transactions under their separate stock item numbers, they never exist separately.

 

[chemist]

Eagar -
Could you be a little more specific on how the "Bill of Materials" table is set up and related to the Item Table?

Doc_Man - thanks! I am going to have to take some time to digest this info. This seems like a complete long term solution.

 

[David Eagar]

What version of Access are you using? I will knock something up

 

[chemist]

2007

...but I'm aware that there are some PC's on our network that are running 2003 (I'm trying to keep in mind the issues there...due to chemical analysis software limitations I can't upgrade the older PC's just yet)

 

[David Eagar]

Something like this - although I would highly recommend you follow the Doc's perscription, he is clearly aware of the subject matter and he knows a LOT more about Access than I do

 

[chemist]

Great!
I was able to use the Eagar-derived file to create a working DB that seems to perform the needed functions.

After reading over all the posts more closely, it would appear that both Eagar's and Doc_Man's approaches are very similar: one table that contains all items, and a separate table that contains information about how those items are related to each other.

I think I'll be able to use Eagar's file as a starting point for (sorely needed) educational purposes, and then incorporate/buildup the robust scheme of Doc_Man's. Make sense? For the moment, I lack cognitive dissonance. Thanks again.

 

[The_Doc_Man]

My solution will take time to develop. However, I emphasize and perhaps emphasize a second time: Study the heck out of normalization. That will help you to keep things isolated to where they belong. My solution is (I think) mostly normalized, though there are details to be considered in any broad-brush solution such as I proposed.

The good part is that you can work up the formulation stuff separately from the inventory part as long as you keep up that normalization.

 

[gemma-the-husky]

This is cerrtainly an area that will be worth spending a lot of time making sure the analysis is correct.

Make some tables, and play with queries - to see if the model works.

The other area worth looking at, maybe is industries that have parts "explosions"

Say - bathrooms, you have a stock item that is a bath, a toilet, a sink - but they also have a way of supplying a package that consists of multiple items.

 

[chemist]

Ok, so I've been spending all my scant free time reading up on normalization, making things atomic, and such.

I totally get the whole normalization thing now - and I have to say it is pretty cool! I've been building the "back end" of the database, including such things as departments, employees, employee titles, etc. Easy.
Now, I'm moving into the tougher area in which I need to set up tables to track the aforementioned chemicals/reagents.

After much reading, I find I'm still confused on hierarchical schemes: notably the "adjacency list" versus the "nested set" models.
I am thinking that the adjacency list should work OK for what I am proposing.
Agree?

Side question: I am facing a similar but a little different problem with the evidence i'll be examining:
This is more of a true parent/child situation;

If an item of evidence comes into the lab, I'll call it Item 1.
If I need to take a sampling of Item 1, that sampling becomes Item 1.1.
Another sampling of Item 1 becomes Item 1.2.
A sampling of Item 1.1 becomes Item 1.1.1.
A sampling of Item 1.1.1 becomes 1.1.1.1.
and so on......

Now -this is a little different than the BOM problem in that Items are NEVER combined to make up a larger whole. That would violate the integrity of evidence.
The Items are either destroyed during examination or left intact and returned to the contributor. However, it is important to be able to track lineage backwards.

I am thinking that there isn't any reason the adjacency list model wouldn't work for this as well?

As described in other articles, I fall into the category of understanding the adjacency list model a bit more intuitively than the nested set approach. I'm honestly not sure if I need to be concerned about the apparent performance differences between the two approaches. Addtionally, I am stuck using Access as opposed to another database for the forseeable future.

Thank you!

 

[jdraw]

Just reading this post from a casual interest perspective. Regarding the adjacency list, I found this info from r937 (Rudy), whose posts always seem clear and to the point. R937 is a true authority on matters of SQL.

I hope, if you haven't already seen it, that it is useful to you.

http://sqllessons.com/categories.html