My database currently stores fairly basic work order information for a stone company that fabricates marble and granite countertops, fireplaces, etc. The primary purpose actually relates to "slab control" or inventory. Anyway, the current entry for quantity used is direct input (with shop ticket in front of me and calculator in hand). It then does some simple math with quantity sold, etc.

I'd like the quantity used to be based on the slab sizes because that's what gets recorded on the shop ticket. I envisioned a subtable that records width and height of each slab (obviously number of slabs varies). Then I'd somehow get the total sq footage from that and plug it into my existing math. I'm not getting anywhere fast and I can't seem to find an example database that relevant.

This is my first real database of any consequence, using it as a guinea pig so you'll find a few other unclean things in there. But it's small enough that figuring it out should be pretty easy. Any help would be much appreciated.

SlabCtrl-test

Welcome to Utter Access!

I can't open your link as it is blocked here at work. You can attach databases directly here at UA. All you have to do is to run a Compact & Repair on the database, and then Zip it so that is less than 500kb in size and then attach it. There's a FAQ piece on the specifics.

Wow. That compresses really well.

Here ya go.

You're going to need to use an inventory model to do this right. You don't need separate fields in one record for each type of material, you need different records with a field to id the material type. You need to get that right before you worry about the related forms. There are lots of posts on the subject of inventory that you can find via the Advanced Search feature.

Fair warning: inventory is one of the toughest applications to get right, even for experienced developers. Not trying to run you off, just making sure you know that it's not an easy critter to tame.

(I mean this honestly, not sarcastically) Your response is disheartening and a little cryptic, but I will take your advice and do some more research. Thanks for the quick replies and pointing me in the right direction.

I'll be happy to elaborate if you'll explain what was cryptic to you. I certainly didn't intend it that way!

Looking back at my last post I have to admit a hint of childishness. I'm pretty stressed at work and this db isn't helping. I bet I'm the only one .
I thought that's what it already was. What's the difference between that and what I have?
I don't see what you see - I don't have material types defined. I have project types and material descriptions. Are you talking theory or what's already in there?
That's what makes researching it so difficult - nobody's inventory is the same. That makes it hard to find examples, which is what I opt for when I have trouble articulating my questions.

Thanks for taking the trouble to work with me on this.

Here is a good link on designing an inventory system.

You don't need multiple fields to store different quantities. There should be only one field containing the quantity with a field in the record to identify what type of quantity it is.

Then you would have multiple small quantity records describing a single work order record as opposed to one big quantity record related to a single work order record. Why is that better? (My tone is supposed to be friendly. It's frustrating communicating in forums effectively)

In regards to the link, were you pointing me toward the "quantity on hand" issue or the table structure in the sample? Quantity on hand isn't a goal - my first post is a little misleading. The quantities are most important for statistical analysis (salesman's bidding accuracy, etc) and having slab sizes helps to identify material lots used for certain jobs. They're supposed to be identified by the color spray-painted on the side but the shop isn't always consistent with it.

I understand from reading other posts just how absolutely crucial it is to have your database designed right from the start. I also understand so far that you're telling me my structure is crippling future functionality (or maybe just plain wrong). I'm more than willing to redesign if I could get a little more explanation.

Quantities are quantities. They may be different types of quantities, but they are all quantities.

Anytime you have multiple fields in one record containing the same basic data (in this case quantities), you have a structure problem, that is commonly called 'repeating groups'. What happens when you have to start tracking a different type of quantity? With you current structure you'd have to add a field to the table and modify every query/form/report that deals with the table. Properly normalized, with quantities in their own table, you'd only have to add a new record to the quantity type lookup table and then keep right on truckin' with your current forms/queries/reports.

Not trying to be overly critical, just trying to help you avoid problems that I've experienced first hand when learning this stuff, and learned quickly to avoid!