Microsoft Access? Try Microsoft Barrier.
May 7, 2014 8:19 AM   Subscribe

Oh, kind people of Metafilter. Please help this Microsoft Access newbie figure out how to connect different tables together when there is no predefined relationship. *Snowflakes inside*

The goal of this project is for users to be able to compare the "#" and "description" of column A with the "#" and "description" of column B. Each number is unique. The “# and DescriptionA” are two separate columns found in Tbl_I, and the “# and DescriptionB” are two separate columns in Tbl_II. It is likely that users will want to change the numbers in both columns A and B at various times since the relationship between the two columns is not predefined.

Example table is below:


(TYPE IN)___|#-> DESCRIPTIONA | #-> DESCRIPTIONB_|(1-3)______ | (1-3)
HAS 4 LEGS_|M01. CAT_________ |R06. FROG ________| 3_________| 3
A GOOD PET_|M03. DOG________ |R05. ALLIGATOR ___| 1 _________| 3

The user may want to change “R05. Alligator” in Column B to “R04. Lizard.”

Potentially complicating factor#1: “DescriptionA” and “DescriptionB” are greater than 255 characters, and splitting their contents into multiple columns is not an option. Because DescriptionA and DescriptionB have up to ~600 characters, I think it would be easier for users to select the number from a drop down menu, which then “pulls” the data for the relevant description.

Potentially complicating factor #2: Eventually I need to link “M01. Cat” to Tbl_3 which has data in the following columns: Kingdom, Phylum, Class, Order, Family, Genus, Species, Encyclopedia Article. The Encyclopedia Article definitely has more than 255 characters.

Potentially complicating factor #3: Ideally, this can be exported into excel.

I'm using Microsoft Access 2010 on Windows 7.

I would greatly appreciate any suggestions on how to do this, or even keywords to Google.
posted by oceano to Computers & Internet (7 answers total) 2 users marked this as a favorite
I think this might be a case where you've oversimplified things to try to explain it, because I'm realllly hazy here on what it is you're actually trying to do. Is M01 the "#" in column A? Is "Cat" standing in for the really long description? Is this "grouping" thing completely arbitrary? Are we rating that both Dogs and Alligators are good pets in the second line? That they're equally good pets? Can only one animal of each type be rated as good pets? I don't actually see why it is they're being put together at all, which makes it hard to make any recommendations about how to relate them.

I realize sometimes things are proprietary, but it's usually easier to explain with something closer to what you're actually doing. Or if your real usage is going to be mammals and reptiles, I think I'm even more confused, I apologize.
posted by Sequence at 8:39 AM on May 7, 2014

Wow, this is confusing. I don't understand why you need separate columns for Mammals and Reptiles. That should be one column for the relevant taxonomic classification (Phylum? I don't really remember biology).

So it seems like your tables should be set up a different way, i.e. tbl_animals is ID, common name, description

Then maybe a features table? ID from tbl_animals, haslegs, goodpet

Your user would select the ID from a dropdown (lookup field)

So example data from tbl_animals would be M01, "domesticated cat", "cats are jerks who like to wake you up at 4 am blah blah blah"
tbl_features would be M01, true, true

tbl_taxonomy is going to be a one-to-many since one phylum contains a lot of different animals, but each animal belongs to only one phylum (I think, see above about not remembering biology class). To really do this right, you'd want a kingdoms table, a phylums table, etc etc so you're not repeating a bunch of rows.

tbl_kingdoms (taken from wikipedia):
ID, Kingdom
K01, Bacteria
K02, Protozoa
K03, Chromista
K04, Plantae
K05, Fungi
K06, Animalia

Example row for our friend the cat: M01, K01, P01, C03, Or05, F05, G04, S12

What it sounds like you want to end up is really a form where the info is pulled together from the various tables and displayed side by side. This is going to be hard to explain in a text comment but the search "create subforms from queries" might get you somewhere. It requires some tweaking, but you can group the data so all the reptiles appear in one column and all the mammals appear on another. This is not really Access 101 though.
posted by desjardins at 8:59 AM on May 7, 2014


Unfortunately, sharing the actual situation is not possible. However, perhaps the following is a better example. I am trying to compare and constrast the constitution from Country A (USA) to the constitution from Country B (Mexico).

US's preamble says, U01: We hold these truths to be self-evident, that all men are created equal, that they are endowed by their Creator with certain unalienable Rights, that among these are Life, Liberty and the pursuit of Happiness

Mexico's 4th Act says, M04:All people, men and women, are equal under the law. This article also grants all people protection to their health, a right to housing, and rights for children. Everyone has a right to an appropriated ecosystem for their development & welfare.

In the grouping "people are equal" I would give a 3 for similarity between these two statements.

In the grouping "rights given by the state" I would give 2 for similarity between these two statements.

Does that help any?
posted by oceano at 9:01 AM on May 7, 2014

That clarifies things, but it also makes it more difficult because there is no preexisting relationship between the US and Mexican constitutions (at least in database terms). I guess that is the question you're asking. So instead of "Animals" and "Reptiles" it'd be "US" and "Mexico"? I don't know anything about the Mexico constitution so I'm going to make up one for Westeros. (No spoilers.)

ID, Country
(01, United States)
(02, Westeros)

DocID, doctype,
(01, Constitution)
(02, Declaration)

PartID, CountryID, DocID, DocPart
(04, 01, 01, "The right of the people to be secure in their persons, houses, papers, and effects...")
(12, 02, 02, "The rights of the people of Westeros are at the pleasure of their king, who may confiscate any property as he sees fit...")

RatingID, PartID, SimilarTo, Score
(001, 04, 12, 3)
Explanation: The person is comparing the 4th amendment of the US constitution to the 12th statement of the Westeros Declaration and rates the similarity with a score of 3.

The ratings table is what is connecting the two documents.

It seems like you'd need 3 subforms - two for tbl_docparts (to display the relevant text you're evaluating) and one for tbl_ratings (to enter your rating).
posted by desjardins at 9:24 AM on May 7, 2014

I think the only thing I'd add to that is that if numbering repeats, the primary key for tbl_docparts may be composed of more than one field or else set separately to just be an incrementing ID field, in which case the foreign keys in tbl_ratings would need to work likewise. But, yeah, basically, they don't have a relationship previously, the relationship is what you're creating.

Technically, ANY database can be exported into Excel in the fashion of just creating a sheet for each table, but the data may not be readably meaningful in that context. How to make it meaningful, again, really very dependent on what you're specifically doing. To be honest, if you aren't experienced with this and it's rather complicated and you can't share details in a public forum, it might be worth it to pay someone to come in to handle this part, because I'm kind of afraid that anything we come up with might turn out not to actually be the right way to do it if your example isn't a close enough analogue to the real thing, but if you don't know anything about databases then you're not going to necessarily know how close it is or isn't, if that makes sense.
posted by Sequence at 10:04 AM on May 7, 2014

I suggest you get this book and read it. I used it for a little training class once, and it does a good job explaining the fundamental ideas of database modeling, and has also lots of information about Access forms, reports, etc.
posted by thelonius at 10:05 AM on May 7, 2014

You could read up on many-to-many relationships and how to create them using a third table.
posted by soelo at 2:19 PM on May 7, 2014

« Older Help me find a notebook   |   There's got to be a better way to do this. Newer »
This thread is closed to new comments.