• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1708
  • Last Modified:

Return 0 if no records found in ACCESS Query

Hi,

  I have two tables (as seen in the attached) - Project (Parent table) and ItemSetup (Child table) in one-to-many relationship and like to display Each Project Name and the number of items in ItemSetup table, but the query only display project names that have records in ItemSetup table.

 How can I still display Project Names even if there is no record in the ItemSetup table.

Thanks

Group-By-Query.doc
0
sglee
Asked:
sglee
1 Solution
 
Dale FyeCommented:
SELECT Project.Title, Count(ItemSetup.ItemNo) AS Item_Count
FROM ItemSetup
LEFT JOIN Project ON ItemSetup.ProjectID = Project.ID
GROUP BY Project.Title;
0
 
Dale FyeCommented:
The use of INNER JOIN limits the result set to instance where there is a matching record in each table.  

Use the Left Join to join the two tables where one table may not have a matching record in the other table.
0
 
sgleeAuthor Commented:
When I plugged in your SQL in to the query, it stil does not pick up Project Titles with 0 records.

Title                   Item_Count
10 New Styles (2011)      4
Shade (2011)                            6
Summer (2011-1)      6
Wholesale (2011)      5
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
GRayLCommented:
SELECT Project.Title, Nz(Count(ItemSetup.ItemNo),0) AS Item_Count
FROM ItemSetup
LEFT JOIN Project ON ItemSetup.ProjectID = Project.ID
GROUP BY Project.Title;
0
 
peter57rCommented:
Previous responses have the join the wrong way round...

SELECT Project.Title, Count(ItemSetup.ItemNo) AS Item_Count
FROM Project LEFT JOIN  ItemSetup
  ON ItemSetup.ProjectID = Project.ID
GROUP BY Project.Title;
0
 
Dale FyeCommented:
Duh.....

I kept looking at this, trying to figure out why it wasn't working and totall missed that.

Dale
0
 
sgleeAuthor Commented:
fyed:, GRayL:
I tried them, they did not work. But I appricate your effort.
peter57r:
It wworked and I thank you for the solution.
0
 
Dale FyeCommented:
sqlee,

Do you understand the difference between what GRayL and I provided and what Peter provided?

There are INNER JOINs, LEFT JOINS and RIGHT JOINS.  When you are in the query design window, and you join two tables by dragging a field from one table to the other, you are creating an INNER JOIN, which as I mentioned above only returns records where there are matches in both tables.

If you Right click on the join line, and select the Join Properties you get the Join Properties dialog box.  In this box, you can define the fields that are used to execute the join, and have 3 radio buttons that allow you to select how the join works.  The 2nd option will generate a LEFT JOIN, the 3rd option will generate a Right JOIN.
0
 
sgleeAuthor Commented:
fyed:

You are right.
I can't believe that I did not even think about that fact that I could have chosen different join option in the Join Properties.
When I saw your query or anyone's query, I simply copied & pasted it into ACCESS query and determined that whether it worked or not.
Sorry about that. Do you want me to re-distribute the points?
0
 
Dale FyeCommented:
No, I just wanted to clarify the difference between the INNER JOIN and LEFT/RIGHT JOIN.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now