Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Incomplete Query

Posted on 2015-02-06
15
Medium Priority
?
76 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 39

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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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

Accepted Solution

by:
PatHartman earned 2000 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 39

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

664 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