Filter report using employee number

[chuma]

Hi, i have an access table that contains information about how many employees worked on a particular vehicle, it has 4 fields for employees with there employee numbers (Empl_Name1, Empl_ManNo1 and Empl_Name2, Empl_ManNo2 repectively.) I want to filter the report using the employee number and and its displaying the entire row from the table.

How can i filter and display a report with that just record that i need.

Thanks in advance

[Seth Schrock]

If you have the option of redesigning your table structure, you should look at Database Normalization and Table Structures. Having your database normalized would make this much simpler and also keep you from having to redesign if you would ever need to have five employees working on a vehicle.

If redesigning isn't an option, then we will have to do it the hard way. I would recommend basing the table on a query and then having your WHERE clause do the filtering instead of the report's Filter property. Then I would have a form that would have a control (textbox or combo box) that would supply the search criteria. I'll call this form frmSearch with the textbox control called txtEmpNo. The query would then look like this:

SELECT *
FROM [I]YourTableName[/I]
WHERE Empl_ManNo1 = Forms!frmSearch!txtEmpNo 
   OR Empl_ManNo2 = Forms!frmSearch!txtEmpNo 
   OR Empl_ManNo3 = Forms!frmSearch!txtEmpNo
   OR Empl_ManNo4 = Forms!frmSearch!txtEmpNO

All you would have to do is enter the employee number for the person you are looking for into the textbox and then run your report.

This would be much simpler if the tables were normalized.

[chuma]

Thanks Seth, i will try the hard way first then if i fail will consider redesigning

[twinnyfo]

Seth is correct that this DB should be properly normalized.

Another method would be to create another field in your query, which speicifically matches your desired Emplyee ID:

SELECT *, IIf(Empl_ManNo1 = Forms!frmSearch!, txtEmpNo, 
    IIf(Empl_ManNo2 = Forms!frmSearch!, txtEmpNo, 
    IIf(Empl_ManNo3 = Forms!frmSearch!, txtEmpNo, 
    IIf(Empl_ManNo4 = Forms!frmSearch!, txtEmpNo, 0)))) AS MyEmp
FROM YourTableName 
WHERE IIf(Empl_ManNo1 = Forms!frmSearch!, txtEmpNo, 
    IIf(Empl_ManNo2 = Forms!frmSearch!, txtEmpNo, 
    IIf(Empl_ManNo3 = Forms!frmSearch!, txtEmpNo, 
    IIf(Empl_ManNo4 = Forms!frmSearch!, txtEmpNo, 0)))) <> 0

Then, in your report, or wherever you need the data, you simply refer to MyEmp as the field name.

Either way, it is ugly and will probably cause problems in the future.