Some bizarre behavior in MS Access. Please help.

Question

I have defined three tables, Stores, InventoryItems and StoreItemRecords. My StoreItemRecords table has foreign key columns (StoreID, InventoryItemID) that "point to" the primary keys of the Stores (StoreID) and InventoryItems (InventoryItemID) records. The columns are named the same between the tables.

If I run a query like this:

SELECT StoreID, InventoryItemID FROM StoreItemRecords;

I get some bizarre results. I get: 1, Hammer 2, Box of Nails 3, Some other item name.

So, I am getting the StoreID, as I should. But I am also getting the NAME of the inventory item, not the ID of the inventory item. Also, it is important to note that the InventoryItemID column is defined as a NUMBER, not TEXT.

So somehow, Access is trying to help me by providing the InventoryItemName in place of the InventoryItemID, but I can't seem to find the cause of this behavior or any way to stop it.

[one more note. I have written some VBA code to populate the StoreItemRecords table and, in debug mode, I can "watch" the InventoryItemID values being assigned to the column, and I have verified that the IDs are actually being put in there.]

Has anyone seen behavior like this? I know I am going to feel really dumb when someone points out the goofy thing I am doing to cause this but, at this point, it is worth the embarrassment.

Thanks in advance for any help you can provide.

Answer 1

My money is on one of the fields being setup as a “lookup table” in access. These are generally considered to be a not so useful feature of access trying to help novice developers who find it hard to grasp how a database works.

If you go into the table in design mode you should be able to verify if that is set on the fields in question. If it is turn it off and try the query again

Here is a link on why they are evil

http://www.mvps.org/access/lookupfields.htm