working with names in 2 fields

[Guest]

Every tells me I should have my names in 2 fields and an autonumber employee
key. The problem is the key is meaningless so if there are say a hundred
employees it isn't possible to know everyone's ID.

I have three tables. One with company info *where the expenses come from*.
One with Employee Info and one with the details of each expense.

I tried creating a form with the firstname lastname fields where I could
type in

John Doe McDonalds $20.00 but every time I add a new expense
for John Doe it creates a new John Doe. Is there a way that if there is no
John Doe it will create one but if there is it'll just add the expense to
current John Doe?

Ryan

 

[fredg]

Every tells me I should have my names in 2 fields and an autonumber employee
key. The problem is the key is meaningless so if there are say a hundred
employees it isn't possible to know everyone's ID.

I have three tables. One with company info *where the expenses come from*.
One with Employee Info and one with the details of each expense.

I tried creating a form with the firstname lastname fields where I could
type in

John Doe McDonalds $20.00 but every time I add a new expense
for John Doe it creates a new John Doe. Is there a way that if there is no
John Doe it will create one but if there is it'll just add the expense to
current John Doe?

Ryan

So you think you are the only one who has 'a hundred employees' and
therefore the tried and true method of identifying employees by
EmployeeID is wrong? You don't need to 'know' each EmployeeID, all you
need is to have Access know it.

The use of a Combo box in a form (with it's row source set to show
EmployeeID, Employee Last and First Names as well as any other
identifying data, such as SSN , or Department, etc., will allow you to
select the correct Employee while storing that EmployeeID value.
Look up Combo Box in Access help.

 

[ManningFan]

I concur with G-Money above. I also think your database isn't set up
correctly, because John Doe should be in his own table and only appear
once. Then John Doe's ID would appear several times in the Details
table.

Try using a continuous subform, hiding the Employee ID.

 

[jahoobob via AccessMonster.com]

fred,
I wholeheartedly agree
We have over 800 current and former employees in our db and have thre James
Jones so our combos have Department so we can distinguish between same names.
How would you handle that situation without an ID number. Ryan, wouldn't
know which James Jones had what expense with his method.
I say, EmployeeID. Don't create an Employee table without it!
If you have a number already, such as a PeopleSoft number, you can use it
because each is unique and most employees will eventually come to remember
theirs and they can be used for lookup.

Every tells me I should have my names in 2 fields and an autonumber employee
key. The problem is the key is meaningless so if there are say a hundred

[quoted text clipped - 12 lines]

Ryan

So you think you are the only one who has 'a hundred employees' and
therefore the tried and true method of identifying employees by
EmployeeID is wrong? You don't need to 'know' each EmployeeID, all you
need is to have Access know it.

The use of a Combo box in a form (with it's row source set to show
EmployeeID, Employee Last and First Names as well as any other
identifying data, such as SSN , or Department, etc., will allow you to
select the correct Employee while storing that EmployeeID value.
Look up Combo Box in Access help.

 

[John Vinson]

Every tells me I should have my names in 2 fields and an autonumber employee
key. The problem is the key is meaningless so if there are say a hundred
employees it isn't possible to know everyone's ID.

Not a problem; an autonumber ID is used behind the scenes anyway, and
NOBODY needs to even see it, much less memorize it.

I have three tables. One with company info *where the expenses come from*.
One with Employee Info and one with the details of each expense.

I tried creating a form with the firstname lastname fields where I could
type in

John Doe McDonalds $20.00 but every time I add a new expense
for John Doe it creates a new John Doe. Is there a way that if there is no
John Doe it will create one but if there is it'll just add the expense to
current John Doe?

Don't store the name in the expenses table. Store the ID, and use a
COMBO BOX - one of the handy tools that Access provides - to *store*
the ID while *displaying* the name. That way the computer sees the
unique ID, the user sees the name (and perhaps other fields to
identify which John Doe you mean, the Chief Financial Officer or the
intern in Purchasing), and both are satisfied.

John W. Vinson[MVP]

 

[Guest]

Thanks John. That sounds great but but 2 questions if you don't mind.

1 - Would that allow me to add a name to employee database by adding
to the expense table. If Ron Smith has never had an expense *and therefore
isn't in the employee table* can I just add his expense and have him show up
in the employee table?


2 - How do I go about hiding the ID behind a combo box. I've been
searching for combo box's in kb and even took a trip to the book store and I
can't find an example like that. I may just not seeing... kind of new to
access..

Thanks again,
Ryan

 

[Guest]

I think I found how to do as you suggested I read that you need to build a
query in the row source box and then set the width of the field you don't
want to show as 0.
On the updating the other table part the site I was on offered a download
but thats hard for me because the comp with the internet and the comp with
the access are different. Everyone seems to be suggesting its really hard
though. Is there a beginner accessible way to

Populate underlying table when new entries are keyed into combo box

as the site I found refered to it as?
Ryan

 

[John Vinson]

Thanks John. That sounds great but but 2 questions if you don't mind.

1 - Would that allow me to add a name to employee database by adding
to the expense table. If Ron Smith has never had an expense *and therefore
isn't in the employee table* can I just add his expense and have him show up
in the employee table?

No, of course not. The expense table shouldn't HAVE any names in it,
period! A person is a person; an expense is an expense. They are
different kinds of Entities and information about them belongs in
different tables.

As you get further advanced, you'll find that you can use VBA code in
a combo box's Not In List event to (say) open the Employee (or People)
table, and add a new record when the person you're looking for isn't
in the table... but let's get the basics first.

2 - How do I go about hiding the ID behind a combo box. I've been
searching for combo box's in kb and even took a trip to the book store and I
can't find an example like that. I may just not seeing... kind of new to
access..

Ummm... have you tried Access Help? I agree, it's badly indexed and
hard to search, but if you search for Combo you will find it.

A typical combo box (one generated by the combo box wizard for
example) might have two (or more) columns. The combo would be based on
a Query, for example, a query returning the PersonID and the person's
name; you might include their department just in case you have two
people with the same name:

SELECT PersonID, LastName & ", " & FirstName, Department
FROM People
ORDER BY LastName, FirstName;

might be the SQL view of the query.

The Combo's properties (again, the wizard will do this for you, I'm
just popping the hood to show you how the engine works <g>) would be:

ControlSource: PersonID, the field in your expense table that
indicates whose expense this is
RowSource: the query above
ColumnCount: 3
BoundColumn: 1 - store the PersonID into the PersonID field in the
expense table
ColumnWidths: 0;.75;.5

i.e. don't show the PersonID at all - zero width; allow 3/4 inch for
the person's name; half an inch for the department. When the combo is
dropped down you'll see both fields; when it's not, you'll just see
the name - but the computer will see the PersonID.

Seriously - open the Northwind sample database and look at the Orders
form. It shows a lot of useful details if you study it.

John W. Vinson[MVP]

 

[John Vinson]

Everyone seems to be suggesting its really hard
though. Is there a beginner accessible way to

Populate underlying table when new entries are keyed into combo box

as the site I found refered to it as?

Well, that's doable, and not *too* hard, but it's not trivial. You
need to get into some VBA code ("modules") to do it. As a rule it's
not all that frequently needed - surely if you're doing expenses for
your employees, you know who your employees are when you start??

John W. Vinson[MVP]

 

[Guest]

Well, that's doable, and not *too* hard, but it's not trivial. You

need to get into some VBA code ("modules") to do it. As a rule it's
not all that frequently needed - surely if you're doing expenses for
your employees, you know who your employees are when you start??

John W. Vinson[MVP]

Well I tried to simplify what I was doing a little bit so that it was easier
to explain and I thought I just had a question on one part. The people are
actually not employees but interview candidates so we can see quite a few
ppl. Some of them come multiple times and they could have a hotel, car rental
and/or a plane ticket.

On another note I realize names shouldn't be in the expenses table. I
mispoke what I was thinking was if I could create a form which would add an
expense to the expense table and add the responsible candidate to the
employee *candidate* table if they aren't there already.

Ryan

I was going to go ahead and give you answer credit but I couldn't see an
option to do it....I thought I read it had to be the person who asked the
question.