InfoPath Dev

Can anyone advice on the best approach to setting up this relational database. I'm so close to just going with a single EMP table and fighting it out with queries at the end of the day.

So is the Unit to Employee relationship one to many? When you set up the main data connection in your form, are you setting the Unit table to not be repeating?

It's a one to many relationship(Unit-EMP table). I have both tables linked properly in the form. I just don't know how to set up form so I can update the unit of an employee as he/she moves around units. I believe the EMP table should be repeating since I want to update many records.

It sounds like you have it set up correctly. What is the exact error you are getting?

Hilary, you seem to know InfoPath well :). He's a question: Is there anyway I can have a main EMP table with EMPID (primary key) and have an EMP History table with RecordID (primary key) and EMPID( foreign key) and when I submit a change in the main EMP table a new row is copied in the EMP History table? Is this something I need to do in Access or can I do this in InfoPath?

I don't think you would be able to do this with out of the box InfoPath functionality. I don't believe that Access has triggers -- SQL Express does, I think, and it is free. You might want to consider moving your DB to SQL. Because, really, a trigger would work nicely for creating a history table.....