ambiguous join query error

[Helystra]

I need some help with a join query/table I'm trying to create. I'm fairly new to access so please bare with me if I'm doing this all wrong.



I have created the following tables with their pertinent fields:

tblEmp: EmpRowID (PK autonumber), EmpID (company assigned ID), LastName, FirstName, MI, Status, Staff Type

tblBudgetCodes: BCRowID (PK autonumber), BudgetCodeType(wage type), RateType (reg or ot), Fund, Function, Object, SubObject, Org and BudgetYear (these 6 fields each contain a separate section of the budget code itself)

tblEmpWages: WagesRowID (PK autonumber), EmpRowID (FK from tblEmp), BCRowID (FK from tblBudgetCodes), Rate ($/hr)

Relationship information:
1 to many relationship set up between tblEMP and tblEmpWages
1 to many relationship set up between tblBudgetCodes and tblEmpWages.

I have 12 pre-set budget codes and almost 700 employees. Each employee can have anywhere from 2 to all 12 budget codes with different rates for each. I had originally intended the tblEmpWages to be where all the joined information was housed. But I'm having trouble creating a join query to bring all the employees and the budgetcodes together. I'm getting an ambiguous outer join error on the query. I'm wondering if my problem is in the join type and relationship set up.

Any ideas?

[Helystra]

I changed the join type in the relationship tables to left joins from tblemp to tblempwages and then again between tblbudgetcodes and tblemp wages. Then I created a query to join all the budget codes and all the employees first and then used that query to join to the empwage table.

That allowed me to pull all the employees and have all 12 budget codes applied to each employee. But now the wagerowid field is not populating with autonumbers and I can't input any data on the form I've created.

[hrenee]

http://www.rogersaccesslibrary.com/f..._topic238.html

The ER Diagraming may help you out.

[Helystra]

Thanks because now I'm having a problem entering data on the form that ties into that query. The autonumbers aren't assigning either. So I'll have a look at that.