How do I make 1 column auto-fill another?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Neonjoe - 09 May 2008 15:18 GMT

I'm working with a database used for military record keeping. Right now
there is a columb for someone's numeric MOS designation and then the worded
title of it. The numeric designation will always have an exact match to the
worded column, so I am assuming there has to be a way to make that
auto-complete, but after 2 hours of searching and experimenting, I haven't
been able to make it happen.

Basically I want to creat 2 seperate lists, one with the MOS numerics and
one with the titles.

Something like:

11B Infantry
11C Mortar
68W Medic
92G Cook

So that when I put 11B in column A it will automatically fill column B with
infantry every time.

Wish I could just use a basic If Then statement, but I can't get access's
version of it to operate properly for me.

Reply to this Message

Steve Schapel - 10 May 2008 10:12 GMT

Neonjoe,

I assume you have created a table that just shows the correspondence
between the MOS numerics and their associated titles, similar to what
you showed in your example?

Ok, so the principle here, as regards correct database design, is that
this "master lookup" table is the *only place* in your database where
you would store both these pieces of data. So for example in the table
for the personnel records, you should only have one of these fields,
presumably the MOS. If you are implying that you would have both fields
there, then this is absolutely incorrect.

Now, if you are talking about the associated MOS title being
*displayed*, whether on your forms or reports, then that's a different
matter. That is understandable, and a very common database application
requirement. There are a number of ways that this type of thing is
achieved. Please have a look at this article:
http://accesstips.datamanagementsolutions.biz/lookup.htm

Signature

Steve Schapel, Microsoft Access MVP

> I'm working with a database used for military record keeping. Right now
> there is a columb for someone's numeric MOS designation and then the worded
[quoted text clipped - 18 lines]
> Wish I could just use a basic If Then statement, but I can't get access's
> version of it to operate properly for me.

Reply to this Message

Neonjoe - 12 May 2008 12:16 GMT

Social Secruity Test Date Last Name First Name Hold Status
123-45-6789 11 MAR 08 Hauser Bryan RP
Age Rank Schooling MOS MOS Title
20 PFC 13 11B Infantry

Imagine that is the first 10 columns in my table of information. That is
how I am having things appear in my data table, and like I said, what I want
to have happen, is be able to put that 11B in the MOS column and have it
automatically fill the MOS Title column. Either inputting the data directly
into the table, or using a form that I have created to input data into the
table.

There are going to be about 35 possibilities for either one of those
columns, so I know I will have to create a source of those 35 possibilities
somewhere. That's what I'm not sure how to do. How do I pre-define all of
those data sets ahead of time, and then link them to their corresponding
pieces of data, and make it automatically fill. I read the article that you
linked, and I really couldn't figure out how to relate that to what I was
trying to accomplish. Thanks for the help thus far though.

Reply to this Message

Pete D. - 15 May 2008 01:13 GMT

Try thinking of this

E-1 AB Airman Basic
E-2 Amn Airman
E-3 A1C Airman First Class
E-4 SrA Senior Airman

You would put this into one table. In this table the pay grade would be the
key, or in a joint assignment it might be a little more complex key like

E-1 in one field and Air Force in another (actual key using military tables
would be E1F)
E-1 in one field and Army in another (actual key using military tables would
be E1A)

By making the combination of both fields you create a unique key that cannot
be duplicated. This would allow you to have the abreviated grade and the
full spelled out grade for both services with each having its own unique
key. From this point on you would never have to type any of them again,
just select the right paygrade and service and access will relate the two
spelled out grades. Pay grades evolved from 80 column punch cards and
provide a sort order, if you dig deeper, this is also where the identifier A
for Army, N Navy and F for Air Force came from in the federal pay tables.
As computer systems evolved those punch card idenifiers became keys for
relational database files (most of the time). Folks wonder how I can look
at 500 fields of data and tell you what is stands for, cause I grew up with
the evolution.

So in your MOS you would just use the identifier 88MXX (XX I don't remember
what it is) as the key and the title would just be displayed in the form
from one table and not stored in the member info table. Search Google for
ms access table relationships for some explinations of this. Start with the
overview below. Excuse my spelling as Office 2007 toasted my outlook spell
checker.

http://support.microsoft.com/kb/304466

> Social Secruity Test Date Last Name First Name Hold Status
> 123-45-6789 11 MAR 08 Hauser Bryan RP
[quoted text clipped - 20 lines]
> linked, and I really couldn't figure out how to relate that to what I was
> trying to accomplish. Thanks for the help thus far though.

Reply to this Message

Pete D. - 15 May 2008 01:26 GMT

Oh, left out the other part. After you relate your Personnel table MOS_ID
field to MOS table MOS_ID field the easiest way for a beginner would be to
build a query with those two tables and base your form on the query. Then
you would just drag the MOS title field to the form and your done.

> Try thinking of this
>
[quoted text clipped - 59 lines]
>> linked, and I really couldn't figure out how to relate that to what I was
>> trying to accomplish. Thanks for the help thus far though.

Reply to this Message

Neonjoe - 16 May 2008 13:29 GMT

OK I'm about ready to give up on this idea, it just seems to be 10x more
trouble than it's worth, but I guess I'll give this one last try.

I created 2 brand new tables after reading your article.

Table 1
Table 2
MOS MOS TITLE SSN MOS
MOS TITLE
11B Infantry 123-45-6789 11B

I had to make the primary key in table 2 MOS, and I made the relationship
between MOS from table 1 and MOS from table 2. I came across 2 things that
are not what I wanted. Number 1, I can't figure out how to get it to fill
that MOS TITLE column in my table. It just leaves me a dropdown box with MOS
TITLE from Table 1 in it with the information. The other problem, is that
in order to get the information to show in Table 2, I had to make that MOS
the primary key, which in turn means I can't have duplicates. So that is not
going to do anything for me when 1000 people need to be input in the table
and none of them can have the same MOS.

I'm obviously missing something here, or I'm wasting time trying to do
something that Access just isn't capable of. Keep in mind, I've been using
computers for a good amount of time, and Access for about 2 weeks. So I can
try to figure out what is being talked about in these articles, but honestly
it's like trying to read a book in another language in my first year of study
of that language. I'm re-reading each section multiple times and then still
having to go to trial and error to see if my hypothesis on the section was
correct.

I appreciate the replies that have tried to help me accomplish my goal at
this point. I'm trying to get this to work, but I'm getting not much but
frustrated.

> Oh, left out the other part. After you relate your Personnel table MOS_ID
> field to MOS table MOS_ID field the easiest way for a beginner would be to
[quoted text clipped - 64 lines]
> >> linked, and I really couldn't figure out how to relate that to what I was
> >> trying to accomplish. Thanks for the help thus far though.

Reply to this Message

Pete D. - 16 May 2008 23:10 GMT

Okay, I know your just starting at this and yes (although I'll probally get
flamed) you can do it the way you started but if you get this one right you
will be well on your way to understanding how to use a relational database.

First your MOS table will have
MOS and MOS_Title
Nothing else is currently needed unless you want to add descriptions and
such later. MOS must be unique key no duplicates.

Your People table will include 1 to millions of personnel. It may include
SSAN, Name, Grade or whatever but it must also include MOS. MOS in the
people table will not be index as a unique key but it should be indexed as a
non-unique key.

Now, open the relationships window and add both tables to the window. Drag
the MOS field from the personnel table to the MOS table MOS Field and you
will get a one to many relationship. In other words your personnel file can
have many MOS that are duplicates but the MOS file can only have one of
each.

Now you create a select query using both files. In the query select all
fields from Personnel and drag them to the query grid. From the MOS file
only drag the MOS_Title field to the query.

Now create your form using this query you just created. Every time you add
a MOS the MOS Title will fill in automatically. Post back and we can help
you get around any problems.

Oh yeah, don't forget to add some MOS's and Titles to the MOS table for
testing, you can just stick them right in the table grid for now but
eventually create a form for just the MOS table to load additonal MOS's. I
also found there are MOS listings around the web that you could probally
import to save you a boat load of typing. Your personnel folks probally
alreay have a spreadsheet or table of all of MOS they could give you.

> OK I'm about ready to give up on this idea, it just seems to be 10x more
> trouble than it's worth, but I guess I'll give this one last try.
[quoted text clipped - 131 lines]
>> >> was
>> >> trying to accomplish. Thanks for the help thus far though.

Reply to this Message

Rate this thread: