Help! Many to Many relationship

[Bursal]

Can someone please help me with a small database with many to many relationships. See my relationship.jpg for the design so far.


I have been asked to create a small database for a Landcare community group. It contains data about Regional Ecosystems (RE) and the three classes of plants species related to the Res. There are many REs that have many species and many species fall into different classes depending on the RE. I have got as far as creating the tables and adding data to tblRE and tblSpecies tables. I can’t work out how to put the species into the various REs and their classes. There are two searches required a search on REs to display the species in their classes and a search on Species to display the REs they relate to.


Thank you for reading my post and I hope you can help.

Di

 

[jdraw]

Why is SpeciesId in tblRE?
Where do Classes fit?

 

[Bursal]

Thanks Pat
I will remove those fields and have a good look at your ManyToMany database and if the brain fog doesn't clear I will be back.
Di

 

[Bursal]

Sorry jdraw I forgot to answer your questions in the post.

The SpeciesID is in tblRE because when I started that was for a one to many relationship before I realised I needed a many to many relationship, so in reality it is not needed now I have a join table.

The classes is just a way of describing the dominant, subdominant etc species fields.

Hope that is clearer
Di

 

[jdraw]

Thanks for getting back.

For clarity, your tblRE is all about and only about your REs. Similarly your tblSpecies is only about Species.

The intersection/join table is about Species in a specific RE.


This link points to info on relationships:
http://www.techrepublic.com/article...relationships-between-database-tables/5034792

Good luck with your project.

 

[Bursal]

I am having trouble with the subforms on the RE and Species forms. The two main forms mfrmREI_nfo and mfrmSpeciesInfo and are fine apart from formatting changes. I am having trouble designing the queries for the subforms.



The forms and subforms are for data entry and searches.


On frmRE_Info there needs to be a tabular subform that displays the relevant species in one of the four vegetation categories - Dominant, Subdominant, Optional and Ground Cover. It is preferable to use a combo box to enter the species to make it easier to add data and to avoid spelling mistakes.



On frmSpeciesInfo the subform needs to display the relevant REs.



I hope this makes sense and you can help me. Thank you reading my post.
Di