I am new at Access, but have been asked to create a db for employee test lookups. I have put each test and it's scores in a separate table. Now I need a query which, when I type in an employee number, will find all tests that employee has taken, and display only those tests. I have used Relationships to link the employee number, but now am stuck as to how to get it to generate those test results and pass over the ones the employee has not taken. Someone please help!

In the Test Scores table, did you include the EmployeeID?

If so, your query should be designed with a one-one join of EmployeeID from each talbe showing fields from the Test Scores Table.

I have one separate table for each test (Color Perception is one table, Personnel another, etc.) and each table has Employee ID as the Primary Key. I also have a main form which has only the Employee ID and Name fields. I have linked them all together. I would like to be able to type in a name and have it go through all the tests, pick out which that person has taken, and display those.

I think were on another page here. I am looking at your table design and finding a better way. Right now, I can query all the tables together to get list that displays info like this:

EmplyeeID Test1Score Test2Score Test3Score
John Smith 100 98 75
Sue Jones 75 75 95

Or one that lists ALL employees and ALL the scores, empty or not.

What I think you may consider doing is creating a new table that lists all the tests available and identifying them by a TestID number.

Still working on your solution.

Yes, I think that is your best bet...

tblEmployees
EmployeeID
Name,etc.

tblTests
TestID
TestName

tblTestsTaken
EmployeeID
TestID
Score

You will be able to work more easily with this structure.

You would build a form based off of a query that joins the tblEmployee [EmployeeID] with the tblTestsTaken [EmployeeID].

On the form you would put a combo box that will display records that match the EmployeeID (or name if you want to see the name instead).

Another approach would be to build a form off of the tblEmployees and have a subform which draws its records from tblTestsTaken. (You will probably like that even better!)

If you need more help, just ask. Unlike a few others, I DON'T mind helping those with less experience.

[This message has been edited by jwindon (edited 09-07-2001).]

She needs a "Many to Many" relationship.

"Many" Employees can have "Many" test scores........

tblEmployees
EmpID ' PrimaryKey
Name,etc.

tblTests
EmpID ' Compound PrimaryKey (ForeignKey for tblEmployees)
TestID ' Compound PrimaryKey (ForeignKey for tblTestDetails)

tblTestDetails
TestID ' PrimaryKey
TestDescription
TestScore

"tblTests" is the Junction table for the "Many to Many Relationship".

RDH

[This message has been edited by R. Hicks (edited 09-07-2001).]

Yeah Rick, I like your idea even more...taking the scores out and putting them in another table alone.

We do both agree a structure change is necessary mlh. As I said, I will help you if you want and if I can.