Returning Fields depending on value of other field

[uselessataccess]

Hi there,

Would appreciate any help you genii could give me.

Okay,

I have 8 fields (amongst others) in job description table:

4 employee (text) fields (called emp1, emp2.. ..emp4) and
4 employee hours (number) fields (called emphours1.. ..emphours4)

What I need to do is design a query that takes in a employee name and returns all jobs with their name it and the hours they worked on it.

I have the achieved the first bit by using criteria and OR statements. I.e. if emp1 OR emp2 OR emp3 OR emp4 = "John Smith" return. I did this just in the design view of the query.

Now the part I CANT do; the returning of the hours worked. So I want it to return emphours1 value if emp1 = "john smith" or emphours2 if emp2 = "john smith" etc.

What I don't want (as is happening at the moment) is emphours1 to emphours4 all being returned. This just leads to a confusing jumble of numbers on the report!

I suspect some sort of IFF statement may do the job but I don't know where to start.

 

[neileg]

Your design is wrong (in a very common way for new Access users). Instead of having 4 employee fields and 4 hours fields in your table, you should have a second table that will look a bit like this:
tblEmployeeHours
EmpHoursID, primary key
JobID, foreign key (link to main job table)
Employee, employee name or identifier
Hours, number

This restructure is a process called normalisation. Once your data is normalised, you queries become really simple.

Look up normalisation, either in these forums or Wikkipedia is a good start.