Having trouble creating relationships.

[qwerty]

delete post

[June7]

What field would have Null? By 'no blank cells' you mean no empty string data but can be Null? Can certainly use an autonumber as PK.

Do you have a table of Locations?

LocID	Location	Region
1	Nebraska	West
2	Alaska	West

Save the LocID into Species table. Join tables to retrieve Region info.


Nebraska is misspelled in your example.

[qwerty]

delete post

[orange]

Tell us more in simple plain English about your project - no jargon. Try to answer the who, what, why,where,how, how much or how often sort of questions that someone may ask about your project. eg Is this for Birds only?? You haven't shown the scope of the data.
For more info on Database Planning and Design

Good luck.

[June7]

Then you are working backwards if you want to use Subdatasheet. The + sign should be on the Locations table and show related species records.

Should not work directly with tables and queries. Build forms and reports.

Build a report that uses a query joining tables to pull together all related info.

If you want to provide db for analysis, follow instructions at bottom of my post.

[qwerty]

delete post

[June7]

Your linking is incorrect. CANNOT link tables on two primary key fields in a x-to-many relationship. One table must have Primary key and other table saves that value as Foreign key.

Link should be on LocID and Location fields.

But I think you have a many-to-many relationship which requires another table: PlantLocations

Fields like: PlantID, LocID, DateSeen, SeenBy

[qwerty]

Can you draw an example, there is no option for foreign key?

[June7]

PlantID and LocID would be foreign key fields in the new table. Maybe call them PlantID_FK and LocID_FK

This would be a 'junction' table between PlantSpecies and Locations (note the plural on table name Locations).

[qwerty]

delete post

[June7]

I do not Skype.

You did not create the junction table I describe. Need ANOTHER table called PlantLocations. Locations and PlantSpecies will both link to the new table. Location will no longer be a field in PlantSpecies.

Then you need to enter data into this new table. Options:

1. a single form bound to PlantLocations with comboboxes to select Location and Species.

2. main form bound to Locations, subform bound to PlantLocations with a combobox to select species

3. main form bound to PlantSpecies, subform bound to PlantLocations with a combobox to select location

Example at http://ms-access-tips.blogspot.com/2...y-to-many.html

Set LocID_FK and PlantID_FK fields as compound index to prevent duplicate pairs. Review http://www.geeksengine.com/article/c...ex-access.html

[qwerty]

I created a junction table and the fields are still blank. The data from plant species and location are not showing.

[qwerty]

solved....thank you for your support

[qwerty]

Last question, under the location table it is only showing 0001 matching with another table data 0001 id for that specific location. How do I make it show all data for that specific location, not just the first.

[June7]

Why did you edit posts to delete content?

1. build form/subform as described in post 11, option 2

2. build report

3. use Subdatasheet on table