Opinions on slick ways to design database

[carpet]

I am trying to construct a database which tracks experiments. The aim is to log all the equipment used and track which variables were selected such that the information can be searched through later and the results (saved on the network) can be found.

I have the following information needed.

-Experiment Name (each is unique)
-What is being tested (usually a material such as aluminium, iron etc)
-What aspect of the material is being tested (usually a nice definable aspect such as strength)
-What equipment is being used (bunsen burners etc)
-Dates
-Facility
-Where the results, analysis, financial approval docs are (hyperlink to folder)

The user needs to be able to search using one term in several fields in case the search term has both been used in 'being tested' and 'equipment' for example.
I also need to allow search using multiple terms with and/or options. It would even better if I could offer lists of possible equipment and their related properties seperately.

I've come with a few designs (some have serious flaws though and some are just clunky and not user friendly) but maybe someone could offer me something something snazzy instead of the crap I've produced!

Am unfortunately unable to offer example data so hope that the info I've provided is enough.

Thanks

 

[Thales750]

It sounds, to me, like the cart is in front of the horse.
I think the questions you have are too general for posting on a forum. I may be wrong but we’ll see if anyone wants to write a complete manual on Access.
First if you do not already understand normalization. Google it.
When you have normalized tables,or you are stumped by that process, come back and ask how to make that work.
Good Luck.
You will find much in the way of career saving advice on this forum, but as with all aspects of life no pain no gain, or as my grandfather used to say: “you can give a man a fish and feed him for a day, or you can teach him how to fish, and feed him for a life time.

 

[The_Doc_Man]

Oddly enough, search this forum for some topics such as Recipes and Restaurants because some of your experiments are "cookbook" in nature. No, I'm not kidding.

You have an experiment. You have ingredients and utensils. Some utensils will almost surely not matter i.e. when you stir a pot with a #5 spoon, you don't care which spoon. When you cook over a burner, you don't care which burner (but might care about size). The same will be true for some of your equipment, but not all. I'll bet that you don't care about which of 20 identical bunsen burners you use, but you might worry about which of several hydraulic presses you use for shear-strength tests.

So what I would do is build a list of experiments and give each a synthetic key as prime key. Then a list of equipment, each with synthetic PK. And a junction table that lists experiment, equipment ID, and quantity of equipment (like, maybe 4 x Erlenmeyer flasks 500 mL). Then a list of chemicals or consumables as appropriate, each with synthetic PK, and a junction table listing experiment, consumables, and quantity.

Then maybe a step-narrative that has the experiment ID, an ordered list of step numbers, and the text of that step. You would need to look up how to generate a repeating secondary ID for the step table, one that restarts at 1 for each new experiment. That has been addressed in the forum under the topic of non-standard autonumbering.

Anything after that, like a particular quantity of testing material (i.e. to test shear stress on a steel beam repeatedly, you will need at least three such beams of specific dimensions, etc.) could be in another table dependent on the experiment.

Now tie it all together in a set of embedded report/sub-report structures where the experiment info is the header/footer data and you tie together several sub-reports in the detail section bound to the experiment number.

Forms? Now there, you need to look at a RESULTS table where you link to experiment number, but perhaps have a different PK because, of course, experiments can be repeated. The experiment would have a PK of its own (synthetic is as good as any), number of trials, the date, the procedure used (tying back to the experiment procedure table), and a child table that shows the results of each repetition on the samples.

That should be enough to get you thinking along what you can do with such a design.

 

[Atomic Shrimp]

as my grandfather used to say: “you can give a man a fish and feed him for a day, or you can teach him how to fish, and feed him for a life time.

Completely off-topic, but I like the Terry Pratchett take on this:

Light a fire for a man and he will be warm for a day. Set a man on fire and he will be warm for the rest of his life.

 

[dcb]

Light a fire for a man and he will be warm for a day. Set a man on fire and he will be warm for the rest of his life.

Not bad for a nuke power press officer hey? - Maybe we should have a Pratchett thread!

 

[carpet]

Thanks so much for your reply, Doc man

I had managed to get pretty far into a design with sub forms, junction tables and forms before stalling due to some problems I could just not get around. I'll look into some of the other functionality you've mentioned and see if I can't get further.

Cheers!

 

[The_Doc_Man]

Light a fire for a man and he will be warm for a day. Set a man on fire and he will be warm for the rest of his life.

However brief, unless someone puts out the fire quickly, of course...

 

[Scooterbug]

However brief, unless someone puts out the fire quickly, of course...

Unless you use White Phosphorus or Thermite....

 

[carpet]

Have been stumped by something silly again...

Following doc's recipe analogy (which really helped me think through this btw!) how do I go about searching for recipes by ingredients? I need the option to search by one, multiple with OR and multiple with AND.

I've played with filter by form (which I'm not a fan of but seems to be the recommended easy search solution -I'm more than willing to go another way if someone can point me in the right direction!) and can't get the right expression for getting the AND search.

Appreciate your help and patience.