Populate a field based on a different field.

[MadMax]

I am trying to populate a one field based on information supplied in a
different field. I have a table that for example has a CID (not ID its a
field that i actually populate) field and in that field is either a 5digit
number and the letter C (12345C) or a six digit number (123456). Now the
first 2 digits of the number reference the state that record pertains to. I
have a State Field that i would to be automatically populated based on the
first 2 digits of the information in the CID field. Is this possible.

 

[John Vinson]

I am trying to populate a one field based on information supplied in a
different field. I have a table that for example has a CID (not ID its a
field that i actually populate) field and in that field is either a 5digit
number and the letter C (12345C) or a six digit number (123456). Now the
first 2 digits of the number reference the state that record pertains to. I
have a State Field that i would to be automatically populated based on the
first 2 digits of the information in the CID field. Is this possible.

It's possible... but I'd REALLY VERY STRONGLY suggest that you
reconsider this design. Fields - *especially* primary key fields -
should be "atomic"; packing multiple pieces of information, such as a
state name or whatever the C represents, into one field is a certain
recipe for trouble.


The way you'ld do it, if you're stuck with this (IMO) flawed design,
is to use Left([CID], 2) to extract the left two digits, and then look
up the state in a table relating the two digits to the state's name or
two-letter postal code.

John W. Vinson[MVP]

 

[MadMax]

I'm kind of a newbie to all of this, i'm pretty good at figuring stuff out
but where do i need to input the Left([CID],2)?

-MadMax

You can extract the first two characters of the field via:

Left([CID],2)

If you can't use a calculation and must populate a field, simply use an
update query to update the field to the above formula.

I am trying to populate a one field based on information supplied in a
different field. I have a table that for example has a CID (not ID its a
field that i actually populate) field and in that field is either a 5digit
number and the letter C (12345C) or a six digit number (123456). Now the
first 2 digits of the number reference the state that record pertains to. I
have a State Field that i would to be automatically populated based on the
first 2 digits of the information in the CID field. Is this possible.

 

[MadMax]

I appreciate all the help, i really do. I am getting a "type mismatch in
expression error". I'm thinking maybe the table isn't set up correctly. Let
overload you with information so that maybe you can get an idea of the
purpose of the Database and what i'm want to do.

So my company does enviromental studies on all the counties and depending on
the size communties in the US. So are main table which we call our
"communities",
lists all the counties and communities that have been studied. Each study
has a CID that numerically tells you the State, and county or community. For
example
01003C. The 01 would equal alabama and the 003C would be Baldwin County.
That would be a county study and 010070 would be 01 For alabama and 0070 for
the city of Wetumpka. Now we have 3 other tables that track 3 different
process that run on each study. Because we have differnet people doinig the
processes at any given time we repeated fields in each so that a person could
focus on the table they needed to update without having to open up a
different table. So in all four tables you have the CID field the Community
Field a Region field (states are grouped together in regions) and a State
field(abbreviated state name i.e. AL, TX).

I hope that will help you help me. What i am trying to do is when a record
is added into that communities table (since that is the main table) it would
automatically populate those repeating in fields in the other tables with the
information inputted into the communities table. And then also, once the CID
is input into a new record it would automatically update the the State and
Region and Community fields in the all 4 tables.

Is this a out of this world notion or can it be done (easily). Or what
suggestions do you have for DB structure.

Do this in an update query using the table you want to modify. Assuming [CID]
and the new field are both in the same table, create a query based on the
table and change it's type to an update query. The new field is the output
and the Update To: would be Left([CID],2). Run the query and the new field
will be populated.

I'm kind of a newbie to all of this, i'm pretty good at figuring stuff out
but where do i need to input the Left([CID],2)?

-MadMax

You can extract the first two characters of the field via:

[quoted text clipped - 10 lines]

have a State Field that i would to be automatically populated based on the
first 2 digits of the information in the CID field. Is this possible.

 

[MadMax]

Once again i appreciate all your help!

I pitched what you said to the powers that be and there debating it right
now but just wanted to see if getting that CID field to populate the rest of
those fields was possible.

What you're thinking of doing is really bad form in terms of database design.
It sounds like you want to have both the CID and the 3 fields it represents
in the same table. Furthermore, you want to do this 3 (4?) times. Think of
the data duplication and the potential for error.

I think you really ought to take a look at queries and what they can do to
provide information from different datasets in one coherent format. For
example, you can create a query that links the main table to a process table
via CID. The query can provide all the information from a process table and
the other information the user needs from the main table with no duplication.
The form that the user works with would be based on the query rather than the
process table and this would be transparent to the user. Then any changes or
corrections to the main table would automatically be reflected to the user.
Does that make sense?

As far as the field CID is concerned, some people will suggest that you split
this field up into its component parts. Thus you would have three fields
instead of one and only represent the data to the user as a single field
through concatenation. This is certainly something to consider. However, I
am inclined to tell you to keep this as is because it's a simple system and
splitting it up may create more opportunity for error (I.E. the number of bad
combinations is orders of magnitude higher than the number of valid
combinations, and you'd have to store all of the valid combinations anyway to
check the input).

In any event, a type mismatch error indicates a problem where two fields are
not the same type. For example, you are trying to put letters in a field
that only holds numbers. HTH

I appreciate all the help, i really do. I am getting a "type mismatch in
expression error". I'm thinking maybe the table isn't set up correctly. Let
overload you with information so that maybe you can get an idea of the
purpose of the Database and what i'm want to do.

So my company does enviromental studies on all the counties and depending on
the size communties in the US. So are main table which we call our
"communities",
lists all the counties and communities that have been studied. Each study
has a CID that numerically tells you the State, and county or community. For
example
01003C. The 01 would equal alabama and the 003C would be Baldwin County.
That would be a county study and 010070 would be 01 For alabama and 0070 for
the city of Wetumpka. Now we have 3 other tables that track 3 different
process that run on each study. Because we have differnet people doinig the
processes at any given time we repeated fields in each so that a person could
focus on the table they needed to update without having to open up a
different table. So in all four tables you have the CID field the Community
Field a Region field (states are grouped together in regions) and a State
field(abbreviated state name i.e. AL, TX).

I hope that will help you help me. What i am trying to do is when a record
is added into that communities table (since that is the main table) it would
automatically populate those repeating in fields in the other tables with the
information inputted into the communities table. And then also, once the CID
is input into a new record it would automatically update the the State and
Region and Community fields in the all 4 tables.

Is this a out of this world notion or can it be done (easily). Or what
suggestions do you have for DB structure.

Do this in an update query using the table you want to modify. Assuming [CID]
and the new field are both in the same table, create a query based on the

[quoted text clipped - 12 lines]

have a State Field that i would to be automatically populated based on the
first 2 digits of the information in the CID field. Is this possible.

 

[John Vinson]

I pitched what you said to the powers that be and there debating it right
now but just wanted to see if getting that CID field to populate the rest of
those fields was possible.

Sure, it's possible. BUT... It's A Bad Thing To Do!

Storing the data redundantly wastes disk space (trivial), but much
more importantly it risks data corruption. Say a county name was
entered incorrectly in your original source. If you have the county
name stored only once, then you need to correct it only once. If you
have copies of it here, there, and everywhere, you must track down
every single instance and correct all of them.

John W. Vinson[MVP]

 

[MadMax]

If its possible, how is it done. I want to show them what you guys are
talking about versus what they want. Believe me i am in complete aggreance
when it comes to the redundancy of information. The data is shown through a
form and the repeated fields don't dispaly on the form. But they want it in
the table because someone might want to export the table. But how would you
do it?

 

[John Vinson]

If its possible, how is it done. I want to show them what you guys are
talking about versus what they want. Believe me i am in complete aggreance
when it comes to the redundancy of information. The data is shown through a
form and the repeated fields don't dispaly on the form.

They can if you wish. Create a Combo Box based on the Counties table,
with fields for all of the information you want to display. Put
textboxes on the form with control sources like

=comboboxname.Column(n)

where n is the zero-based subscript of the field you want displayed.

But they want it in
the table because someone might want to export the table. But how would you
do it?

Join the Counties table to the data you want exported; select the
fields from the Counties table that you want to export; export the
Query instead of exporting the table.

John W. Vinson[MVP]

 

[MadMax]

So to update the state field i created a select query
and it the query selects the very well but how do i get the query to
populate that value in the field. The way i have the 'State Field set up is
its a text field, the display control is a combo box row source type is
Table/Query and the row source is:
SELECT [Location LookUp].[State Abbr]
FROM [Location LookUp]
WHERE (((Left([CID],2))=[Location LookUp]![FIPS Code]));

but when i switch to datasheet view and type in a CID in the field it
doesn't populate the state!

I'm confused!?!?!?

 

[MadMax]

So to update the state field i created a select query
and it the query selects the very well but how do i get the query to
populate that value in the field. The way i have the 'State Field set up is
its a text field, the display control is a combo box row source type is
Table/Query and the row source is:
SELECT [Location LookUp].[State Abbr]
FROM [Location LookUp]
WHERE (((Left([CID],2))=[Location LookUp]![FIPS Code]));

but when i switch to datasheet view and type in a CID in the field it
doesn't populate the state!

I'm confused!?!?!?

What you're thinking of doing is really bad form in terms of database design.
It sounds like you want to have both the CID and the 3 fields it represents
in the same table. Furthermore, you want to do this 3 (4?) times. Think of
the data duplication and the potential for error.

I think you really ought to take a look at queries and what they can do to
provide information from different datasets in one coherent format. For
example, you can create a query that links the main table to a process table
via CID. The query can provide all the information from a process table and
the other information the user needs from the main table with no duplication.
The form that the user works with would be based on the query rather than the
process table and this would be transparent to the user. Then any changes or
corrections to the main table would automatically be reflected to the user.
Does that make sense?

As far as the field CID is concerned, some people will suggest that you split
this field up into its component parts. Thus you would have three fields
instead of one and only represent the data to the user as a single field
through concatenation. This is certainly something to consider. However, I
am inclined to tell you to keep this as is because it's a simple system and
splitting it up may create more opportunity for error (I.E. the number of bad
combinations is orders of magnitude higher than the number of valid
combinations, and you'd have to store all of the valid combinations anyway to
check the input).

In any event, a type mismatch error indicates a problem where two fields are
not the same type. For example, you are trying to put letters in a field
that only holds numbers. HTH

I appreciate all the help, i really do. I am getting a "type mismatch in
expression error". I'm thinking maybe the table isn't set up correctly. Let
overload you with information so that maybe you can get an idea of the
purpose of the Database and what i'm want to do.

So my company does enviromental studies on all the counties and depending on
the size communties in the US. So are main table which we call our
"communities",
lists all the counties and communities that have been studied. Each study
has a CID that numerically tells you the State, and county or community. For
example
01003C. The 01 would equal alabama and the 003C would be Baldwin County.
That would be a county study and 010070 would be 01 For alabama and 0070 for
the city of Wetumpka. Now we have 3 other tables that track 3 different
process that run on each study. Because we have differnet people doinig the
processes at any given time we repeated fields in each so that a person could
focus on the table they needed to update without having to open up a
different table. So in all four tables you have the CID field the Community
Field a Region field (states are grouped together in regions) and a State
field(abbreviated state name i.e. AL, TX).

I hope that will help you help me. What i am trying to do is when a record
is added into that communities table (since that is the main table) it would
automatically populate those repeating in fields in the other tables with the
information inputted into the communities table. And then also, once the CID
is input into a new record it would automatically update the the State and
Region and Community fields in the all 4 tables.

Is this a out of this world notion or can it be done (easily). Or what
suggestions do you have for DB structure.

Do this in an update query using the table you want to modify. Assuming [CID]
and the new field are both in the same table, create a query based on the

[quoted text clipped - 12 lines]

have a State Field that i would to be automatically populated based on the
first 2 digits of the information in the CID field. Is this possible.

 

[John Vinson]

So to update the state field i created a select query

A SELECT query doesn't update anything. An UPDATE query does - but you
probably don't want to do so.

and it the query selects the very well but how do i get the query to
populate that value in the field. The way i have the 'State Field set up is
its a text field, the display control is a combo box row source type is
Table/Query and the row source is:
SELECT [Location LookUp].[State Abbr]
FROM [Location LookUp]
WHERE (((Left([CID],2))=[Location LookUp]![FIPS Code]));

but when i switch to datasheet view and type in a CID in the field it
doesn't populate the state!

I'm confused!?!?!?

So am I!!

That's NOT HOW IT WORKS.

Table datasheets don't have events, and don't automatically display
anything other than what's in the table.

If you have a Combo Box in your table... *take it out*. The Lookup
Wizard is a source of endless confusion - see

http://www.mvps.org/access/lookupfields.htm

for a critique.

On a *FORM* you can use a combo box based on the query above; it will
need to be Requeried when you type in or change the CID. What you
might want to do instead is create a Query based on your table; join
your table to [Location LookUp] initially by CID, joining CID to FIPS
Code. This won't work of course! But if you open the query in SQL view
you can change

.... INNER JOIN [Location LookUp] ON [yourtable].[CID] = [Location
LookUP].[FIPS Code]

to

.... INNER JOIN [Location LookUp] ON Left([yourtable].[CID], 2) =
[Location LookUP].[FIPS Code]

and select the state name (or other fields as needed) from the
Location Lookup table.

John W. Vinson[MVP]