Retrieve 2 values into 1 field via Lookup

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

Hello gurus,

Thank you for your help and assistance.

I have been wondering if I can save multiple values from
separate fields into a single field in a table using a
lookup query.

Here we go:

there is a Branches table
BranchID BranchDescription
101 City A Dept 1
102 City A Dept 2
300 City B
400 HQ

and Staff Table

BranchID StaffID Name
101 101 John Smith
101 201 Mary Poppins
102 101 George Jackson
102 111 Louis Brett
102 201 Jenn Smirnoff

and some other Table, say "customers"

...... InputStaffID
101101
102201
101201

When entering InputStaffID values into the Customer table
in the table view mode I use Lookup query from Staff
Table picking up BranchID and StaffID. I can display 2
values in the combobox, but only one can be entered, it
seems.

The question is how do I save BranchID and StaffID, which
when combined constitute the unique ID, together in the
InputStaffID filed using a lookup query?

Sorry for long explanation, I tried to make it easy to
inderstand.

Any suggestions would be very much appreciated.

Regards,
Paul
 
-----Original Message-----
Hello gurus,

Thank you for your help and assistance.

I have been wondering if I can save multiple values from
separate fields into a single field in a table using a
lookup query.

Here we go:

there is a Branches table
BranchID BranchDescription
101 City A Dept 1
102 City A Dept 2
300 City B
400 HQ

and Staff Table

BranchID StaffID Name
101 101 John Smith
101 201 Mary Poppins
102 101 George Jackson
102 111 Louis Brett
102 201 Jenn Smirnoff

and some other Table, say "customers"

...... InputStaffID
101101
102201
101201

When entering InputStaffID values into the Customer table
in the table view mode I use Lookup query from Staff
Table picking up BranchID and StaffID. I can display 2
values in the combobox, but only one can be entered, it
seems.

The question is how do I save BranchID and StaffID, which
when combined constitute the unique ID, together in the
InputStaffID filed using a lookup query?

Sorry for long explanation, I tried to make it easy to
inderstand.

Any suggestions would be very much appreciated.

Regards,
Paul

.
you could figure out a way, or someone could if they put
Click to expand...
their mind to it - but i think you'd be sorry later! you
couldn't set table relationships on that "dual" field, and
parsing out the values for later use in the db would be an
ongoing headache and complication.
two choices: 1) add both combination-primarykey fields to
the "customers" table, or 2) create another field, such as
an autonumber field, in the Staff table to serve as its'
primary key.
i would probably choose #2, just to simplify my life. you
can still use the BranchID and StaffID fields to create a
unique index in the Staff table, if you want to prevent
duplicate records at the table level.
 
Tina, thank you for your advice.

Paul
-----Original Message-----
put
their mind to it - but i think you'd be sorry later! you
couldn't set table relationships on that "dual" field, and
parsing out the values for later use in the db would be an
ongoing headache and complication.
two choices: 1) add both combination-primarykey fields to
the "customers" table, or 2) create another field, such as
an autonumber field, in the Staff table to serve as its'
primary key.
i would probably choose #2, just to simplify my life. you
can still use the BranchID and StaffID fields to create a
unique index in the Staff table, if you want to prevent
duplicate records at the table level.
.
Click to expand...
 
Back
Top