Problem seeing data with multiple queries (1 Viewer)

jjake

Registered User.
Local time
Yesterday, 19:16
Joined
Oct 8, 2015
Messages
291
Hello,

I have a PO form (frmOrder) from tblOrder with a field (ProjectID) from (tblProjectCoding).

I have a query (qryProjectDetailForm) that pulls several fields from (tblProjectCoding) to show all my projects.

I then have a secondary query (qryProjectPurchases) which shows all purchases made under each project # with a totals column.

My problem is my query will only show projects that have had purchases made under them. How do i also show the projects that haven't had purchases made under them so they reflect $0 in the totals column.

i'm using these queries much deeper after this to show other data with other queries but this is the root cause of my problem.


Code:
SELECT QryProjectDetailForm.ProjectID, QryProjectDetailForm.ProjectName, Sum(qryOrderSubTotal.SumOfUnitTotal) AS ProjectSubTotal
FROM (QryProjectDetailForm INNER JOIN tblOrder ON QryProjectDetailForm.[ProjectID] = tblOrder.[ProjectCoding]) INNER JOIN qryOrderSubTotal ON tblOrder.[OrderID] = qryOrderSubTotal.[OrderID]
GROUP BY QryProjectDetailForm.ProjectID, QryProjectDetailForm.ProjectName;
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:16
Joined
Oct 29, 2018
Messages
21,358
Hi. Instead of using an INNER JOIN, try using an OUTER JOIN (LEFT or RIGHT). Check out this article (if it helps): Query Join Basics
 

June7

AWF VIP
Local time
Yesterday, 16:16
Joined
Mar 9, 2014
Messages
5,423
Don't use INNER JOIN, use LEFT or RIGHT (I forget which for this case) as appropriate.
 

Users who are viewing this thread

Top Bottom