Solved

Incomplete Query

Posted on 2015-02-06
15
74 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
[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
  • 8
  • 3
  • 2
  • +1
15 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40594060
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 37

Expert Comment

by:PatHartman
ID: 40594083
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
ID: 40594084
You have to be more specific. I built a query from four different tables. I'm not sure what you are suggesting.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

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

Author Comment

by:mjmangano
ID: 40594102
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 48

Expert Comment

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

Author Comment

by:mjmangano
ID: 40594119
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
 
LVL 37

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40594153
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
ID: 40594183
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
ID: 40594209
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 37

Expert Comment

by:PatHartman
ID: 40594516
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
ID: 40594583
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
ID: 40594589
Thanks everyone!
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 40595077
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

752 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