Re: How to compare last year's to today's numbers from one table?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Olavious (richter_at_nospam.de)
Date: 12/02/04 

Date: Thu, 02 Dec 2004 16:29:43 GMT

Dear Tom,

I think this is as close as it can get. You are absolutely right about my sample not to include prev and present period. As well as concolidating periods (e.g. customer A all sales in Month Jan or all sales in 2004) to facilitate reading

I think I learned more than I was asking for :-) Never thought of the nested querying or adding a table twice to a query. Just used the wizard so far.

Now I tried your sample code immediately on a trial table and got some promising results, though not yet what I look for. I am glad I don't have to split tables again!

To stay with my sample 2003 and 2004 period:

1- all customers who bought in either 2003 or 2004
2- all customers who bought in both years
3- omit all customers who haven't purchased in both years.

1=I see new customers and those who didn't do anything this year
2=I can compare performance
3=I don't want to distract view by customers with no turnover

Now its time for my homework. Will try to setup a proper query on the weekend and am confident to be able to tell you beginning of next week how I solved my problem (hope I am not to enthusiastic ;-)

Thank you for showing me the right direction so far!

Olaf

>>> Tom Ellison<tellison@jcdoyle.com> 01.12.04 >>>

Dear Olaf:

I'm glad if I stimulated your thinking a bit. It's usually best to do
the thinking before doing the programming. Things usually come out
better, or at least quicker.

Now, let me say a bit about splitting the table. If you were
interested in results for 2004 compared with 2003, you could write two
queries that filter the table, one for all 2004 rows, and the other
for all 2003 rows. You could then form a JOIN between these two
queries to see them side by side.

This is known as "partitioning the data" in that it produces
non-intersecting subsets of the data. Actually, I will not suggest
that you write separate queries for each year to partition it.
Rather, this can be done in a SELECT query by referencing the table
twice in the same query. Here's an example:

SELECT *
  FROM YourTable T1
    INNER JOIN YourTable T2 ON T2.MONTH([Date]) = T1.MONTH([Date])
      AND T2.DAY([Date]) = T1.DAY([Date])
  WHERE YEAR(T1.[Date]) = 2004 and YEAR(T2.[Date]) = 2003

To understand this, you need to know that T1 and T2 are called
"aliases" for the table, creating a situation where the table is
considered twice in the same query, that is, there are two "instances"
of the table with which you are working simultaneously. I believe
this technique will solve one of your problems.

Based on your sample results, I don't think you need to JOIN on every
date within the year. The sample seems to indicate you only want to
have a total sales for each year. JOINing on date would be an
unnecessary complexity for this.

A comment about those sampe results. I would not show the columns
Period and PrevPeriod you have. As I understand you, these would be
invariant. I'd probably just put them at the top of the page. If the
report were to cover 2004 compared to 2003, then compare 2003 to 2002,
I'd go do a new page when it changes and change the heading on the
page. What you'd have remaining is Customer / Sales / Prev Sales.

There is the possibility of having a Customer with no sales in either
year. In order to not omit that Customer from the report, I'd base
the whole report on the Customer table and probably use a subquery to
sum his sales in each year, substituting a 0 for NULL when there are
no sales records. Here's a rough idea of the query:

SELECT C.Customer,
    Nz((SELECT SUM(Amount) From SalesTable ST
      WHERE ST.Customer = C.Customer AND Year(ST.[Date]) = 2004), 0)
      AS Sales,
    Nz((SELECT SUM(Amount) From SalesTable ST
      WHERE ST.Customer = C.Customer AND Year(ST.[Date]) = 2003), 0)
      AS PrevSales
  FROM CustomerTable C

Given what you said you wanted in the sample results, maybe this is
close. You'll need to put in the actual names of your tables.

Is this getting close? Are you learning anything? Or am I perhaps
not getting what it is you want?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Relevant Pages