Prev: SQL vs. VBA
Next: automatically assign value to query
From: John Spencer on 28 Jan 2010 11:05
If you want the value of Company your query must include the Company table
joined to the employee table.

SELECT Employee.*, Company.Company
FROM Employee INNER JOIN Company
ON Employee.CompanyID = Company.CompanyID

If it is possible that an employee record can be created without a companyId
then you would probably want to change the INNER JOIN to a LEFT JOIN.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Heath wrote:
> 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
>>
>
>
First  |  Prev  | 
Pages: 1 2
Prev: SQL vs. VBA
Next: automatically assign value to query