Auto-populating further fields

[Gasman]

BTW All this back and forth in PMs does not help any members on this site whatsoever (or any other), which is why these sites exist? :-(

 

[Pat Hartman]

@robinfq Welcome to AWF. You're off to a bad start though since as Gasman mentioned, this isn't the way forums work. The experts who answer your questions do not get paid for the service. They do it to contribute to the community but the community only benefits if you are not communicating in private with someone who is helping you. If Mike is helping you privately, that's generous of him but far from standard operating procedure and it is actively discouraged.

I don't really understand Mike's motivation. He seems to take questions posted here and repost them on UA. Maybe he thinks they have better experts there. I don't think so based on some of the things he comes back here with like his answer to your question. In any event, please do not get the idea that you can just PM an expert and expect private support. Please post your questions in the forums and there's lots of people who are happy to help. And please consider attempting to normalize your tables rather than using the poor technique that was recommended to you. You will be far better placed in the future as you attempt to expand your app's functionality.

 

[mike60smart]

And you didn't recognize this as inferior to the solution I suggested in #11?????

@robinfq the breakdown is more complicated than what was described. You could use a a table with 5 columns but I would probably use three related tables instead to avoid repetition and to give you the most flexibility.

tblEra
EraName (PK)

tblPeriod
PeriodName (PK)
EraName (FK)

tblEpoch
EpochName (PK)
PeriodName(FK)
StartYR
EndYR

You use tblEpoch for the criteria and then join up the line to get the other names.

Hi Pat
My earlier solution was not a good solution. I actually ended up with a similar table layout as yours as shown.
I then used Cascading Combo Box's to manage the Data Input

 

[Pat Hartman]

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.

 

[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.

 

[Pat Hartman]

If you think that is a good solution, what can I say? My method means they enter the actual year, not a range, and everything else fills automatically. Your method requires FOUR entries and you can't enter a specific year. I don't think that is what the OP had in mind.

 

[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?

 

[Pat Hartman]

Are you able to create a form based on these tables?

Not today. I have paying work to attend to.

 

[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.