Lookups in Tables:Why isn't ID# put in field instead of actual name
Posted on 2000-03-01
Try to keep it simple:
I have 2 tables: one is Publications and the other is Publishers. In the Publications table there is a field called PublisherID and the data type is Number. In the Publishers table there is a field called PublisherID, which is an autonumber field that assigns a number to each Publisher you enter; this field is named Publisher and contains the actual text name of the publisher.
Going back to the Publications table, I have a Lookup in the PublisherID field as such-here's the SQL statement: SELECT DISTINCTROW
The Bound Column is 1; Column Count is 2; Column widths are o",1"; and Display Control is Combo Box.
I go to worksheet view and go into the PublisherID field of the Publications table to select a PublisherID from the drop down combo box that show all the Publishers so I can select one. I pick a publisher by name, which is only thing that is displayed, and the actual publisher name is put into this PublisherID field in the Publications table which I'm in.
Why does it do this? I thought that since the PublisherID field data type is Number and the Bound Column is 1 (which per the SQL statement is the PublisherID number from the Publisher's table) that the actual ID number should have popped into the field, especially at the table level (I'm not using any forms here)? What is displayed in this Publications table is the actual publisher name in the PublisherID field and no the ID number? I'm a little confused. Is the actual ID number stored some where "under the surface" so to speak with Access itself tracking it and the Publisher name is displayed in the table where I'd expect to see a number. I understand why this happens in forms (I think), but I didn't expect to see this at the table level?
Thanks for your help; I really appreciate it. I sure hope I explained this enough to make sense.