Multiple Acronyms for Same Name

[giblets]

Bewary, extreme beginner in Access/databases.

I'd like to assign multiple acronyms to the same entry. Is there a way to do this? For example, I want both ANDSCO and SCHSCO to link to the entry for Schizachyrium scoparium, which contains information about the grass "little bluestem." The reason is because some users are more familiar with the old name, Andropogon scoparium, and its old acronym, ANDSCO, but I want the output to be the new name, Schizachyrium.

Is there a way to do this without having separate entries for ANDSCO and SCHSCO?

I am not familiar with Access terminology so it is hard to Google the answer of such questions.

Thanks!



P.S. I'm so happy to be on a distinguished road!

[orange]

What exactly does entry for

Schizachyrium scoparium

mean in the context you've described?
You have old name, new name,old acronym and new acronym -- but what is the entry?
Is that the biological/official/authoritative name?

[giblets]

What exactly does entry for mean in the context you've described?
You have old name, new name,old acronym and new acronym -- but what is the entry?
Is that the biological/official/authoritative name?

Here is a shortened example of the plant database and an example query.

Also, please let me know if there's a preferred way of showing this kind of information on the forum.

[orange]

Is this a database that you/your organization created? Can you tell us more about the underlying database?
Can you show us Tables and relationships as jpg? There are more pieces than you have shown in the image.

CChi05, WChi05, tpye and lifecycle are referenced values

You seem to have part of a thesaurus.
What exactly is your role/intent?

Here is a link to more general thesaurus design (for info only)
http://www.iskouk.org/presentations/will_21072008.pdf

[giblets]

My question is how, if possible, to produce summaries like the output listed above, being able to use either Acronym. The ultimate goal is to create reports that will summarize multiple queries that are generated from the data in the "data entry" format above, e.g. not just average coverage, but %native vs. %non-native species per quadrat or site, %grass vs. %forbs vs %trees, %perennial vs. %annual, and weighted % of all those things taking into account coverage. But I'm getting ahead of myself here as I'm just learning.

Mostly I am just messing around with Access. There already exists a standalone program with a plant database that does most of this, but it is moderately antiquated and still uses all the old scientific names. The names as well as the reports that are generated are not editable, so you are stuck with the formulas and formatting built into the program.

I would also love to get a link to some good beginner's guides that are tailored toward scientific data management in Access.

[orange]

This is the scary part

messing around with Access

.
If you have a corporate database and related standards, then no one should be messing with that database.

If the database is "out of date" with regard to acronyms, then that can be remedied. But it requires work.
Is there a dba or group that makes changes to the database structures/tables?

Can you provide a jpg of the tables and relationships in the plant database?

[giblets]

This is the scary part
.
If you have a corporate database and related standards, then no one should be messing with that database.

If the database is "out of date" with regard to acronyms, then that can be remedied. But it requires work.
Is there a dba or group that makes changes to the database structures/tables?

Can you provide a jpg of the tables and relationships in the plant database?

It is a database I created on my own, so I guess I am the DBA and I have no formal standards, ha. There should be nothing "scary" about the situation except my lack of knowledge...The other "out of date" database I referenced is in some weird 1990s printer file format and is not something that belongs to me nor am I editing.

Anyway, for now I am just looking for an answer to my original question, which I guess can also be phrased as such if I assign the acronym to be the primary key for each entry: "Can I assign multiple keys to a single entry?"

[orange]

Can you provide a jpg of your tables and relationships?
That is can you post a screen dump of your Relationships?

Can you post a copy of your database in mdb format?

As for beginner guides

Database principles http://forums.aspfree.com/attachment...achmentid=4712
Normalization http://www.rogersaccesslibrary.com/T...malization.zip
Entity Relationship Diagramming http://www.rogersaccesslibrary.com/T...lationship.zip

[giblets]

Can you provide a jpg of your tables and relationships?
That is can you post a screen dump of your Relationships?

Can you post a copy of your database in mdb format?

As for beginner guides

Database principles http://forums.aspfree.com/attachment...achmentid=4712
Normalization http://www.rogersaccesslibrary.com/T...malization.zip
Entity Relationship Diagramming http://www.rogersaccesslibrary.com/T...lationship.zip

Thanks for the guides. This is a hypothetical conversation (I created the original image in Excel...). I am just trying to learn how things work before I waste time assembling the database, entering data, making queries, etc. the wrong way.

[giblets]

Here's the only relationship in my fake example, I guess. It seems like a simple question but I guess it must be much more complicated than I expected!

[orange]

Read the guides - they are well worth the read. They deal with the basic concepts of database.
Getting the database tables and relationships structured/designed to meet your requirements is the key to a useful database application.

Here are few free existing data models http://www.databaseanswers.org/data_models/index.htm

and a list of steps in database design http://www.databaseanswers.org/approach2db_design.htm

[giblets]

Read the guides - they are well worth the read. They deal with the basic concepts of database.
Getting the database tables and relationships structured/designed to meet your requirements is the key to a useful database application.

Here are few free existing data models http://www.databaseanswers.org/data_models/index.htm

and a list of steps in database design http://www.databaseanswers.org/approach2db_design.htm

I will read the guides, but do you have an answer to my original question?

[orange]

I believe it can be done, with proper database design.
It seems to me, from this thread, that you have Plants that have an authoritative Scientific name.
In addition you have a number of other names and alias, that could be used to identify that "plant" in various contexts.

Seems scientific name represents the "thing" and doesn't change.
I would assign these "things", known by their scientific name an autonumber as Primary key.

I would set up relational tables based on that autonumber

It appears that a "thing" could have 1 or more "old names". You didn't say that explicitly, but I could see where a "common name" goes from Old to New and then another New...

If you set up a table of acronyms/aliases, then you might include a PreferredReportingAlias, where there is one and only one PreferredReportingAlias per "thing".
A query would use the acronym to find the Plant and then the PreferredReportingAlias.

This is just an approach that would need some data and some prototyping to prove/disprove/adapt to a workable solution.

I have done similar things in the past where there were misspellings of various terms. So setting up a table with a unique, authoritative spelling and relating that to various acronyms/aliases/misspellings allowed consistent spelling/naming where required.

[giblets]

That seems to be what I was looking for. I'll follow-up later, when I'm sure I'll have more specific questions, when I actually start putting the whole thing together.

Thanks!

[orange]

Glad to help, but you really need a designed database structure. Give the Entity Relationship diagramming a go with your data.