Number/Name in Code

[michalj via AccessMonster.com]

Hi,

there`s a template for Issue Database in Access 2003. In the table "Issues",
there are two fields Assigned To/Opened By, which have data type as "Number",
but this one table is showing them as "Name". Can somebody help me with
advise, if shall I use "Name" or "Number" in SQL code while
inserting/updating these two fields?

Thnx
Michal

 

[Brendan Reynolds]

Hi,

there`s a template for Issue Database in Access 2003. In the table
"Issues",
there are two fields Assigned To/Opened By, which have data type as
"Number",
but this one table is showing them as "Name". Can somebody help me with
advise, if shall I use "Name" or "Number" in SQL code while
inserting/updating these two fields?

Thnx
Michal

I don't have that template available to check right now, but I expect
they're probably lookup fields. They probably store an EmployeeID but
display the employee's name. If so, you would need to use the EmployeeID
when inserting or updating (or indeed, selecting) records via SQL.

 

[michalj via AccessMonster.com]

Exactly, there are two tables: Contacts and Issues, in the Issues table there
are two fields [Assigned To] and [Opened By] (datatype = number) which they
are a lookup fields (First Name + Last Name) based on Contacts_ID;
the question is, I want to update/insert (with VBA/SQL code) [Assigned To]
and [Opened By] records in Issues table, I use names (First Name + Last Name)
or contact_id?

Thnx

Hi,

[quoted text clipped - 8 lines]

Thnx
Michal

I don't have that template available to check right now, but I expect
they're probably lookup fields. They probably store an EmployeeID but
display the employee's name. If so, you would need to use the EmployeeID
when inserting or updating (or indeed, selecting) records via SQL.

 

[John W. Vinson]

I want to update/insert (with VBA/SQL code) [Assigned To]
and [Opened By] records in Issues table, I use names (First Name + Last Name)
or contact_id?

The ID.

This is one of the infuriating bad designs of the Lookup field. It APPEARS to
contain a name, but of course it doesn't. What's in the table is a numeric ID,
and that's what you need to insert in your query or your code.