Query?? or something else?
in [Queries]
|
Prev: SQL vs. VBA
Next: automatically assign value to query
From: Heath on 25 Jan 2010 21:57 Greetings all, I am new to actually messing around in access and can�t seem to find a solution to my problem. I have two tables one is Employees and the other is company. They are already set up with a one-to-many relationships. The problem I am having is when adding a new employee I am limited to selecting a value that exists in the company table, which can be 1 though 5. Is there a way though query or some other means to show the name rather than the ID for that field? Table outline [Employee] CompanyID FirstName LastName Department Title [Company] CompanyID Company So rather than showing the ID for the first company (1) I want to show the actual companies name (Andersons). Thanks for the time! Heath -- Heath
From: Tom van Stiphout on 25 Jan 2010 22:54 On Mon, 25 Jan 2010 18:57:41 -0800, Heath <NoAddress(a)here.com> wrote: You probably currently have a textbox for Employee.CompanyID. Replace it with a 2-column dropdown that gets its rowsource from the Company table, and is bound to (= has its ControlSource set to) Employee.CompanyID. The dropdown has columnwidths set to "0;1" to hide the first (CompanyID) column. Check out some sample databases, and you'd likely find examples of this technique. -Tom. Microsoft Access MVP >Greetings all, > >I am new to actually messing around in access and can�t seem to find a >solution to my problem. I have two tables one is Employees and the other >is company. They are already set up with a one-to-many relationships. >The problem I am having is when adding a new employee I am limited to >selecting a value that exists in the company table, which can be 1 >though 5. Is there a way though query or some other means to show the >name rather than the ID for that field? > >Table outline >[Employee] >CompanyID >FirstName >LastName >Department >Title > >[Company] >CompanyID >Company > >So rather than showing the ID for the first company (1) I want to show >the actual companies name (Andersons). > >Thanks for the time! >Heath
From: KARL DEWEY on 25 Jan 2010 23:25 >> They are already set up with a one-to-many relationships. You should set Referential Integerity and Cascade Update. The best way is to use queries feeding forms. SELECT [Company].[Company], [Company].CompanyID FROM [Company] ORDER BY Company; SELECT [Company].CompanyID, FirstName, LastName, Department, Title FROM [Company] LEFT JOIN [Employee] ON [Company].CompanyID = [Employee].CompanyID ORDER BY Company, LastName, FirstName; Use the first query for the main form (Company) and second for subform (employee). Set Master/Child links between form/subform using CompanyID. To enter new employee scroll company form to the company. -- Build a little, test a little. "Heath" wrote: > Greetings all, > > I am new to actually messing around in access and can't seem to find a > solution to my problem. I have two tables one is Employees and the other > is company. They are already set up with a one-to-many relationships. > The problem I am having is when adding a new employee I am limited to > selecting a value that exists in the company table, which can be 1 > though 5. Is there a way though query or some other means to show the > name rather than the ID for that field? > > Table outline > [Employee] > CompanyID > FirstName > LastName > Department > Title > > [Company] > CompanyID > Company > > So rather than showing the ID for the first company (1) I want to show > the actual companies name (Andersons). > > Thanks for the time! > Heath > > -- > Heath > . >
From: John W. Vinson on 26 Jan 2010 01:25 On Mon, 25 Jan 2010 18:57:41 -0800, Heath <NoAddress(a)here.com> wrote: >Greetings all, > >I am new to actually messing around in access and can�t seem to find a >solution to my problem. I have two tables one is Employees and the other >is company. They are already set up with a one-to-many relationships. >The problem I am having is when adding a new employee I am limited to >selecting a value that exists in the company table, which can be 1 >though 5. Is there a way though query or some other means to show the >name rather than the ID for that field? > >Table outline >[Employee] >CompanyID >FirstName >LastName >Department >Title > >[Company] >CompanyID >Company > >So rather than showing the ID for the first company (1) I want to show >the actual companies name (Andersons). Are you doing this *IN THE TABLE* datasheet itself? If so, you're missing most of the power of Access. Tables aren't designed for data entry or data interaction, but for data storage! The simplest way to do this would be to use a Form based on the company table, with a continuous Subform based on the employee table; use CompanyID as the master/child link field. You can put an unbound combo box on the Company form to navigate to a chosen company name (the toolbox combo box wizard will help you build this). Users should never need to even SEE (much less enter) an autonumber ID or a number linked to one. -- John W. Vinson [MVP]
From: Heath on 27 Jan 2010 23:23
Hi All, Thanks for the help with the query information it worked very well! I do have another question and not sure if this is something access can do. When someone connects to the access database using Visual basic and uses a select statement to get the record they get the number value that corresponds to the foreign key and not the name for that field. Taking my example from my first post� [Employee] CompanyID FirstName LastName Department Title [Company] CompanyID Company When they do a select for the Employee they get a value of 1 for the CompanyID (which is what it actually is), is there a way to get the company name instead of the CompanyID value? > Greetings all, > > I am new to actually messing around in access and can�t seem to find a > solution to my problem. I have two tables one is Employees and the other > is company. They are already set up with a one-to-many relationships. > The problem I am having is when adding a new employee I am limited to > selecting a value that exists in the company table, which can be 1 > though 5. Is there a way though query or some other means to show the > name rather than the ID for that field? > > Table outline > [Employee] > CompanyID > FirstName > LastName > Department > Title > > [Company] > CompanyID > Company > > So rather than showing the ID for the first company (1) I want to show > the actual companies name (Andersons). > > Thanks for the time! > Heath > -- Heath |