Solved

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

Posted on 2017-05-17
10
51 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
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
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 17

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

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

Question has a verified solution.

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

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
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…

728 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