Solved

Lookups in Tables:Why isn't ID# put in field instead of actual name

Posted on 2000-03-01
11
201 Views
Last Modified: 2011-10-03
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].[PublisherName]
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
0
Comment
Question by:JoeBoyd
  • 6
  • 4
11 Comments
 
LVL 10

Expert Comment

by:paasky
ID: 2572386
Hello JoeBoyd,

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
0
 
LVL 4

Expert Comment

by:threeps99
ID: 2572390
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
0
 

Author Comment

by:JoeBoyd
ID: 2572436
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
0
 
LVL 10

Expert Comment

by:paasky
ID: 2572511
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
0
 

Author Comment

by:JoeBoyd
ID: 2572658
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
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 10

Accepted Solution

by:
paasky earned 50 total points
ID: 2572699
Perhaps the column is too narrow and the second columns is hidden (cannot fit into column width in datasheet view).

Make column more wide selecting the right edge of the column and dragging it to the right (mouse cursor should change like this <-|->).

In forms you can set the combobox width but in datasheet view that depends how wide you make the column.

Paasky
0
 

Author Comment

by:JoeBoyd
ID: 2572979
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
0
 
LVL 10

Expert Comment

by:paasky
ID: 2573012
You're welcome. These things are easy after you have learned them. :-)

Paasky
0
 

Author Comment

by:JoeBoyd
ID: 2574339
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@lightstreamcomputing.com
0
 
LVL 10

Expert Comment

by:paasky
ID: 2574377
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
0
 
LVL 10

Expert Comment

by:paasky
ID: 2574385
And thanks for all the compliments, I think I haven't earned them all but it's always nice to read them.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now