I apologize in advance for the length here. I always tend to be long winded in my explanations so I will try and keep as
simple as possible.
I have two main tables that I am attempting to split up into several tables.
The two primary tables are: “VEHICLES” and “EQUIPMENT”
There are many fields in each table so I’m trying to create tables for some of these fields and set a primary key in those
tables pointing back to a Foreign Key in one or both of the above referenced tables.
As I said, there will be many additional tables but If I can get help figuring this out on one, I can replicate on the
For reasons of explanations I’ll include one of the additional tables which will have Foreign Keys in both VEHICLES and
EQUIPMENT tables. This table is “VHIC” for Vehicle ID Code.
It is a 6 character text field. An example of a record would be “VTR123”.
I created the VHIC table with two fields, one for text (6-characters) and one for VHIC_ID which is the primary key and
I then created a number field in the VEHICLES table, named it: ‘VHIC_ID’ (Foreign Key in VEHICLES) number-long integer and
then in Relationships created a relationship from the VHIC table to VEHICLES.
So far, so Good or so I thought.
Lets go back to the example Record I have above, which is : VTR123
for that specific row in the VHIC table I have a VHIC_ID number of 127 (meaning it is the 127th record in the table) and the
record VTR123 in the column next to it.
Going back over to the VEHICLES table under the Foreign Key VHIC_ID it is displaying 127.
I want to be able to open the VEHICLES table and see (in the actual table, not a query or form view) VTR123 displayed in the
I know it can be done as I have seen and torn apart a template for another access DB I downloaded off the templates section
That database is Orders management database.
The first complication is that I am going from a number format as a primary key to a number format in Foreign Key field and
trying to display text.
In the Orders Management DB I see the following for the EmployeesID colum in the Orders Table:
SELECT [EmployeeID], [LastName] & ", " &
[FirstName] FROM Employees ORDER BY
This SQL is in the RowSource in the LookUP tab on that field.
Again, this is a number datatype and it is pulling not only one column of text data from another table but both first and
I now click open the ORDERS table and right there under EmployeeID is first name and last name.
If I click on a name it opens a drop down where I can select from other employees first and last name, all in a field that
has data type set for number.
I have tried to replicate this SQL in the LOOKUP tab in the VEHICLES table for VHIC_ID but obviously changing the SQL to
reflect the correct names.
When I open the VEHICLES table and go over to VHIC_ID column I still see the autonumbers pulling from the VHIC and this is
what happens when I click on one of those numbers:
I get a drop down and the values I can choose from are the 6-characted text (example: VTR123)
If I choose one, it throws up an error that I am trying to select Text for a Number data type column.
Any idea how to fix this?
I want to see only related records from the VHIC table and not the autonumbers, only the related 6-character records.
any and all help will be VERY much appreciated.