?
Solved

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

Posted on 2017-05-17
10
Medium Priority
?
95 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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 18

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

650 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