Solved

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

Posted on 2017-05-17
10
31 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
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
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
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 30

Accepted Solution

by:
hnasr earned 500 total points
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
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
Sorry in one table
0
 
LVL 30

Expert Comment

by:hnasr
May you upload a sample database depicting the issue?
0
 
LVL 17

Expert Comment

by:John Tsioumpris
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
This worked

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

Thank you very much
0
 
LVL 30

Expert Comment

by:hnasr
Welcome!
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Join & Write a Comment

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

739 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