If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access combo box-show name, not ID, in table?
I have read through some old posts relative to my question, but came away
three times as confused and intimidated. Here is a simple and common example of the equivalent of what I want to do: Let's say I have a Suppliers table and form, and a Products table and form. On the Products form I want to create a combo box for the Supplier field that will use the Supplier table as a source to look up data. I want the combo box to show me a list of names (not ID#s), AND I want to store Supplier NAMES (not ID#s) in the Products table. When I look at the table, I want that info (Supplier NAME) to just be there, without having to fish through subdataforms, separate queries, or reports. Is there a REASONABLE way for a non-programmer to accomplish this? I notice that the sample Northwinds database shows names (as opposed to IDs) in tables routinely. I have carefully studied design, field properties, and relationships until my eyes glazed over. But for the life of me I cannot duplicate this. Then there's the question of expressions vs. SQL statements. How do I tell the difference? How do I know which I need? How do I learn to write either one? I am using Access (in Office) 2003--with an Idiot's Guide for Access 2000. (That's where I started, but didn't get far. This time I mean it--but I'm beginning to wonder if I'm crazy.) |
#3
|
|||
|
|||
As others mentioned, the WHOLE idea of relational database system is hat you
only need to store the id field, and then the rest of the fields (like supple name) can then be viewed. The way the northwind works is that the id is stored, but the form (or sub-form) is based on a query. When you set the id, then the name etc magically will appear!. Those queries need to be left join queries.. Also, it is not clear: I want the combo box to show me a list of names (not ID#s), AND I want to store Supplier NAMES (not ID#s) in the Products table. what do you mean list of names? do you mean suppler names? or do you look up a name..and what the suppler name to appear? (I guess you are not clear on name vs product name...or do you mean one and the same?). When I look at the table, I want that info (Supplier NAME) to just be there, without having to fish through subdataforms, separate queries, or reports. Yes...you build query to do this. You then build combo box on the form that displays that returns the id..and this comb box can search/display by any column you want..but you always still store the id. Is there a REASONABLE way for a non-programmer to accomplish this? You can use a query to do the above. Just make the combo box return the "id"...and make sure the query has as many (or all) fields from the other table. once done, then no code at all needs to be written here... -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
#4
|
|||
|
|||
Thanks, Wayne.
OK, you've convinved me to do things the "normal" way--a rarity for me. That list of the Evils of Lookup Fields is pretty scary, all right. Between you, the Ten Commandments you linked me to, and the other response to this post, some (not all) of my "Why?" questions have been answered. (See my response to Albert Kallal). I love the Ten Commandments, but I don't understand all of them. To wit: #4 What's a "procedure"--as in "write comments in your procedures and explain each variable"? #5 What's error handling? #6 What does it mean to split a database? #9 I'm not even going to touch it. #10 I dread to ask, although this sounds like the really important one-- *Back-up my database? I regularly backup my documents (to a zip disk). I assumed my Access files (databases) would be included in that. Am I wrong? Does this mean something different? *Work only on the Production Database? What? How? *Prototype copy? What? How? Thanks for your help. "Wayne Morgan" wrote: Yes, you can set up the equivalent of a combo box in the table. However, tables are just to store data. They shouldn't be used by regularly going to the table. As you indicate, the combo box on the form does show the name and it is possible to have the name show in the results of a report with just the ID being stored in the table. What you are looking for is setting up the Lookup tab for that field in the table. You would set it up just as you do the combo box on the form. The problem with doing this is that if you do look at the table, what you see isn't what is really there and this can cause confusion and problems later. See item #2 at this link. http://www.mvps.org/access/tencommandments.htm -- Wayne Morgan MS Access MVP "write on" write wrote in message ... I have read through some old posts relative to my question, but came away three times as confused and intimidated. Here is a simple and common example of the equivalent of what I want to do: Let's say I have a Suppliers table and form, and a Products table and form. On the Products form I want to create a combo box for the Supplier field that will use the Supplier table as a source to look up data. I want the combo box to show me a list of names (not ID#s), AND I want to store Supplier NAMES (not ID#s) in the Products table. When I look at the table, I want that info (Supplier NAME) to just be there, without having to fish through subdataforms, separate queries, or reports. Is there a REASONABLE way for a non-programmer to accomplish this? I notice that the sample Northwinds database shows names (as opposed to IDs) in tables routinely. I have carefully studied design, field properties, and relationships until my eyes glazed over. But for the life of me I cannot duplicate this. Then there's the question of expressions vs. SQL statements. How do I tell the difference? How do I know which I need? How do I learn to write either one? I am using Access (in Office) 2003--with an Idiot's Guide for Access 2000. (That's where I started, but didn't get far. This time I mean it--but I'm beginning to wonder if I'm crazy.) |
#5
|
|||
|
|||
Thanks for the response.
You wrote, As others mentioned, the WHOLE idea of relational database system is hat you only need to store the id field, and then the rest of the fields (like supple name) can then be viewed. First, viewed how? I find subdatasheets to be too cumbersome. (I am visually impaired, so the less I have to scan through and pick out the better.) Is there a better way to link to the Supplier Table that I'm missing? I guess my reasoning went something like this: Queries and reports are useful for sorting or displaying data in a format that's not readily available in a table (especially using fields from more than one table). But if I can set up a table to show exactly what I want to see, why not do that, rather than bothering to write a query, which I then have to go to and run just to see what I want? Put another way, why duplicate the table with a query, if I can just have the table show what I want in the first place? (Answer: Because that ain't so easy after all!) But it turns out that I apparently have to write a query anyway to put the info in the table the way I want it. Still, that saves navigating to and running another object every time I want to see the info. But then, I could just open the query, rather than even opening the table, which I guess is the point after all. Sorry I'm rambling; I'm "thinking out loud" here. You say: The way the northwind works is that the id is stored, but the form (or sub-form) is based on a query. When you set the id, then the name etc magically will appear!. Those queries need to be left join queries.. I'm sorry, but I have to ask: If the normal and recommended way is to store and display the [Supplier] ID# in the [Products] table, WHY is the sample database written to display [Supplier] Names in the [Products] tables, which is the ABnormal and NOT recommended way? This is confusing to us newbies (at least this newbie), who presumably use the sample to help us understand the normal way to use a database. Also, I'm afraid I don't quite follow. 1) Are you implying that I should be looking at the form within the table somehow? 2) Are you saying that the combo box in the form, which displays Supplier Names to choose from for data entry, but stores the ID in the table, is itself a kind of query? 3) I don't understand what you mean by "set the ID." 4) I guess I'm not clear yet on join queries, and I'm not sure which queries you are referring to here. You asked: Also, it is not clear: I want the combo box to show me a list of names (not ID#s), AND I want to store Supplier NAMES (not ID#s) in the Products table. what do you mean list of names? do you mean suppler names? or do you look up a name..and what the suppler name to appear? (I guess you are not clear on name vs product name...or do you mean one and the same?). Sorry I wasn't clear. What I had in mind was this: I would use a Products Form for data entry, to feed a Products Table. The Products Table would have a Supplier field. The corresponding field on the Products Form would be a combo box (or list box, if you prefer). In order to enter the Supplier to go with a particular product, I would use this combo/list box (on the Products Form) to choose the Supplier Name (so I don't have to memorize all Supplier ID#). I understand the norm is to then store the Supplier ID in the Supplier field, in the Products Table. What I originally intended, however, was to store, or at least display, the Supplier Name (rather than ID) in the Products Table, as well as displaying it in the form. My thinking was, "If I view the data in the Products Table, what good is the Supplier ID to me, unless I have memorized which Supplier belongs to which ID?" But I guess I'm SUPPOSED TO view the data either one record at a time in a form, or in a query, or in a report. The table is apparently not MEANT for viewing; it's just a storage closet from which to pull out whatever I want to view somewhere else. Did I finally get it? I guess most of this is a moot point if I just stick to the normal way of doing things. Thanks again for your help. "Albert D. Kallal" wrote: As others mentioned, the WHOLE idea of relational database system is hat you only need to store the id field, and then the rest of the fields (like supple name) can then be viewed. The way the northwind works is that the id is stored, but the form (or sub-form) is based on a query. When you set the id, then the name etc magically will appear!. Those queries need to be left join queries.. Also, it is not clear: I want the combo box to show me a list of names (not ID#s), AND I want to store Supplier NAMES (not ID#s) in the Products table. what do you mean list of names? do you mean suppler names? or do you look up a name..and what the suppler name to appear? (I guess you are not clear on name vs product name...or do you mean one and the same?). When I look at the table, I want that info (Supplier NAME) to just be there, without having to fish through subdataforms, separate queries, or reports. Yes...you build query to do this. You then build combo box on the form that displays that returns the id..and this comb box can search/display by any column you want..but you always still store the id. Is there a REASONABLE way for a non-programmer to accomplish this? You can use a query to do the above. Just make the combo box return the "id"...and make sure the query has as many (or all) fields from the other table. once done, then no code at all needs to be written here... -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
#6
|
|||
|
|||
Dear "write on":
It caught my eye that you say you are visually impaired. I teach at a school for blind and visually impaired students. I have taught computer courses involving Access to totally blind and low vision students. If you have problems with Access related to your vision, perhaps I might be of assistance; or, perhaps I can learn from you! Fred Boer "write on" wrote in message ... Thanks for the response. You wrote, As others mentioned, the WHOLE idea of relational database system is hat you only need to store the id field, and then the rest of the fields (like supple name) can then be viewed. First, viewed how? I find subdatasheets to be too cumbersome. (I am visually impaired, so the less I have to scan through and pick out the better.) Is there a better way to link to the Supplier Table that I'm missing? I guess my reasoning went something like this: Queries and reports are useful for sorting or displaying data in a format that's not readily available in a table (especially using fields from more than one table). But if I can set up a table to show exactly what I want to see, why not do that, rather than bothering to write a query, which I then have to go to and run just to see what I want? Put another way, why duplicate the table with a query, if I can just have the table show what I want in the first place? (Answer: Because that ain't so easy after all!) But it turns out that I apparently have to write a query anyway to put the info in the table the way I want it. Still, that saves navigating to and running another object every time I want to see the info. But then, I could just open the query, rather than even opening the table, which I guess is the point after all. Sorry I'm rambling; I'm "thinking out loud" here. You say: The way the northwind works is that the id is stored, but the form (or sub-form) is based on a query. When you set the id, then the name etc magically will appear!. Those queries need to be left join queries.. I'm sorry, but I have to ask: If the normal and recommended way is to store and display the [Supplier] ID# in the [Products] table, WHY is the sample database written to display [Supplier] Names in the [Products] tables, which is the ABnormal and NOT recommended way? This is confusing to us newbies (at least this newbie), who presumably use the sample to help us understand the normal way to use a database. Also, I'm afraid I don't quite follow. 1) Are you implying that I should be looking at the form within the table somehow? 2) Are you saying that the combo box in the form, which displays Supplier Names to choose from for data entry, but stores the ID in the table, is itself a kind of query? 3) I don't understand what you mean by "set the ID." 4) I guess I'm not clear yet on join queries, and I'm not sure which queries you are referring to here. You asked: Also, it is not clear: I want the combo box to show me a list of names (not ID#s), AND I want to store Supplier NAMES (not ID#s) in the Products table. what do you mean list of names? do you mean suppler names? or do you look up a name..and what the suppler name to appear? (I guess you are not clear on name vs product name...or do you mean one and the same?). Sorry I wasn't clear. What I had in mind was this: I would use a Products Form for data entry, to feed a Products Table. The Products Table would have a Supplier field. The corresponding field on the Products Form would be a combo box (or list box, if you prefer). In order to enter the Supplier to go with a particular product, I would use this combo/list box (on the Products Form) to choose the Supplier Name (so I don't have to memorize all Supplier ID#). I understand the norm is to then store the Supplier ID in the Supplier field, in the Products Table. What I originally intended, however, was to store, or at least display, the Supplier Name (rather than ID) in the Products Table, as well as displaying it in the form. My thinking was, "If I view the data in the Products Table, what good is the Supplier ID to me, unless I have memorized which Supplier belongs to which ID?" But I guess I'm SUPPOSED TO view the data either one record at a time in a form, or in a query, or in a report. The table is apparently not MEANT for viewing; it's just a storage closet from which to pull out whatever I want to view somewhere else. Did I finally get it? I guess most of this is a moot point if I just stick to the normal way of doing things. Thanks again for your help. "Albert D. Kallal" wrote: As others mentioned, the WHOLE idea of relational database system is hat you only need to store the id field, and then the rest of the fields (like supple name) can then be viewed. The way the northwind works is that the id is stored, but the form (or sub-form) is based on a query. When you set the id, then the name etc magically will appear!. Those queries need to be left join queries.. Also, it is not clear: I want the combo box to show me a list of names (not ID#s), AND I want to store Supplier NAMES (not ID#s) in the Products table. what do you mean list of names? do you mean suppler names? or do you look up a name..and what the suppler name to appear? (I guess you are not clear on name vs product name...or do you mean one and the same?). When I look at the table, I want that info (Supplier NAME) to just be there, without having to fish through subdataforms, separate queries, or reports. Yes...you build query to do this. You then build combo box on the form that displays that returns the id..and this comb box can search/display by any column you want..but you always still store the id. Is there a REASONABLE way for a non-programmer to accomplish this? You can use a query to do the above. Just make the combo box return the "id"...and make sure the query has as many (or all) fields from the other table. once done, then no code at all needs to be written here... -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
#7
|
|||
|
|||
Dear "write on":
While I am not an expert like Wayne or Albert, perhaps I can suggest some answers: I love the Ten Commandments, but I don't understand all of them. To wit: #4 What's a "procedure"--as in "write comments in your procedures and explain each variable"? A procedure is a piece of programming code. Access uses Visual Basic for Applications as its programming language. A procedure might look like this: Private Sub Form_Open(Cancel As Integer) On Error GoTo Errorhandler Me.cboAuthor.SetFocus ExitPoint: Exit Sub Errorhandler: fncWRMSErrMsg Err.Number, Err.Description Resume ExitPoint End Sub This procedure, which runs when a form is opened, sets the focus to a combobox on the form called "cboAuthor". #5 What's error handling? Error handling is programming code which deals with errors that might occur with your program. In the procedure above, the lines 2,4,6,7, and 8 are error handling code. #6 What does it mean to split a database? To actually have two MDB files: one which has only data tables, (the back end), and one with all the forms, queries and reports (the front end). The front end links to the tables in the back end. Usually the back end is on a server, and the front end on individual workstations. #9 I'm not even going to touch it. #10 I dread to ask, although this sounds like the really important one-- *Back-up my database? I regularly backup my documents (to a zip disk). I assumed my Access files (databases) would be included in that. Am I wrong? Does this mean something different? You back up a database by copying the database file to a backup location. No one should be using the file when this is done. If you mean that you copy the files in the folder "My Documents" to a zip disk, you may or may not be backing up your Access database, depending on whether those files are in that folder. *Work only on the Production Database? What? How? *Prototype copy? What? How? Production Database=finished database application actually in use. Prototype=unfinished database application HTH Fred Boer Thanks for your help. "Wayne Morgan" wrote: Yes, you can set up the equivalent of a combo box in the table. However, tables are just to store data. They shouldn't be used by regularly going to the table. As you indicate, the combo box on the form does show the name and it is possible to have the name show in the results of a report with just the ID being stored in the table. What you are looking for is setting up the Lookup tab for that field in the table. You would set it up just as you do the combo box on the form. The problem with doing this is that if you do look at the table, what you see isn't what is really there and this can cause confusion and problems later. See item #2 at this link. http://www.mvps.org/access/tencommandments.htm -- Wayne Morgan MS Access MVP "write on" write wrote in message ... I have read through some old posts relative to my question, but came away three times as confused and intimidated. Here is a simple and common example of the equivalent of what I want to do: Let's say I have a Suppliers table and form, and a Products table and form. On the Products form I want to create a combo box for the Supplier field that will use the Supplier table as a source to look up data. I want the combo box to show me a list of names (not ID#s), AND I want to store Supplier NAMES (not ID#s) in the Products table. When I look at the table, I want that info (Supplier NAME) to just be there, without having to fish through subdataforms, separate queries, or reports. Is there a REASONABLE way for a non-programmer to accomplish this? I notice that the sample Northwinds database shows names (as opposed to IDs) in tables routinely. I have carefully studied design, field properties, and relationships until my eyes glazed over. But for the life of me I cannot duplicate this. Then there's the question of expressions vs. SQL statements. How do I tell the difference? How do I know which I need? How do I learn to write either one? I am using Access (in Office) 2003--with an Idiot's Guide for Access 2000. (That's where I started, but didn't get far. This time I mean it--but I'm beginning to wonder if I'm crazy.) |
#8
|
|||
|
|||
"write on" wrote in message:
... I love the Ten Commandments, but I don't understand all of them. To wit: #6 What does it mean to split a database? Just to add some additional information to Fred's excellent response, here are some great links on splitting databases which should help explain the purpose: http://www.granite.ab.ca/access/splitapp/index.htm http://allenbrowne.com.au/ser-01.html http://www.access-experts.com/defaul...lSplitDB&sm=18 http://www.access.qbuilt.com/html/gem_tips.html#SplitDB How to manually split a Microsoft Access database in Access 2002 or in Access 2003: http://support.microsoft.com/kb/304932/ -- Jeff Conrad Access Junkie Bend, Oregon |
#9
|
|||
|
|||
Fred,
Most of that is pretty scary, but thanks for the enlightenment. At least it tells me what I need to worry about, and what I don't. That's helpful. write on "Fred Boer" wrote: Dear "write on": While I am not an expert like Wayne or Albert, perhaps I can suggest some answers: I love the Ten Commandments, but I don't understand all of them. To wit: #4 What's a "procedure"--as in "write comments in your procedures and explain each variable"? A procedure is a piece of programming code. Access uses Visual Basic for Applications as its programming language. A procedure might look like this: Private Sub Form_Open(Cancel As Integer) On Error GoTo Errorhandler Me.cboAuthor.SetFocus ExitPoint: Exit Sub Errorhandler: fncWRMSErrMsg Err.Number, Err.Description Resume ExitPoint End Sub This procedure, which runs when a form is opened, sets the focus to a combobox on the form called "cboAuthor". #5 What's error handling? Error handling is programming code which deals with errors that might occur with your program. In the procedure above, the lines 2,4,6,7, and 8 are error handling code. #6 What does it mean to split a database? To actually have two MDB files: one which has only data tables, (the back end), and one with all the forms, queries and reports (the front end). The front end links to the tables in the back end. Usually the back end is on a server, and the front end on individual workstations. #9 I'm not even going to touch it. #10 I dread to ask, although this sounds like the really important one-- *Back-up my database? I regularly backup my documents (to a zip disk). I assumed my Access files (databases) would be included in that. Am I wrong? Does this mean something different? You back up a database by copying the database file to a backup location. No one should be using the file when this is done. If you mean that you copy the files in the folder "My Documents" to a zip disk, you may or may not be backing up your Access database, depending on whether those files are in that folder. *Work only on the Production Database? What? How? *Prototype copy? What? How? Production Database=finished database application actually in use. Prototype=unfinished database application HTH Fred Boer Thanks for your help. "Wayne Morgan" wrote: Yes, you can set up the equivalent of a combo box in the table. However, tables are just to store data. They shouldn't be used by regularly going to the table. As you indicate, the combo box on the form does show the name and it is possible to have the name show in the results of a report with just the ID being stored in the table. What you are looking for is setting up the Lookup tab for that field in the table. You would set it up just as you do the combo box on the form. The problem with doing this is that if you do look at the table, what you see isn't what is really there and this can cause confusion and problems later. See item #2 at this link. http://www.mvps.org/access/tencommandments.htm -- Wayne Morgan MS Access MVP "write on" write wrote in message ... I have read through some old posts relative to my question, but came away three times as confused and intimidated. Here is a simple and common example of the equivalent of what I want to do: Let's say I have a Suppliers table and form, and a Products table and form. On the Products form I want to create a combo box for the Supplier field that will use the Supplier table as a source to look up data. I want the combo box to show me a list of names (not ID#s), AND I want to store Supplier NAMES (not ID#s) in the Products table. When I look at the table, I want that info (Supplier NAME) to just be there, without having to fish through subdataforms, separate queries, or reports. Is there a REASONABLE way for a non-programmer to accomplish this? I notice that the sample Northwinds database shows names (as opposed to IDs) in tables routinely. I have carefully studied design, field properties, and relationships until my eyes glazed over. But for the life of me I cannot duplicate this. Then there's the question of expressions vs. SQL statements. How do I tell the difference? How do I know which I need? How do I learn to write either one? I am using Access (in Office) 2003--with an Idiot's Guide for Access 2000. (That's where I started, but didn't get far. This time I mean it--but I'm beginning to wonder if I'm crazy.) |
#10
|
|||
|
|||
Dear Fred,
Wow! That's pretty cool! I use ZoomText for screen magnification, and I love it. The downside is, it doesn't play well with my graphics card (or visa versa), so mouse control can be a hassle. The mouse can be difficult to position exactly where I need it; it's hard to control highlighting of text, and may not stop highlighting when I let go of the mouse button; mouse pointer may jump around the screen out-of-control. This has led me to learn and use keyboard shortcuts as much as I can. But that presents its own set of challenges. First of all, There are cases where the ZoomText hotkeys conflict with Microsoft shortcuts. ZoomText always wins. Then I have to do the MS function another way--usually ALT+menu keys. But even that doesn't always work. (Case in point: If I open a new message window in Outlook Express, there is an address book icon next to the "To:" box label. The idea is, you click that icon to open your address book and select an address to put in the "To:" field. I cannot find a way to open that icon without a mouse. I cannot even get it to receive focus so I can try to open it. And this function can't be done with ALT+menus either. Do screen readers have a way around this?) Receiving focus is often a problem when trying to navigate. ZoomText has a function that lets me ALT+TAB through all open windows, until I get to the one I want. But the window I stop at doesn't always receive focus the way it should, so that my next keyboard command will work there. I particularly have trouble with Outlook in this regard. Navigating within a window is usually a pain. The F6 key doesn't work most of the time (to move between panes) the way MS shortcut key lists say it is supposed to, and I don't even know why. I can't even find ZoomText conflicts to explain that. There are certain areas of the Outlook main window, for example, that just can't be had without a mouse. Internet Explorer is a nightmare without a mouse. I can't even TAB or F6 out of the menu bar/toolbar area, into the actual web page. This is where MS MouseKeys comes in handy. I can use keys to move and click the mouse; once you get used to it, control is much better than handling the mouse, although it's slower. (See Accessibility Wizard in Control Panel. You can also adjust the thickness and blink rate of the cusor.) ZoomText does have the Web Finder feature that works with IE. That is useful, if I am determined to use the keyboard just on principle, but it is more cumbersome and time-consuming than using the mouse. As for Access and keyboard shortcuts--well, I haven't even gotten that far yet. Just getting comfortable with Access itself is all I can handle right now. The shortcut learning curve will have to wait. And I'm leary of it, because I supsect all the navigating between multiple objects and views will be really hairy. Again, the ALT+TAB does help for switching windows. But receiving focus...? I don't know. Do you work with ZoomText much? Got any tips for me? write on P.S. Should we change the subject, and/or move this discussion to a different forum? Can we? Will "Notify me of replies" work if we do? "Fred Boer" wrote: Dear "write on": It caught my eye that you say you are visually impaired. I teach at a school for blind and visually impaired students. I have taught computer courses involving Access to totally blind and low vision students. If you have problems with Access related to your vision, perhaps I might be of assistance; or, perhaps I can learn from you! Fred Boer "write on" wrote in message ... Thanks for the response. You wrote, As others mentioned, the WHOLE idea of relational database system is hat you only need to store the id field, and then the rest of the fields (like supple name) can then be viewed. First, viewed how? I find subdatasheets to be too cumbersome. (I am visually impaired, so the less I have to scan through and pick out the better.) Is there a better way to link to the Supplier Table that I'm missing? I guess my reasoning went something like this: Queries and reports are useful for sorting or displaying data in a format that's not readily available in a table (especially using fields from more than one table). But if I can set up a table to show exactly what I want to see, why not do that, rather than bothering to write a query, which I then have to go to and run just to see what I want? Put another way, why duplicate the table with a query, if I can just have the table show what I want in the first place? (Answer: Because that ain't so easy after all!) But it turns out that I apparently have to write a query anyway to put the info in the table the way I want it. Still, that saves navigating to and running another object every time I want to see the info. But then, I could just open the query, rather than even opening the table, which I guess is the point after all. Sorry I'm rambling; I'm "thinking out loud" here. You say: The way the northwind works is that the id is stored, but the form (or sub-form) is based on a query. When you set the id, then the name etc magically will appear!. Those queries need to be left join queries.. I'm sorry, but I have to ask: If the normal and recommended way is to store and display the [Supplier] ID# in the [Products] table, WHY is the sample database written to display [Supplier] Names in the [Products] tables, which is the ABnormal and NOT recommended way? This is confusing to us newbies (at least this newbie), who presumably use the sample to help us understand the normal way to use a database. Also, I'm afraid I don't quite follow. 1) Are you implying that I should be looking at the form within the table somehow? 2) Are you saying that the combo box in the form, which displays Supplier Names to choose from for data entry, but stores the ID in the table, is itself a kind of query? 3) I don't understand what you mean by "set the ID." 4) I guess I'm not clear yet on join queries, and I'm not sure which queries you are referring to here. You asked: Also, it is not clear: I want the combo box to show me a list of names (not ID#s), AND I want to store Supplier NAMES (not ID#s) in the Products table. what do you mean list of names? do you mean suppler names? or do you look up a name..and what the suppler name to appear? (I guess you are not clear on name vs product name...or do you mean one and the same?). Sorry I wasn't clear. What I had in mind was this: I would use a Products Form for data entry, to feed a Products Table. The Products Table would have a Supplier field. The corresponding field on the Products Form would be a combo box (or list box, if you prefer). In order to enter the Supplier to go with a particular product, I would use this combo/list box (on the Products Form) to choose the Supplier Name (so I don't have to memorize all Supplier ID#). I understand the norm is to then store the Supplier ID in the Supplier field, in the Products Table. What I originally intended, however, was to store, or at least display, the Supplier Name (rather than ID) in the Products Table, as well as displaying it in the form. My thinking was, "If I view the data in the Products Table, what good is the Supplier ID to me, unless I have memorized which Supplier belongs to which ID?" But I guess I'm SUPPOSED TO view the data either one record at a time in a form, or in a query, or in a report. The table is apparently not MEANT for viewing; it's just a storage closet from which to pull out whatever I want to view somewhere else. Did I finally get it? I guess most of this is a moot point if I just stick to the normal way of doing things. Thanks again for your help. "Albert D. Kallal" wrote: As others mentioned, the WHOLE idea of relational database system is hat you only need to store the id field, and then the rest of the fields (like supple name) can then be viewed. The way the northwind works is that the id is stored, but the form (or sub-form) is based on a query. When you set the id, then the name etc magically will appear!. Those queries need to be left join queries.. Also, it is not clear: I want the combo box to show me a list of names (not ID#s), AND I want to store Supplier NAMES (not ID#s) in the Products table. what do you mean list of names? do you mean suppler names? or do you look up a name..and what the suppler name to appear? (I guess you are not clear on name vs product name...or do you mean one and the same?). When I look at the table, I want that info (Supplier NAME) to just be there, without having to fish through subdataforms, separate queries, or reports. Yes...you build query to do this. You then build combo box on the form that displays that returns the id..and this comb box can search/display by any column you want..but you always still store the id. Is there a REASONABLE way for a non-programmer to accomplish this? You can use a query to do the above. Just make the combo box return the "id"...and make sure the query has as many (or all) fields from the other table. once done, then no code at all needs to be written here... -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
show data from a table based upon combo box | Daiuy | New Users | 3 | January 8th, 2005 03:54 PM |
Access 2000 -vs- Access 2003? | Mark | General Discussion | 5 | November 30th, 2004 06:36 AM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Access XP Compared to Access 2003 | Mardene Leahu | New Users | 1 | October 1st, 2004 05:11 AM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |