Foreign Key shows data rather than Key reference (1 Viewer)

I used Analyze Table to split a table into three tables Table2, Customer, and Shipment. Both the Customer and Shipment tables have an autonumber Primary Key (PK) field assigned (CustomerID & ShipmentID respectively). Table 2 is linked to these tables via Foreign Key (FK) fields CustomerID & ShipmentID (Same names as PK in the associated table). The display control is a combo box for these two fields.

When I open Table2 in datasheet view, both of the FK fields contain what appears to be the data from the multiple fields contained in the associated PK table. I was expecting to see just the PK (as a number) rather than the data.

I do see that Row Source for the field has code

SELECT [ShipID] AS xyz_ID_xyz, [Tracking_Info] & ', ' & [Shipdate] AS xyz_DispExpr_xyz, [Tracking_Info], [Shipdate] FROM Shipments ORDER BY [Tracking_Info], [Shipdate];​

If I delete this code, the PK reference number appears to show up leaving me a couple questions:

• Is this normal or did I set something up incorrectly?
• If this code remains, am I storing duplicate data from the other tables in the Table2 fields?
• Is this code necessary to maintain data integrity?

.

Thank you for the help and insight.

i've never used any built-in ANYTHING in access, prolly for this exact reason. this scenario, to me, is the same thing as a recuiter's software trying to analyze a resume. it doesn't work, nor has it ever. a combo box in a table field when looking at it at the table level means it's a lookup field, and most programmers say it's a bad idea. I'm indifferent cuz I've never used them anyway. if you have any programming experience at all, or DB dev experience, I would suggest you build your table objects (and all other ones, for that matter) from scratch or get them from outside applications using the "import/export" ribbon tab at the top of the screen.

Hi

I agree with Adam

The analyser just indicates that your initial table should be split into a number of tables.

Look at what they are suggesting and build from scratch.

Hi. To answer your questions:

1. It is normal and you did set something up by using the analyze wizard
2. If the code remains, no, you are not storing duplicate data because you are actually just storing the foreign key
3. That code is not necessary to maintain data integrity and removing it is actually recommended (also, change the display control back to Textbox)

theDBguy said:

2. If the code remains, no, you are not storing duplicate data because you are actually just storing the foreign key

Click to expand...

So in this case the SELECT code is just displaying the info in the referenced table's fields?

Thank you.

PuzzledNH said:

So in this case the SELECT code is just displaying the info in the referenced table's fields?

Thank you.

Click to expand...

Yes. It's just the same as using a Combobox on a Form.