How to look up a data from another table to a table?

[Guest]

I am very interested in Microsoft Access, but I find it rather difficult. Now
I am creating a database about human resources but I have faced a lot of
problems. First, I created a table named "StaffID", which included the
StaffID, FullName, HireDate fields and so on. Then I created another table
called "StaffInfo", which also has the StaffID and FullName fields. I want to
use lookup wizard to get the data from the first table. For instance, when I
entered the data into the StaffID, I open the Down button and get one, let's
say, 0001. When I tabbed into the next field, FullName, I did the same and
get Peter Carsen. If my table has many rows, i.e. more than 100 staff, then
this job takes time and is not convenient. Are there any ways to get lookup
data very quickly and scientifically?
Another problem is that I created a query bringing together 2 tables. Of
course, these 2 tables had some same fields, like StaffID, FullName,
DateofBirth, HireDate. I saw the query has no blank row at the end to add a
new record. And the form based on it does not, either. So I couldn't add any
new records into the form. What I should do to tackle this problem?
Thanks for any assistance helpful to me.

 

[John Vinson]

On Mon, 31 Jan 2005 20:47:03 -0800, "Hoa Anh" <Hoa

I am very interested in Microsoft Access, but I find it rather difficult. Now
I am creating a database about human resources but I have faced a lot of
problems. First, I created a table named "StaffID", which included the
StaffID, FullName, HireDate fields and so on.

I'd suggest calling this table Staff - the staff ID is a fieldname.
The Table should represent a type of "Entity" - in this case, a staff
member; each record would represent all of the relevant information
about a person on your staff.

You may want to use LastName and FirstName as separate fields. It's
much easier to concatenatate them for display purposes than to split
them apart.

Then I created another table
called "StaffInfo", which also has the StaffID and FullName fields.

Ummm...

Why?

You already HAVE that information. There is absolutely *NO* need to
create a second table to store this information redundantly. If you
need a query listing the fullname, simply base a Query on the Staff
table selecting that field.

I want to
use lookup wizard to get the data from the first table. For instance, when I
entered the data into the StaffID, I open the Down button and get one, let's
say, 0001. When I tabbed into the next field, FullName, I did the same and
get Peter Carsen. If my table has many rows, i.e. more than 100 staff, then
this job takes time and is not convenient. Are there any ways to get lookup
data very quickly and scientifically?

Yes. Enter the name ONCE, and only once. It is not necessary to "look
it up" or to type anything in for each row.

Another problem is that I created a query bringing together 2 tables. Of
course, these 2 tables had some same fields, like StaffID, FullName,
DateofBirth, HireDate.

The StaffID will probably exist in several tables - but *NONE* of
these other fields should. Relational tables are based on the
"Grandmother's Pantry Principle" - "A place - ONE place! - for
everything, everything in its place". If you have a table of (say)
StaffHours worked, it should have fields for the StaffID, the
WorkDate, and the HoursWorked; it is neither necessary nor beneficial
to have any of the other fields from the Staff table.

Instead, you would use the Relationships window to define a
relationship between the Staff table and the StaffHours table, joining
the two tables on StaffID and checking the "Enforce Relational
Integrity" checkbox. This prevents the addition of an Hours record for
a nonexistant staff member.

I saw the query has no blank row at the end to add a
new record. And the form based on it does not, either. So I couldn't add any
new records into the form. What I should do to tackle this problem?
Thanks for any assistance helpful to me.

Sinc I have no idea what this second table was intended to do, nor how
you constructed the form, I don't know.

In my example of the Hours table, you would use a Form based on the
Staff table (to enter the information specific to the Staff table),
with a Subform based on the StaffHours table. Use the StaffID as the
master/child link field, and it will automatically sychronize the data
in the two tables.

John W. Vinson[MVP]