Auto-populating further fields (1 Viewer)

[robinfq]

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

 

[bob fitz]

Sounds like you should have a "period" table which could be used to retrieve values from when your 'Age (millions)' column is updated. BTW, better to avoid the use of "special keys" e.g. )(# when naming fields.
Can you post a copy of the db

 

[bob fitz]

BTW, I see that you are a new member. Welcome to the forum

 

[theDBguy]

I believe bob was talking about normalizing your table. Look up "normalization" to get you on the right path.

 

[mike60smart]

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

Hi Robin
Welcome to the forum.
Are you able to upload a copy of the database to the forum?

 

[Gasman]

You would do it just like you would manually.
You would take your number and then look up in your list of periods as to which period that value falls between.
There is a function to do this called DLookUp().

 

[Gasman]

Is this your dB?

UtterAccess.com

www.utteraccess.com

If the number is 65, what period would that be in this picture?

 

[mike60smart]

Is this your dB?

UtterAccess.com

www.utteraccess.com

If the number is 65, what period would that be in this picture?
View attachment 112017

 

[mike60smart]

Hi Gasman
The user sent me an excel file listing the Date values.
If 65 then the value returned would be Cenozoic

 

[robinfq]

Hi Gasman, well spotted!

Mike is kindly helping me figure my way around this and has taught me a heap. I'm fascinated to see how it turns out now it's starting to make some more sense (I was heading down the wrong path entirely with how I set it up)!

Is this your dB?

 

[Pat Hartman]

Do not store the period. Use a query that joins to the Period table using between. It doesn't happen often but sometimes, the names and dates of the periods change.

The Period table has three columns
Start, End, PeriodName

The query is :

Select fld1, fld2, fld3, ...., PeriodName
From YourTable Left Join tblPeriod on YourTable.Age >= tblPeriod.Start And <= tblPeriod.End;

 

[Gasman]

Hi Gasman, well spotted!

Mike is kindly helping me figure my way around this and has taught me a heap. I'm fascinated to see how it turns out now it's starting to make some more sense (I was heading down the wrong path entirely with how I set it up)!

Yes, that is what he does.
He goes to other sites and ask the questions you are asking, and no doubt comes back with a solution that he takes credit for.
I have caught him out doing this in the past.

 

[Gasman]

What is the significance of the different numbers for a period?
Surely a period is between on set of years and another?
So if you just store the max for each period a simple lookup will find the correct period?
You just have Mike asking the same question on another forum?
Just a convoluted method of crossposting.

 

[robinfq]

I'd prefer to stay out of internal politics tbh but I really, truly appreciate everyone's input on my questions, wherever they're getting that knowledge from. Mike has already helped me with the very basics (I was treating the table like an Excel spreadsheet previously which made sense in my mind but not particularly useful for this application). My level of knowledge on Access is way below what could even be considered beginner level so it's been massively helpful.

The numbers relate to the different eras, periods and epochs, probably making a bit more sense here where you can see the other half of the spreadsheet:

I'm working through fixing my earlier errors on the table at the moment and getting the data into different tables etc and will then revisit some of the suggestions on here once the data is in a more Access-accessible format. I can see lots of potential but it's a bit further away than I had realised..!

 

[Gasman]

You have not asked anything here except for this post though?
If Mike is going to do the asking for you, over on UA, no need for me to try. They have their own experts over there, who answer his questions.
I believe all you have there can be determined in a table in a certain order, but I will let UA do the work.

 

[mike60smart]

Yes, that is what he does.
He goes to other sites and ask the questions you are asking, and no doubt comes back with a solution that he takes credit for.
I have caught him out doing this in the past.

Hi Gasman
For your information I told Robin that I did not know if this was possible, but would try to fine out. Hence the
post. I am just helping and not looking for any praise.

 

[mike60smart]

I'd prefer to stay out of internal politics tbh but I really, truly appreciate everyone's input on my questions, wherever they're getting that knowledge from. Mike has already helped me with the very basics (I was treating the table like an Excel spreadsheet previously which made sense in my mind but not particularly useful for this application). My level of knowledge on Access is way below what could even be considered beginner level so it's been massively helpful.

The numbers relate to the different eras, periods and epochs, probably making a bit more sense here where you can see the other half of the spreadsheet:

View attachment 112021

I'm working through fixing my earlier errors on the table at the moment and getting the data into different tables etc and will then revisit some of the suggestions on here once the data is in a more Access-accessible format. I can see lots of potential but it's a bit further away than I had realised..!

Hi Robin
I managed to get an answer to your problem here on Utter Access

 

[jdraw]

Can anyone describe/explain why the era/periods/epochs are not contiguous?

?? Why does no category cover ~67---99 million yrs ago?
There are other gaps also.

 

[Pat Hartman]

I managed to get an answer to your problem here on Utter Access

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.

 

[robinfq]

Can anyone describe/explain why the era/periods/epochs are not contiguous?

?? Why does no category cover ~67---99 million yrs ago?
There are other gaps also.

Hiya,
The year listed is the start of that era, so it runs until the start of the next one eg Cretaceous runs from 145 to 66 million years ago