?
Solved

MS Access Error - specified field could refer to more than one table listed

Posted on 2017-05-17
10
Medium Priority
?
178 Views
Last Modified: 2017-05-18
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.EmployeeNumber, 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.DepartmentName, tbl_Employee.[401K Compliance Codes], tbl_PayrollInfo.[Claim Federal], tbl_PayrollInfo.[Additional WH Federal], tbl_PayrollInfo.[Additional 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.EmployeeNumber = tbl_PayrollInfo.EmployeeNumber) ON tbl_Departments.Department = tbl_Employee.Department) LEFT JOIN [Qry_PTO Balance] ON tbl_PayrollInfo.EmployeeNumber = [Qry_PTO Balance].EmployeeID
WHERE (((tbl_Employee.[Terminated 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
0
Comment
Question by:DJ P
  • 5
  • 4
10 Comments
 
LVL 31

Expert Comment

by:hnasr
ID: 42139541
If you have two tables, parents(id, name, ...) and children(id, name, parentID, ...)
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.
0
 
LVL 31

Expert Comment

by:hnasr
ID: 42139548
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 ....
0
 

Author Comment

by:DJ P
ID: 42139555
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.
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 31

Accepted Solution

by:
hnasr earned 2000 total points
ID: 42139563
Do you have DOH in two tables?
Format([DOH],"mm/dd/yy")

Try:
Format(tbl_Employee.DOH ,"mm/dd/yy")

you may mean another DOH if exists in another table.
0
 

Author Comment

by:DJ P
ID: 42139573
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:
0
 

Author Comment

by:DJ P
ID: 42139575
Sorry in one table
0
 
LVL 31

Expert Comment

by:hnasr
ID: 42139583
May you upload a sample database depicting the issue?
0
 
LVL 19

Expert Comment

by:John Tsioumpris
ID: 42139839
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]

Open in new window

1
 

Author Closing Comment

by:DJ P
ID: 42140311
This worked

Format(tbl_Employee.DOH ,"mm/dd/yy")

Thank you very much
0
 
LVL 31

Expert Comment

by:hnasr
ID: 42140742
Welcome!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question