Query to show all values from all tables

[nchinnici]

I am trying to create a query to show the difference between sales projections vs. actual orders for the year. I've created a query that basically does that as shown in the screenshot.



The problem is that we have instances where there was a projection for a customer but no order AND times when we get an order that was not originally projected. Neither of these values show up in the query as a record doesn't exist yet for them in either the projections or orders table. Is there any way around this, or any other way to create this type of query/report?

[June7]

Probably need to use LEFT or RIGHT join instead of INNER.

However, including multiple many relationship tables will likely produce unsatisfactory result, especially if doing aggregate calcs. Might need to use report/subreport arrangement.

Don't do grouping in this query, do it in report. Or might need to build GROUP BY queries for Projections and Projects and join those queries to Customers.

[nchinnici]

Thanks for your feedback, I'll give your suggestions a try!

[Micron]

I think the underlying flaw is that orders usually relate to customers. By relating to some other entity you have set up a situation that presupposes you can't have an order without a project. Adding custID to orders and moving the relationship to customer table may be a better long term fix.