AboutScottgem Expertise I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.
Experience I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.
Organizations Author of Microsoft Office Access 2007 VBA Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing
Question Scott:
I use a combo box to look up physician name and address to add to my patient file. Each physician has a unique number that I use for relationships. However, when I index this number as a primary key the combo box no longer lists the physicians alphabetically (important because I have duplicate last names) Your thoughts?
Thanks
bob
Answer First, the ONLY thing that should be in the patients table is the Physician ID. You can pull name and address using joins. This is also assuming that a patient will only have ONE physician relationship. If they can have more, then you need to use a join table.
The combo should have a RowSource like the following:
SELECT PhysicianID, PhysicianName FROM tblPhysicians ORDER BY PhysicianName;
You set indexes on the table level, but you can have an Order By clause in a query. Note, I left off the address. If you need the address to further identify a physician you can add it as a third column. In addition, the column widths property of the combo should be set so the first column is 0" (ex: 0";2"). This will surpress the display of the ID and display the name. Finally, if you also want to display the physician's address on the patient form, you can do so by adding the address column to the query and then adding an unbound textbox to your form and setting the data source to:
=cboPhysician.Column(2)
This will display the contents of the 3rd column (column count starts at 0) in the control assuming the combo is named cboPhysician.
