Automating entry into fields in the same table. 1

[Conner]

Designing my first database as a learning exercise. The database has six tables with one appears to be good relationships. However, I've noticed a peculiar thing about one table.

There are six fields in this table. Field 1 is a primary key set to autonumber. Field two is a "foreign key" that allows duplicate text. The thing I am noticing takes place in Fields 3, 4 and 5. If I make an entry into field two, that entry "predefines" what must go into fields 3, 4 and 5.

For example, suppose I enter "dog" into field 2. My entries in fields 3, 4 and 5 would need to be "pet", "bow-wow", and "four legs." Similarly, if I enter "house" in field 2, I need to enter "roof", "home", "living-place", in fields 3, 4 and 5.

I've "researched" this problem and keep coming back to autolooup queries, but don't understand how they work.

Is there a way to automate data entry between "related" fields in the same table?

 

[alley]

conner;

Let's call the table with the autonumber key AA. The foreign key in field two is a key to table BB. Table BB better be the table that contains the data you want in fields 3, 4, and 5 of table AA.

But, you don't want to load these fields into table AA permanently, because not only are they redundant, but bas DB design. Also field 2 in table AA can occur in many records (rows), but there should not be duplicates in table BB.

Define a relationship between AA and BB with field 2 in AA and (supposedly) the key in BB. Use a form to enter data into AA with the fiekd source of BB for fields 3,4,and 5.
Access will automatically lookup the correct record in BB, if it exists. Add an error routine foe field 2 when it does not match a key in BB.

If I have missed your point, let me know.

alley

 

[Conner]

Thank you for your quick response. I have printed out what you suggest, but I have an IQ about that of a fried tomato, so I'll have to "study" for a little while.

In reading what you suggested, my first reaction is that you have solved the problem that was really bugging me -- where the data that goes into those three fields is to come from.

With your kind understanding, I will work on this and let you know if I'm making progress. Man is evil and needs to be punished -- I think Access does a really good job of that, don't you?

 

[alley]

Access does some complicated things automatically, but is frustratingly stupid about some things I think are trivial. Just like our two non-Presidents, irritating.

Will help you if I can, but remember, although I am an experienced programmer, Access makes me feel really stupid sometimes. At least I know I am in a large club.

 

[THoey]

Sometimes, Access makes me want to take a large CLUB to my desktop...

If I understand this correctly, every time "dog" is entered into field two, the same values are entered into three, four and five. If that is the case, a separate table that has those values and a key could be used so that you are not storing redundant data. Example:

Lookup table:
KeyField   Autonumber
LookupString Text   (ex: "DOG", "HOUSE")
FirstValue   Text   (ex: "PET", "ROOF")
SecondValue  Text   (ex: "BOW-WOW", "HOME")
Thirdvalue   Text   (ex: "FOUR-LEGS", "LIVING-PLACE")

Now, using this table, you could have a list box that showed the LookupStrings ("DOG", "HOUSE&quot and which ever one you select will put the corresponding KeyField autonumber in the table. When you display this record, you can have it show the 1st, 2nd and 3rd values based on a query...

Not sure if this will help or not, I understand you are just learning. If you have any questions, just ask...


Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[Conner]

Thanks. Let me put my IQ of 20 on "focus" and study what you suggest. I'll get back to you when I've digested this. In my limited wisdom, I have decided that it's not money, land, or genes we need to leave the next generation....it is painfully, slowly acquired, computer knowledge.

How did you ever learn all this stuff, especially in isolation? And something else while I'm on my rocking horse; I can read as well as anyone, but the darn 2400 page books on this stuff almost require that you know what you need to know before you can find it in the "index." Again, thanks. Back to you soon.