Query?? or something else?

  • Thread starter Thread starter Heath
  • Start date Start date
H

Heath

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
 
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
 
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.
 
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).
Click to expand...

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.
 
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
Click to expand...
 
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
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
Click to expand...
Click to expand...
 
Back
Top