Lookup from on tanle adding to another

[jcaptchaos2]

I need some help with a lookup, in excel for me it is very easy but am struggling here with access. Here is what I want to do, I have a table named employee listing and the columns are employee #, Employee Name, Department and Shift. I have a user form that data is being entered into that goes into another table, I want to add a column to that table that looks at the employee # that was entered and brings over the shift number. I use the vlookup formula all the time in excel so I thought I could figure it out but no luck, could someone give me some advice?

 

[SydneyGeek]

If you base the form on a query that uses the 2 tables, joined on Employee #, you should just get the value when you add that field to the form. The advantage of doing this is that the data is only stored in one place so you don't get inconsistencies.
You can also use a combo box to look up the Employee #, and push data from its columns into the appropriate fields. This is typically used in ordering systems where you may want to offer a client a discount without affecting the normal price; and you don't want price increases to affect historical data.
Assuming that
1. MyCombo has Employee #, Full Name, Shift as the 3 columns.
2. You want to push Shift into another control (txtShift).
The AfterUpdate code for the combo would look like this:

Me.txtShift = Me.MyCombo.Column(2)

Note that column numbers start from 0, so Column(2) is actually the third column.

Denis

 

[jcaptchaos2]

Sydney,

Thanks for the advice, I am very novice in Access and can not seem to get either to work, I am much better in excel so maybe if I put how I would do it in excel down you could tell me if it is possible to do it in access that way. In this example my employee number that is in my table 1 would be in B9 in the example below, A1:D100 would be my table 2 employee listing table and in that table the 4th column over would have the shift # in it. Is it not possible to pull data into a table from another table by using this sort of code? or it may be easier the way your explaining and I just don't understand it.

=vlookup(B9,A1:D100,4,false)

 

[jcaptchaos2]

I will try to explain a little better, I have a form that has 3 text boxes on it the first one named "Employee Number" the person scans their employee badge, the other 2 text boxes are auto one has the time =Time() and one has the date =Date() so the only thing the person does with this form is scan their badge. The information from this form goes to a table Named "Payroll Time On" That table has "Employee Number", "Time On' and "Date" I am trying to add a column with a lookuo that would look at the employee number in the Payroll Time On table and then find it in a table named "Employee Listing Jac" and grab the 4th colum over which is the shift number. Seems like it should be easy to do but I am making a mess out of it.