[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1294
  • 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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