?
Solved

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

Posted on 2017-05-17
10
Medium Priority
?
68 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 30

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 30

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
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.

 
LVL 30

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 30

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 30

Expert Comment

by:hnasr
ID: 42140742
Welcome!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

764 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