Where to start!

[spondulacks]

Hi guys,



I have never used Access before but now find myself in a situation that it seems only Access can get me out of. My primary goal is simply to express and understand some data; basically all the effects of a particular cause, and simultaneously all the causes of a particular effect (for maybe 40-100 causes and 50-200 effects). As far as I can tell Access will be the best way of displaying/manipulating this data without a massive amount of repetition. There are some additional nuances but at it's core this is really all I need.

I spent some time reading/watching Access database tutorials but my need is so specific that they weren't very helpful and my unfamiliarity with the program makes it very hard to know exactly how to express my question. My main job means I travel a lot and only get a few days here and there to devote to this project so my time is very limited, only a few days a month at the moment. Can anybody help me find where to start with this. Ideally a template that matches my database, or at least somebody explaining how to express this particular basic relationship so that I can begin to fill out the data. I looked at many-to-many relationships but it was usually giving examples of students enrolling on courses or shop stock databases, things that did not help me with the specific type of database I need.

More details can be given if needed,
Thanks
S.

[June7]

Relational database concepts are the same regardless of what your data is. Identify your data entities and how they relate and build tables. Doesn't matter if you use Access, Oracle, SQLServer or some other database platform, the same concepts apply.

[spondulacks]

Relational database concepts are the same regardless of what your data is. Identify your data entities and how they relate and build tables. Doesn't matter if you use Access, Oracle, SQLServer or some other database platform, the same concepts apply.

Sorry to clarify I've never used any database software. I'm a total beginner, and would be with any of these programs. All i want is to be able to represent the data described above in the shortest amount of time as my interest isn't so much the database (although that will be very useful and potentially interesting) but finding a way of collating and understanding the data I have so that I can find out: if I *have* a, it will lead to xyz, or if I *want* z it will be caused by abc (with necessary additional details). A template that I can edit to do this, or an explanation/tutorial that will show me how to do this should be sufficient.

[spondulacks]

Googling for this gave me the term "many to many relationships", but further investigation just led to dead ends. As my need is so simple I thought maybe somebody on this forum could help explain it to me or at least point me in the right direction!

[June7]

Sounds like many-to-many.

TableA

TableB

TableAB - junction table that relates items of TableA and TableB.

Filter TableAB for a TableA value to see associated TableB items.

Filter TableAB for a TableB value to see associated TableA items.

This assumes there are standardized sets of TableA and TableB items.

Doesn't matter if TableA is students or causes and whether TableB is courses or effects. Same concept.

If this model does not fit your data then your need is not so simple.

[spondulacks]

Thanks for your response. If I'm a bit more specific perhaps you could help me figure which data to put in which tables. The project is to create a database of various supplements (proteins & amino acids, compounds, extracts, etc) and show effects they have and the degree of that effect, as well as dosage, duration of effect and other substances that should be avoided due to bad combinations. So for example, L Carnitine: decreases amonia to a notable degree; increases sperm quality to a notable degree; has a minor effect on decreasing blood glucose, and so on. It should also give some basic information about what L Carnitine is, how much to take, what other supplements to avoid if taking it. As a result I want to be able to produce a form which shows me, for example, all the supplements that decrease amonia and list their effectiveness, their dosage, etc.

Ideally I would quite like a second stage where I can group effects, for example a diabetic would want all the supplements that: a) increase insulin sensitivity, and b) decrease blood glucose levels. Meanwhile a weight lifter might look for effects that increase endurance, increase cardio performance, and reduce muscle damage, etc. However if this is too complicated I can still do it manually, so this bit isn't as important.

I'm not really sure which of the above would belong in the same tables. To reiterate, I think this is the information I need:

Supplement name
Dosage of supplement
Supplements not to mix with
Effect
Duration of effect

Is this still a two table database? Should I list them as follows:

Supplement_table
ID
Supplement name
Dosage
Not to be mixed with

Effects_table
ID
Effect
Degree of effect
Duration of effect

I'm a bit confused as to how to fill this out. For example say I find a supplement and all the effects it has, would I fill this in using the third table?

[June7]

Many-to-Many relationship requires 3 tables. A table of supplements and a table of effects and a junction table that relates the two.

I don't have a medical background but seems to me effects can be influenced by gender, age, weight. So if those factors are not taken into consideration, data structure so far is quite simple.

Data queries can get a bit complicated. Say you want to find any supplements that have 3 specific effects in common. Such a query might be like:

SELECT tblSupplements.* FROM tblSuppEffects INNER JOIN tblSupplements ON tblSupplements.ID = tblSuppEffects.SuppID WHERE EffectID IN (2, 5, 11);

That query could also include a join between tblSuppEffects and tblEffects so the effective descriptive info would be available.

And if you want to organize data by the type of user, that means more tables - tblUsers and tblUserEffects or tblUserSupps.

Your data entities at this point are: Users, Supplements, Effects.

[orange]

You may get some insight and ideas from this link re database design and planning.