Lookups in Tables:Why isn't ID# put in field instead of actual name
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
[Publishers].[PublisherID]
[Publishers].[PublisherNam
FROM[Publishers];
The Bound Column is 1; Column Count is 2; Column widths are o",1"; and Display Control is Combo Box.
MY SITUATION:
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.
MY QUESTION:
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.
Joe Boyd
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
[Publishers].[PublisherID]
[Publishers].[PublisherNam
FROM[Publishers];
The Bound Column is 1; Column Count is 2; Column widths are o",1"; and Display Control is Combo Box.
MY SITUATION:
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.
MY QUESTION:
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.
Joe Boyd
I dont think you can do what you are trying to do in worksheet view. You need to create a combo box on a form and do it that way. If I understand you right :)
threeps
threeps
ASKER
Thanks to both of you so far for your respones. The only reason I'm using the Datasheet view right now is to help me learn and understand Access better. I'm very new at this and the application I will be developing is very complicated so I want to make sure I understand what's going on inside Access (I'm the one who posted the message about "Yes I Can Run My Business").
paasky:
That is what I thought, that the Bound Column data is actually stored in the table field, but it isn't. When I open the Publications table I see the Publisher name and not the ID number in the PublisherID field. The first column in the SQL statement, which I understand is what the "Bound Column" refers to (thus column #1 the way I think I understand it) is the Publisher ID number which should be what is stored in this field in the Publications table. I understand that if I was using a form that since column #1 is hidden that the Publisher Name would be displayed and not the actual ID number, but I didn't expect this at the table level. Please explain. Is the ID# actually there in the field in the table and I can only see the Publisher name. I know this is going to sound stupid, but if that's the case isn't that like having two pieces of data stored in one field? Again, at the table level and not the form. Thanks again for your help; I really appreciate it.
Joe
paasky:
That is what I thought, that the Bound Column data is actually stored in the table field, but it isn't. When I open the Publications table I see the Publisher name and not the ID number in the PublisherID field. The first column in the SQL statement, which I understand is what the "Bound Column" refers to (thus column #1 the way I think I understand it) is the Publisher ID number which should be what is stored in this field in the Publications table. I understand that if I was using a form that since column #1 is hidden that the Publisher Name would be displayed and not the actual ID number, but I didn't expect this at the table level. Please explain. Is the ID# actually there in the field in the table and I can only see the Publisher name. I know this is going to sound stupid, but if that's the case isn't that like having two pieces of data stored in one field? Again, at the table level and not the form. Thanks again for your help; I really appreciate it.
Joe
threeps99,
you can define field source as (combobox type) lookup in the table design view. When you view the table in datasheet view and move the cursor to the field, it changes to combobox.
JoeBoyd,
if you have defined the field source to lookup field, you will see the lookup value in the datasheet mode. Try changing the column width to 2;2 and you'll see what's the data stored in the table. Access doesn't allow you to store text type data to number field. Assuming you've defined ID numeric, there must be numeric value in lookup bound column.
I hope this is not confusing you more.
Paasky
you can define field source as (combobox type) lookup in the table design view. When you view the table in datasheet view and move the cursor to the field, it changes to combobox.
JoeBoyd,
if you have defined the field source to lookup field, you will see the lookup value in the datasheet mode. Try changing the column width to 2;2 and you'll see what's the data stored in the table. Access doesn't allow you to store text type data to number field. Assuming you've defined ID numeric, there must be numeric value in lookup bound column.
I hope this is not confusing you more.
Paasky
ASKER
paasky,
Thanks for responding so quickly and for being so patient. I DO appreciate it.
I tried what you suggested, and it worked. I did see just the ID# in the field and not the name anymore. But, now I have another question. Since I changed the column widths to 2",2" like you suggested and left the Column Count to 2, shouldn't I have seen 2 columns when I clicked on the drop down box in the PublisherID field (the PublisherID number and the Publisher Name, with the Publisher ID number being displayed and/or stored in the PublisherID field when I made a selection, since the Bound Column is still 1). I only now see just the first column, just the PublisherID number. When I was experimenting with forms, the drop down combo boxes would display whatever columns the Column Count was so long as the width for that column wasn't set to 0" and of course I had those columns selected in the SQL statement. Does it work different in Datasheet view at the table level? Thanks again.
Joe
Thanks for responding so quickly and for being so patient. I DO appreciate it.
I tried what you suggested, and it worked. I did see just the ID# in the field and not the name anymore. But, now I have another question. Since I changed the column widths to 2",2" like you suggested and left the Column Count to 2, shouldn't I have seen 2 columns when I clicked on the drop down box in the PublisherID field (the PublisherID number and the Publisher Name, with the Publisher ID number being displayed and/or stored in the PublisherID field when I made a selection, since the Bound Column is still 1). I only now see just the first column, just the PublisherID number. When I was experimenting with forms, the drop down combo boxes would display whatever columns the Column Count was so long as the width for that column wasn't set to 0" and of course I had those columns selected in the SQL statement. Does it work different in Datasheet view at the table level? Thanks again.
Joe
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
paasky,
You were right on the money!!! Thanks. That worked. Especially thanks for explaining the difference why things acted different in the table datasheet view verses a form. Not only did you answer my question but you also helped me learn and understand a lot. I'm finding that a little basic knowledge can carry a long way with understanding Access, and that the flip side is true -if you don't understand some of the basics, you'll have trouble with a lot of things. Thanks.
I'm going to leave the question open for a little while just in case I think off a follow-up (which I don't think I will). But, I will be accepting your answer of course and will increase the points.
Joe Boyd
You were right on the money!!! Thanks. That worked. Especially thanks for explaining the difference why things acted different in the table datasheet view verses a form. Not only did you answer my question but you also helped me learn and understand a lot. I'm finding that a little basic knowledge can carry a long way with understanding Access, and that the flip side is true -if you don't understand some of the basics, you'll have trouble with a lot of things. Thanks.
I'm going to leave the question open for a little while just in case I think off a follow-up (which I don't think I will). But, I will be accepting your answer of course and will increase the points.
Joe Boyd
You're welcome. These things are easy after you have learned them. :-)
Paasky
Paasky
ASKER
Paasky,
All your responses were excellent! Thank you. Like I've already told you in another question, you answered my questions thorougly and completely, and in detail. I like detail. You also comment in a manner that is very intuitive (it's like you know what a person needs to know without the person asking it exactly, boy, that was a mouthful). For example, in the above response you could have just said you need to widen the column, but you told me how also. I am new to this MS Access development and your responses here have helped me a lot. Thank you. I hope you are available when I have more questions.
Joe Boyd
Email:joe@lightstreamcompu
All your responses were excellent! Thank you. Like I've already told you in another question, you answered my questions thorougly and completely, and in detail. I like detail. You also comment in a manner that is very intuitive (it's like you know what a person needs to know without the person asking it exactly, boy, that was a mouthful). For example, in the above response you could have just said you need to widen the column, but you told me how also. I am new to this MS Access development and your responses here have helped me a lot. Thank you. I hope you are available when I have more questions.
Joe Boyd
Email:joe@lightstreamcompu
I'll sit here and wait for your new questions... :-)
Well to be seriously, this is hobby I really enjoy and this is good variety for the work I do.
My mother language is Finnish and maybe that's one reason my suggestions are different than suggestions given by English speaking people.
Good luck!
Arto Pääskynen
Well to be seriously, this is hobby I really enjoy and this is good variety for the work I do.
My mother language is Finnish and maybe that's one reason my suggestions are different than suggestions given by English speaking people.
Good luck!
Arto Pääskynen
And thanks for all the compliments, I think I haven't earned them all but it's always nice to read them.
The bound column contains the data which is stored into the field. If you have set columnwidth to 0, the bound column is not shown and you'll see only the description column. And when you select publisher name, the ID field from the same record is used as a key value.
Paasky