DJ P
asked on
MS Access Error - specified field could refer to more than one table listed
Hello experts. I have a MS Access Report that when run gives an error that says the specified field '[DOH]' could refer to more than one table listed in the FROM clause of your SQL Statement. I go into the design mode of the report and see the sql statement is as follows:
SELECT tbl_Employee.Department, tbl_Employee.EmployeeNumbe r, tbl_Employee.FirstName, tbl_Employee.LastName, Format([DOH],"mm/dd/yy") AS [Date of Hire], Format([DOB],"mm/dd/yy") AS [Date of Birth], tbl_PayrollInfo.[Hrly Rate], tbl_Employee.[Bi-Weekly Deductions - MED125], tbl_Employee.[Bi-Weekly Deductions - HSA], tbl_Employee.FSAMED, tbl_Employee.FSADEP, tbl_Employee.[LTD Amt], tbl_Employee.[401K Amt], tbl_PayrollInfo.[Marital Status Federal], tbl_PayrollInfo.[Direct Deposit], tbl_PayrollInfo.Salaried, IIf([Salaried]=Yes,([Hrly Rate]*80),[Hrly Rate]) AS Pay, tbl_Employee.DOH, tbl_Departments.Department Name, tbl_Employee.[401K Compliance Codes], tbl_PayrollInfo.[Claim Federal], tbl_PayrollInfo.[Additiona l WH Federal], tbl_PayrollInfo.[Additiona l WH State], [Qry_PTO Balance].Remaining, IIf([Salaried]=Yes,([Hrly Rate]*80),0) AS [Bi-weekly Salary], tbl_Employee.[Terminated or Resigned], tbl_Employee.DOB
FROM (tbl_Departments RIGHT JOIN (tbl_Employee LEFT JOIN tbl_PayrollInfo ON tbl_Employee.EmployeeNumbe r = tbl_PayrollInfo.EmployeeNu mber) ON tbl_Departments.Department = tbl_Employee.Department) LEFT JOIN [Qry_PTO Balance] ON tbl_PayrollInfo.EmployeeNu mber = [Qry_PTO Balance].EmployeeID
WHERE (((tbl_Employee.[Terminate d or Resigned]) Is Null) AND ((tbl_Employee.DOB) Is Not Null))
ORDER BY tbl_Employee.Department, tbl_Employee.LastName;
Obviously it has something to do with the DOH field and when I go into the query builder I see DOH listed twice that shows like the following (keep in mind two separate fields as I did not represent the vertical lines to seperate:
Field: Date of Hire: Format([DOH],"mm/dd/yy") DOH
Table: tbl_Employee
Sort:
Show: has a check mark has a check mark
Criteria:
Or:
I did a google search and it say the reason for the error is because the statement does not specify which tableDate of Hire: Format([DOH],"mm/dd/yy") (I'm assuming) belongs to. I'm not well versed as to how to fix. If someone can provide a simple fix a newbie can understand that would be great.
Much appreciated. Please let me know if I did not provide enough detail
SELECT tbl_Employee.Department, tbl_Employee.EmployeeNumbe
FROM (tbl_Departments RIGHT JOIN (tbl_Employee LEFT JOIN tbl_PayrollInfo ON tbl_Employee.EmployeeNumbe
WHERE (((tbl_Employee.[Terminate
ORDER BY tbl_Employee.Department, tbl_Employee.LastName;
Obviously it has something to do with the DOH field and when I go into the query builder I see DOH listed twice that shows like the following (keep in mind two separate fields as I did not represent the vertical lines to seperate:
Field: Date of Hire: Format([DOH],"mm/dd/yy") DOH
Table: tbl_Employee
Sort:
Show: has a check mark has a check mark
Criteria:
Or:
I did a google search and it say the reason for the error is because the statement does not specify which tableDate of Hire: Format([DOH],"mm/dd/yy") (I'm assuming) belongs to. I'm not well versed as to how to fix. If someone can provide a simple fix a newbie can understand that would be great.
Much appreciated. Please let me know if I did not provide enough detail
If you try to makes it easy at start, you need to face complications later.
Instead, table can be as such: parents, parentID, parentName, ...), and children(childID, childName, childparentID, ...)
Another way is to create a query and assign aliases to field names:
Select parents.ID As parentID, children.ID As childparentID from parents Inner Join children On ....
Instead, table can be as such: parents, parentID, parentName, ...), and children(childID, childName, childparentID, ...)
Another way is to create a query and assign aliases to field names:
Select parents.ID As parentID, children.ID As childparentID from parents Inner Join children On ....
ASKER
hnasr: I am not a programmer so your comment does me little good unfortunately. As I stated I have no clue how to solve. Just need someone to tell me how to correct what I provided.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, as stated:
Field: Date of Hire: Format([DOH],"mm/dd/yy") DOH
Table: tbl_Employee
Sort:
Show: has a check mark has a check mark
Criteria:
Or:
Field: Date of Hire: Format([DOH],"mm/dd/yy") DOH
Table: tbl_Employee
Sort:
Show: has a check mark has a check mark
Criteria:
Or:
ASKER
Sorry in one table
May you upload a sample database depicting the issue?
If you specify [DOH] from which table is taken then the query will execute just fine...i assume it should be changed to
Format([tbl_Employee]![DOH], "mm/dd/yy") AS [Date of Hire]
ASKER
This worked
Format(tbl_Employee.DOH ,"mm/dd/yy")
Thank you very much
Format(tbl_Employee.DOH ,"mm/dd/yy")
Thank you very much
Welcome!
Then when reporting you need to tell access, or even a human, which specific field you refer to.
Name could refer to that in parents table, or that in children table.
To be specific add table name to the field name, such as [parents.name], and [children.name]
parentID is specific and appears only in children's table and can be references either with or without a table name.