Microsoft Access Table contains too many links to another table

I am building an Access database containing research data from various projects. The main table in the database is the Species table, which has SpeciesID as the primary key. Each species has many possible attributes starting with taxonomy eg. Class, Order, Family, Genus, SpeciesName, and continuing through many other attributes, which may or may not be recorded for each species. For each attribute, for each species, I need to record which project the data has come from (I have a Project table with ProjectID as the primary key), and the reference used (I have a Reference table with RefID as the primary key).  I have tried having a SourceID (links to project) and RefID (links to Reference) column after each attribute (or attribute group, when they are related), but this results in a very messy Species table, and too many links to the Reference and Project tables, well over the maximum possible in Access. I could split the Species table into smaller tables of related attributes, but I don't think will reduce the overall number of links to the Reference and Project tables greatly, as even within the taxonomic fields there may be multiple sources and references used with the one species. Each species can only appear in the table once. Is there an obvious design change I'm missing here? Any suggestions would be greatly appreciated.
mycenaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SheilsCommented:
You need the following tables:

tblSpecies (fldSpicesID,fldSpeicesName, etc.....)
tblSpeciesAttributes (fldSpeciesAttributeID,fldSpicesID,fldProjectID,fldAttributeID)
tblAttributes (fldAttributeID,fldAttribute)
tblProject(fldProjectID,fldProjectName,fldDate,etc...)
0
SheilsCommented:
The purpose of the reference table is unclear. If all it does is store a reference number for the project then delete it and add a fldReference to tblProjects.

If you need a many to many relationship between project and species attributed i:e a species attribute may result from data from various project then you need a link table with the following fields (fldProjectID,fldSpeciesAttributeID). Make both field primary key. They can both duplicate but the combination of the two can't duplicate. Then use this table to link tblProject to tblSpeciesAttributes
0
mycenaAuthor Commented:
Hi Sheils,

Thank you very much for your suggestions. I think you are essentially suggesting breaking the Species table down into a correlation table of tblSpecies and tblAttributes. So perhaps, following your suggestion:
tblSpeciesAttributes (fldSpeciesID,fldAttributeID,fldSpeciesAttributeValue,fldProjectID,fldRefID)

I have used correlation tables elsewhere in my database, but hadn't done so here because the attributes in the Species table have a range of data types. (Sorry, looking back I realize I didn't mention this earlier.)  Fields may be text, memo, number (long integer), or yes/no. Should I have 4 separate correlation tables, one for each data type? Or is there a better way to do this?

To clarify, the Reference table contains a list of all the references (eg. books, journal articles, websites, other databases) used when collecting the data. Each species/Attribute combination will be recorded as part of a particular project (ProjectID), and using a particular reference (RefID), so both need to be recorded. Each Project may use more than one reference, and each reference may be used in more than one project, but only one of each is recorded for each Species/Attribute combination, so if I can manage the multiple data types complication, I think you are right that a correlation table is a good way to proceed. Any further comments much appreciated! Thanks again for your time.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SheilsCommented:
With regard to the reference I think that you may need an additional table tblSpeciesAttributesRef (fldSpeciesAttributesRefID, fldSpeciesAttributesID,  fldRefID).

NB:- tblSpeciesAttributes should have the following fields (fldSpeciesAttributesID, fldSpeciesID, fldAttributeID, fldSpeciesAttributeValue, fldProjectID)


That's because you may use multiple references for one attributes.

The idea of having 4 tables for attributes sounds messy but it could work.  I need some more examples of what the attributes are ( I was weak in biology, more physic, maths, chemistry type of guy) to see if there is a better alternative.
0
mycenaAuthor Commented:
Hi Sheils,

Thank you once again, you are helping me think through the design in a different way. I still foresee some difficulties with the different data types - I am working through a redesign using your suggestions, as much of the data will work that way, and will then be better able to clearly describe to you the attributes which don't fit.

I have one set of attributes which will work much more neatly in the way you suggest, as they are all numeric, the only problem is that they each refer to a separate values table. Currently the number value is stored in the database. For example, the first field is Environment, and that is linked to the EnvironmentValues table, where 1 = forest, 2 = farmland, 3 = aquatic, etc. The next field is Impact, which is linked to the ImpactValues table, where 1 = economic, 2 = health, etc., and so on for a number of fields. They each follow the same pattern in that they are numeric, and require a project and a reference as discussed above - but in each case the numbers used are linked to a different table. Would it be best to change to storing the text instead, and using a dynamic lookup in a form for data entry, or is there a better way to cope with this? Getting them all into the one column as you suggest for attribute above would certainly help reduce the number of links.
0
SheilsCommented:
Instead of having a lookup table for each value type I would suggest converting that to a single table which has 3 fields.(fldAbbributeID,fldAttribute,fldAttributeTypeID)

fldAttributeType being environment, Impact etc... You'll also need a table for your attributeType with fields fldAttributeTypeID,fldAttributeType

Then You can use a query for your combobox row source to determine which attributes can be selected eg:

Select fldAbbributeID,fldAttribute from tblAttributes where fldAttributeType=1

I believe that this structure should be able to accomodate every attribute including the yes/no type. eg does the species have legs. fldAttributeType="Have Legs" then you have 2 possible attributes yes/no (-1/0).
0
SheilsCommented:
Instead of having a lookup table for each value type I would suggest converting that to a single table which has 3 fields.(fldAbbributeID,fldAttribute,fldAttributeTypeID)

fldAttributeType being environment, Impact etc... You'll also need a table for your attributeType with fields fldAttributeTypeID,fldAttributeType

Then You can use a query for your combobox row source to determine which attributes can be selected eg:

Select fldAbbributeID,fldAttribute from tblAttributes where fldAttributeType=1

I believe that this structure should be able to accomodate every attribute including the yes/no type. eg does the species have legs. fldAttributeType="Have Legs" then you have 2 possible attributes yes/no (-1/0).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mycenaAuthor Commented:
Hi Shiels,

So sorry for the long delay, I am not able to work on this full-time. However, I have been working on redesigning the database following your suggestions. So far it has been mostly working well - for the fields which follow the same pattern this is certainly a much neater way of storing the data, and has considerably reduced the number of links to the project and reference tables I need. And yes, indeed, the single lookup table for attribute values is a much better solution, so thank you very much indeed.

Really, my remaining problem is having to split each table into 3 or 4 separate tables according to data type. For example, the Species table contains attributes such as SpeciesName, Genus, etc., which are Text, and also a Status field, which is Yes/No. I could change this to a text field and store "yes" or "no", but it is nice to be able to display it as a check box - but it would seem the only way to keep it as yes/no is no have it in a separate table, with the same structure but a different data type. And similarly, some field are too long to be stored as text, and so require the memo data type, which then requires another table.  I also have a Country table, which requires the same link to a Project and a Reference for each attribute, and which has attributes with different data types, eg. CountryName (text) and GDP_per_capita and Area (numeric).

I have put these different data types into different tables, however it makes a very cluttered database - do you think I should be doing it this way? Or should I be condensing the data from all the big base tables (Species, Country, Country Occurrence, etc.) - which are organised this way currently because they share attributes - into one set of tables structured as you've suggested, with an extra column identifying the table the data is from, and thus have the table organised based on data type?

Many thanks again for your time.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.