using combo boxes in a subform

[melda11]

I have this employee table that have column names - Name,Job description,etc and Deduction1,Deduction2....... DEDUCTION 22.

Table looks something like this:

Name Job Descript Deduction1 Deduction2 Deduction3
Peter Clerk $10 $0 $2

I want to show this on a form but I dont want to use textboxes because it will show all the deductions and this will look messy.I reserched from the net and I found out that there is a way of doin it using a combo box in a subform.This should show the employee details and only the deductions that are being deducted plus the amount.

I made another table that looks like this
Deduction_code Description
Deduction1 Health Insurance
etc..... etc...


What I think is this.In order to use a combo box in a subform I need a relational table to look like this.
Employee_code Deductions
58 Deduction1
Deduction2
59
60 Deduction 10


Can anyone pls tell me if there is any othere way of doin this and if it is possible to use a combo box in a subform for this scenario.I have been wrecking my brains with solutions to this.I have tried using query statements and even lookup tables but it is not working.

Iam not a pro with ms access.pls help...

Thanks

 

[pbaldy]

Your table is not normalized, which you may want to search on. You should have a separate table that contains 1 record per employee/deduction, like:

EmployeeID - DeductionID - Amount

so if I had 3 deductions, I would have 3 records in that table. You could then display each employee's deductions in a subform.

 

[melda11]

thanks i wil....

 

[melda11]

so i need to use a query to make my table normalised right??

 

[pbaldy]

Well, normalizing the tables refers to their actual structure. I would remove the various deduction fields from your employee table and implement the new table I mentioned earlier. If you have a lot of existing data to convert, a query could certainly help copy the data to the new table, but it would not on its own make the table normalized.

 

[melda11]

But the problem is that this table I imported it from excel.When changing the structure I have to look at each employee and see which deductions code is being deducted.What if there is a lot of employees, doing this will be time consuming.What do u think???

 

[melda11]

its more like I pasted the data from excel.

 

[pbaldy]

Like I said, a query can help there.

SELECT EmployeeID, "Deduction1", Deduction1
FROM TableName
WHERE Deduction1 > 0
UNION ALL
SELECT EmployeeID, "Deduction2", Deduction2
FROM TableName
WHERE Deduction2 > 0
UNION ALL
And so on