Solved

Incomplete Query

Posted on 2015-02-06
15
66 Views
Last Modified: 2015-02-12
I have a query that is not returning all of the records and I have not selected any filtering criteria. It appears to be omitting records where one field has no (null) data. I have not been able to figure out why. Can anybody help. I am a novice user.
0
Comment
Question by:mjmangano
  • 8
  • 3
  • 2
  • +1
15 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
try this query as a test

select * from tablename

change "tableName" with the actual name of your table

see if you get all the records
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
If the query is using an inner join, then when records from tblA don't have a match in tblB the record will not appear in the resultset.  If you want all records from tblA even when they have no match in tblB, then you need to use a Left Join.
0
 

Author Comment

by:mjmangano
Comment Utility
You have to be more specific. I built a query from four different tables. I'm not sure what you are suggesting.
0
 

Author Comment

by:mjmangano
Comment Utility
Pat - Can you please explain an inner join and a left join?
0
 

Author Comment

by:mjmangano
Comment Utility
The field that I believe is causing the problem is EmpID, I have many to one relationship between the Primary table (Tasks)  and the secondary table (Employees).
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Can you post the SQL of your query?  Change to SQL view, copy the text and paste it here.
0
 

Author Comment

by:mjmangano
Comment Utility
Ok, here it is:

SELECT tblProject_List.ProjName, tblDiv35_Roles_Skills.Role, tblDiv35_Tasks.Task, tblDiv35_Tasks.[WF Level], tblDiv35_Tasks.[Planned Start], tblDiv35_Tasks.[Planned Completion], tblDiv35_Personnel_List_New.Full_Name, tblDiv35_Tasks.[Projected Start], tblDiv35_Tasks.[Projected Completion], tblDiv35_Tasks.[Task Complete?], tblDiv35_Tasks.DateModified, tblDiv35_Tasks.TaskID, tblDiv35_Personnel_List_New.EmpID, tblDiv35_Roles_Skills.RoleID, tblProject_List.ProjID
FROM tblProject_List RIGHT JOIN (tblDiv35_Roles_Skills INNER JOIN (tblDiv35_Personnel_List_New INNER JOIN tblDiv35_Tasks ON tblDiv35_Personnel_List_New.EmpID = tblDiv35_Tasks.EmpID) ON tblDiv35_Roles_Skills.RoleID = tblDiv35_Tasks.Role) ON tblProject_List.ProjID = tblDiv35_Tasks.ProjID;
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
Look at the tables with the inner Joins.   You can identify the join type visually by looking at the ends of the join lines.

tblA --- tblB    is an Inner Join so there must be matching records in each table
tblA --> tblB   is a Left Join so all rows in tblA are returned along with any matching rows from tblB
tblA <-- tblB   is a Right Join so all rows in tblB are returned along with any matching rows from tblA

When you build the query, all tables ti the right of a left join must also be left joins and conversely, all tables to the left of a right join must also be right joins.

Since your query already has Right Joins, you may need to change the inner joins to right joins.

Left and right are essentially the same thing, just from a different perspective.
0
 

Author Comment

by:mjmangano
Comment Utility
OK! I changed the join characteristic and it now returns all of the records. I see where I went wrong. That's pretty tricky. Thanks for the help!

Are you willing to help with some other issues that I'm having?
0
 

Author Comment

by:mjmangano
Comment Utility
I have been using queries to generate a resultset to use in subforms. I am entering data into tables via forms/combo boxes. The table will show the primary key of the specific field that I am filling out. For example, my Task table will show the EmpID Auto Generated number in the Name Field. Therefore I am combining the Task table and the Employee Table in a query to use the Name Field in my subform. This seems a little cumbersome but perhaps it is standard practice. As a novice, am I missing a switch in the Property Sheet so that I can avoid this manipulation?
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Normally, you would assign points to the expert who gave you the solution that worked.  Or if several experts helped with parts of the puzzle, then distribute the points accordingly.  Then start a new thread for a new question.

Usually, when working with a form, you would use a combo box to pick the Employee.  The combo will be bound to the EmpID in your task table and will reference tblEmployee or a query of the employee table as the RowSource and that will show the name.  You can use the wizard to build the combo if you don't know how to set the properties yourself.  If this form doesn't pick the employee, then the best solution is to use a query that joins the form's primary table to the employee table so you can "look up" the employee first and last names.  Once they are included in the query, you can bind them to controls on the form.  HOWEVER, when you do this, best practice is to set the Locked property to true for these "look up" fields to prevent the user from accidentally updating them.  You want the user to go to the employee maintenance form to change an employee name, you don't want him to accidentally do it on the task form because he thinks he is reassigning the task to a different person.
0
 

Author Comment

by:mjmangano
Comment Utility
Thank you very much Pat. I am not familiar with the etiquette but I will give it a try. Thanks for the help on both questions! I'll start a new thread for any additional issues. Much appreciated.
0
 

Author Comment

by:mjmangano
Comment Utility
Thanks everyone!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Thanks for the points, but I didn't contribute to the solution on this one.  If you would like to reallocate points, just click the REquest Attention hyperlink at the bottom right corner of the original post and request that the question be reopened to allow you to reallocate points.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now