join two fields in the same table in Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bluesky - 11 Jul 2007 23:36 GMT

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.

Reply to this Message

Joseph Meehan - 12 Jul 2007 00:05 GMT

> Hi, I need to join two fields in the same table into a third field in
> the same table.
[quoted text clipped - 14 lines]
>
> Any help would be appreciated.

I doubt if you really mean exactly what you wrote. :-)

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

Needing to do that and wanting to do that would be very unusual. First
I am going to assume that you are using text fields as number fields don't
have "001" they only have "1" or 1 displayed as "001"

If for any reason, and reasons will happen, one or the other fields gets
changed, then unless you or whoever will remember each time (ain't going to
happen) then you are going to get the data out of sync.

It also means you are storing data repetitively and that in inefficient
in a database. Access can combine the data faster than looking up new data.

Us a formula like "Field1&Field2" to give the result 1001 or "Field1&"
"&Field2" to get 0 001.

You can use the formula in a query report or form.

You can select multiple fields for the primary key if that is part of
your question and you don't even need to do the above.

Signature

Joseph Meehan

Dia 's Muire duit

Reply to this Message

Larry Daugherty - 12 Jul 2007 00:44 GMT

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

Signature

-Larry-
--

> Hi, I need to join two fields in the same table into a third field in the
> same table.
[quoted text clipped - 14 lines]
>
> Any help would be appreciated.

Reply to this Message

John W. Vinson - 12 Jul 2007 01:08 GMT

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

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)

>I would like this to be automatic when I type in the first two fields, but I
>not sure if that can be done.
[quoted text clipped - 4 lines]
>work ok in the query, not when I use it in the table. Maybe I'm using it
>incorrectly.

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]

Reply to this Message