Compare Customer Sales in Previous and Current Period

[Guest]

I have two queries with customer sales data. qryPreviousPeriod and
qryCurrentPeriod. The fields include customercode, salespersoncode and
SumOfSales. In a third query I want to compare the sales data from the
previous period with the sales data from the current period. The third query
contains the fields customercode, salespersoncode, SumOfSales and difference
(currentperiodsales-previousperiodsales). In the third query I have linked
the two queries based on customercode. The query returns data for customers
that exist in both the qryPreviousPeriod and qryCurrentPeriod but it excludes
customers that don’t exist in both queries. If a customer exists in
qryPreviousPeriod but not in qryCurrentPeriod I still need its pervious
period data to display in the query and visa versa for a customer that exists
in qryCurrentPeriod. So I need to combine all customers that exits in both
queries and all customers that exist in one query or the other. How do I do
this?

Thanks,

Scott

 

[Nikos Yannacopoulos]

Scott,

I've had this challenge in the past, and here's how I did it (until
someone comes up with a better solution!):

Create a Union query to collect all customer codes from both queries, like:

SELECT customercode FROM qryPreviousPeriod

UNION SELECT customercode FROM qryCurrentPeriod

(to do this, start creating a new query in design view, add no table or
query to it, then revert to SQL view and paste the two lines of code above)
Save as qryCustomerBase

Now, in your third (now, fourth) query, add the newly created query
qryCustomerBase and the two you already had, and join each of the latter
to qryCustomerBase with outer joins, so you get all records from
qryCustomerBase and matching records from each of the other two.

HTH,
Nikos