Using Access to Manage Biological Field Data

[swallowingsky]

Hi all!

I'm new to access, and I'm not sure what my limitations are. A little background: I work for a biologist who is taking part in multiple field studies at the same time. In short, he works on some projects where he may catch frogs and others where he may catch salamanders, etc. He asked me to create a database for him in access since excel had too many limitations. I think I've got the database going well so far, but then he popped this question on me.

"Is there a way that I can have a table auto generate when a new species is caught, from all of my different studies? That way I could go to this table and find exactly how many species/which species I've ever looked at?"

This had some problems for multiple reasons:

1. We cannot always identify a species to scientific name, sometimes we have to identify it to order or family.
2. This would have to pull from MANY tables.
3. I am uneasy about working with queries.
4. He would not want multiple entries for the same species.

Any help on how I can attempt to tackle this?

Thanks

-SS

[kd2017]

We're going to need more specifics. What does your current db structure look like? Can you post a screenshot of your db's relationships window?

[swallowingsky]

So "Observation" under "Salamander Observation" and "Bycatch" would be an example of a species entry.

[kd2017]

My first suggestion would be to have a species table. Then instead of a string value [Observation] or [Bycatch] in your tables you would just have a foreign key field that refers back to your species table. It will be faster/easier to aggregate your data as you requested.



My second is that you consider renaming you tables and fields. It's best practice not to use special characters (eg. #, /, (, ), etc...) or spaces in either table names or field names. Only use letters, numbers, and underscores. You can swap spaces for underscores if you like. [Water Data] becomes [WaterData] or [Water_Data]. Also, don't use reserved words for object names either. From my quick scan you should rename all the fields named [Date] and [Description]. Here is a list of reserved words: http://allenbrowne.com/AppIssueBadWord.html


I understand that your single database is combining information collected for many different studies with different requirements and types of data collected. I'm wondering if excel/power query might be better for your particular situation? Can anyone else speak to that? Is Access the right tool for this job?

[swallowingsky]

Thank you for the response,

I will make those syntax edits, I didn't realize spaces were a no-go! The problem with Excel is that it doesn't offer the same organizational methods access does. Every study would have to be a different excel file, and then every different measurement a separate page, none of which is linked. I think the appeal of Access is that I can look up not only a certain site ID, but what the water qualities, weather, and organisms observed there was on a form if I wanted to.

Learning to utilize this tool for scientific purposes has been difficult though- almost all teaching material is based around customer databases and it doesn't always translate well. It seems many biologists and scientific-based organizations are slowly switching to Access. I've heard University of Vermont is even teaching their graduate students to organize their data this way now.

That being said, I've never had any experience with Power Query.

I appreciate your input and will look into a species table.

[kd2017]

At any rate you can *almost* do what you wanted as is. What you will want to look into is running an agregate query on each table that you want to count species entries. You would group by [observation] I suppose, and use the count aggregate function to get the count of each species in that table. Then you would tie those together with a union query.

The problem with this is that it will breakout any observation that are unintentionally different in any way, eg misspelled species names or variations or any human input error. Not to mention it will be slow processing on string field. This is where the dedicated species table would improve accuracy and performance.

All that said, if the dataset is small you probably won't notice the performance hit.

[orange]

swallowingsky,

Not directly related to your subject matter, but here is a link of a "new to database" person trying to create a database (about crocodiles) and the dialog that progressed with his development. It might help put some ideas/questions into context.

Good luck.