How can I view all the records on a query if one of the fields doesn't have a record...

[interclone]

I'm a newbie to Access, so maybe this is a simple thing to do but I'm struggling with it right now. Basically I've got queries looking at sales for two separate years. The main categories are customer and units. I want to compare the two results in one query. But when I do this it only displays the customers who have activity in both years. So if a customer only ordered in year one they are not displayed. So what I want to do is display all customers regardless of orders.



Any suggestions for help? Thanks in advance!

[Dal Jeanis]

You're looking for an outer join. There are roughly three ways to do this, but I'm going to give you a simple one that will work for you.

1) create a query (let's call it qry_AllCust) that gives you just your customer keys/id numbers for anyone who is in the two years under question.
2) create a query (let's call it qry_sumCustYear) that gives you the year, customer key and total units for each year
3) LEFT JOIN the results of the first query to the results of the second query from your first year, then LEFT JOIN those results to the results of the second query from your second year.
4) Use the NZ function to change the missing results to zeroes for comparison.

SELECT Q1.CustID, NZ(Q2.units) AS Y1Units, NZ(Q3.Units) AS Y2Units
FROM 
    (qry_AllCust AS Q1
     LEFT JOIN qry_sumCustYear AS Q2
     ON Q1.CustID = Q2.CustID) 
         LEFT JOIN qry_sumCustYear AS Q3
          ON Q1.CustID = Q3.CustID)
    WHERE  (Q2.CustYear = 2012 
       AND Q3.CustYear = 2014)

That's a pretty basic example of what you're looking for. Don't take the syntax as gospel --it's totally untested aircode --but something like that approach will work.

[June7]

Units of what?

You have two queries that summarize the sales for two years grouped by customer?

Need a dataset of all possible customers. Do you already have such a table?

Join the two summary queries to the customers dataset.

[interclone]

Thanks guys! I think that pretty much gets me there.

I'm adding a layer too for analysis. We sell basically 2 classes of products. I'm wanting to analyze the two classes together for a big picture and then the two classes separate. Also by salesman. I'll be back if I have any more troubles. Thanks again for the help.