MS Access- use of subdatasheets 1

[osp001]

This is my first post here, so be gentle on me.

I'm trying to put together a database of formulations. These formulations use common components, mixed in different ratios. I've been banging my head on the keyboard all day trying to figure out how to get it all to work. Being fairly new to Access isn't helping.

The database consists of a table (no forms yet) with the BatchName being the key. It has some other characteristics associated with it (date of manufacture, type of formula, and so forth). What I'd really like to do is create a list of different components that go into the mix (CompoundName) that can be selected using a pull-down menu (which I've done), and have each component associated with a quantity of each compound that is weighed out at the time of manufacture. Because there are different formulas and there is some variation in weight, this number is not fixed.

I've tried to create a subdatasheet so that I can put this all together, but the subdatasheet keeps coming out the same for each table:

Mix A: 12 June, 2003 Epoxy Mix #47 Prep time 12 hours
Compound A 1650
Compound B 3.1
Compound C 165.0

Mix B: 14 June, 2004 Epoxy Blue #5 Prep time 6 hours
Compound A 1650
Compound B 3.1
Compound C 165.0

and so forth. Instead, I need it to look more like this:

Mix A: 12 June, 2003 Epoxy Mix #47 Prep time 12 hours
Compound A 1650
Compound B 3.1
Compound C 165.0

Mix B: 14 June, 2004 Epoxy Blue #5 Prep time 6 hours
Compound A 1400
Compound B 6.2
Compound C 98.2

Anyway- I'm lost as to how to do this. Any suggestions or recommendations would be welcome. Thanks in advance!

 

[alr0]

Hi Osp,

This sounds like a classic many-to-many relationship.

Add a table that lists each compound from which the formulations are made. (once only)

Then a third table will link each formulation with each compound and the quantity for that formulation. There will be one record for each compound that goes into a formulation. In your example there would be three records for each formulation or a total of 12.

By linking the data properly, you can create a form to display formulations and list all needed compounds and quantities. This can be done simply with a list box or with a sub-form that makes it easy to edit formulations.

Does that sound like an idea that might work for your needs?

alr

 

[tahilg]

alr,

Don't you mean "... or a total of 6"?

Gunny

 

[alr0]

Yes Gunny,

In writing my response I glanced back and saw four formulations but there are only two times three is indeed six.

Thanks,

alr

 

[osp001]

Wow. That fixed it!

I must have tried this half a dozen times last night, and couldn't get it to work. In the morning, I started over from scratch, and it seems to work fine.

Thanks!

 

[tahilg]

alr,

You is welcome, sir. Or is that madam?

Gunny

 

[JeremyNYC]

A couple of notes, now that you've gotten alr and tahlig's helpful posts:

You should probably do some reading on normalization. I've got a copy of Paul Litwin's Fundamentals of Relational Database Design on my website. There are many other excellent things to read out there. Something like this will give you a conceptual framework for getting to these solutions more quickly in the future.

And I would avoid, at all costs, using the subdatasheet feature. It snares new users and quickly establishes risky habbits (or is it habits? I can never get those two straight). Data entry should not be done through tables, only through forms. In a form you can build a combo box or a subform to do the work of a sub datasheet, and you will be able to quickly poke around in it and see what it what. Sub datasheets slow down data retrieval from a table. And they bring you further away from an understanding of what a table really is, and how it should be manipulated.

Check groups.google.com for discussion of these things on comp.databases.ms-access. Very few of the experienced folks have anything good to say about subdatasheets.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[tahilg]

Hobbits and habits.

Gunny