join two fields in the same table in Access

B

bluesky

Hi, I need to join two fields in the same table into a third field in the
same table.

field 1= group_id (can be 1 or 2)
field 2 = person_id (starting at 001 and continuining)

new field: subject_id (combination of group_id and person_id)

I would like this to be automatic when I type in the first two fields, but I
not sure if that can be done.

Do I need a query, or can I write some code somewhere?

I'm confused whether a query is needed, but I did make one, but it seems to
work ok in the query, not when I use it in the table. Maybe I'm using it
incorrectly.

Any help would be appreciated.
 
L

Larry Daugherty

It can be done but shouldn't be done so don't do it. It violates a
couple of the rules of relational databases.

Use a query or code to calculate and the concatenation of the data
from two fields every time you need to display or print it.

Learn the Ten Commandments of Relational Databases on
www.mvps.org/access Beyond that, learn about relational databases and
data normalization in particular. That site is an incredibly valuable
site for Access developers, poke around and learn a lot.

HTH
 
J

John W. Vinson

Hi, I need to join two fields in the same table into a third field in the
same table.
Click to expand...

No, you don't. See below.
field 1= group_id (can be 1 or 2)
field 2 = person_id (starting at 001 and continuining)

new field: subject_id (combination of group_id and person_id)
Click to expand...
I would like this to be automatic when I type in the first two fields, but I
not sure if that can be done.

Do I need a query, or can I write some code somewhere?

I'm confused whether a query is needed, but I did make one, but it seems to
work ok in the query, not when I use it in the table. Maybe I'm using it
incorrectly.
Click to expand...

Since the SubjectID can always be derived from the other two fields, it SHOULD
exist only in the Query. You can use the query anywhere that you would use the
table - the only thing you can't do with the calculated concatenated field is
edit it, but then you wouldn't WANT to edit it, since the result would then
have a different group ID or person ID than what's in those fields.

John W. Vinson [MVP]
 
Top