Auto-populating further fields

[mike60smart]

Your schema is incorrect. Look at the one I suggested in #19 again. The start and end need to go at the LOWEST level. While it is technically possible to use just the Start year or the End year for that matter, queries are far simpler if you have both start and end.

The relationship is Era, Period, Epoch and the years are associated with the Epoch which is the lowest level.

For this application, I would use the actual names rather than surrogate keys. It will be easier for the OP to understand and they are not likely to change but just in case, use Cascade Update on the relationship.

Hi Pat

I amended some names of tables in the attached and believe it does what the OP wants.

 

[mike60smart]

Hi Pat
I have no idea how to manage the requirement of being able to select a Start Year and then filter the specific Period
using your table structure?
I attach a screenshot of your table structure.
Are you able to create a form based on these tables?

 

[arnelgp]

try this sample also.

 

[mike60smart]

try this sample also.

Hi arnelgp

That looks perfect.
An example of how I wish I was able to create a Form.
I am sure the OP will be thankful.

 

[gemma-the-husky]

Hi y'all,
I am building a database of my fossil and geology collections. One thing that would be super useful would be for it to be able to auto-populate certain fields based on the data I enter in another.
For example: if I enter any figure between 66 to 145 in the 'Age (millions)' column, I would like it to automatically complete the 'Period' column to say Cretaceous, etc.
I am a complete beginner at Access so don't know if this is even possible but I feel like it should be. If so, please explain it like you would to your great gran as my knowledge on this is pretty basic!
Thanks

The point about normalisation is that you don't repeat derived data in a table.

You would generally store just the age in the data table, and use that data element to derive any information that is dependent on the stored data.

Storing the age is probably more correct than storing the epoch directly. The date will never change, but the period designations may change depending on scientific advancements.