sglee
asked on
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
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
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.
Use the Left Join to join the two tables where one table may not have a matching record in the other table.
ASKER
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
Title Item_Count
10 New Styles (2011) 4
Shade (2011) 6
Summer (2011-1) 6
Wholesale (2011) 5
SELECT Project.Title, Nz(Count(ItemSetup.ItemNo)
FROM ItemSetup
LEFT JOIN Project ON ItemSetup.ProjectID = Project.ID
GROUP BY Project.Title;
FROM ItemSetup
LEFT JOIN Project ON ItemSetup.ProjectID = Project.ID
GROUP BY Project.Title;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Duh.....
I kept looking at this, trying to figure out why it wasn't working and totall missed that.
Dale
I kept looking at this, trying to figure out why it wasn't working and totall missed that.
Dale
ASKER
fyed:, GRayL:
I tried them, they did not work. But I appricate your effort.
peter57r:
It wworked and I thank you for the solution.
I tried them, they did not work. But I appricate your effort.
peter57r:
It wworked and I thank you for the solution.
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.
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.
ASKER
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?
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?
No, I just wanted to clarify the difference between the INNER JOIN and LEFT/RIGHT JOIN.
FROM ItemSetup
LEFT JOIN Project ON ItemSetup.ProjectID = Project.ID
GROUP BY Project.Title;