How to set up relationship query for minerals database

  • Thread starter Thread starter Jerry Whittle
  • Start date Start date
J

Jerry Whittle

You need a third table named something like Min_Location to break up this
many-to-many relationship. This type of table is know as a joining, linking,
or bridging table. The table would have a minimum of two fields which would
be foriegn keys to the primary keys in the other table. For example:

COM_MIN_ID MINERAL_ID
123 1286
123 257
123 44
555 1286

The combination of these two fields should be a unique index to prevent
duplicates.

With the above table you could as questions both directions such as what is
at COM_MIN #123 and where can I find Gypsum. You could also do away with the
current Com_Min column.
 
You are correct that it would be better to have each mineral for the main
table in a separate record.

HOWEVER, you can join to Com_Min using

SELECT MT.ID, MT.Lat, MT.Long,
M.Mineral, M.Density, M.Streak
FROM MainTable as MT INNER JOIN Minerals as M
ON ", " & MT.Com_Min & "," LIKE "*, " & M.Mineral & ",*"

This would probably be slow and could be inaccurate if there were any cases
where the list of minerals in the MainTable were not separated by exactly a
comma and a single space. Of course that could be fixed by manipulating the
field in the main table to ensure that was the case.

The real fix would be to split out the main table into multiple records.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Good morning everyone,

I'm using Access 2003 and am working on a minerals database.

The main table has a field COM_MIN (common minerals) which shows the minerals
occurring at a particular location e.g.

ID Lat Long Com_Min
123 -27.5 148.66 scheelite, gypsum, galena

My next table has individual minerals and their properties

ID MINERAL Density Streak etc
257 Scheelite 7.2 Gray
1286 Gypsum 2.6 White
44 Galena 6.7 Gray/black

There are over 16000 entries in the database. I would like to relate the
minerals to the main database so that the report contains Lat, Long, Mineral
and its properties.

Any suggestion how I can relate these two tables.

My thoughts are that I can't and would have to rename the field COM_MIN to
MINERAL and convert (for example) ID 123 in the Main table
to three entries , one for each mineral occurring at that location.

Any help appreciated.

Neville
Cootamundra
Australia
 
Back
Top