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

O

Olaf Richter

I am trying to compare, for example, this quarter's sales to a particular customer with last year's sales to him during same period.

Data for last 4 years is in one table

Fields: [Date],[Customer],[Sale],[Amount]

e.g. All Sales to Customer "A" in second quarter 04 compared to all sales to him in second quarter 03.
I would like to see even those sales in last year which don' have sales in this year's period.

Pls give me an idea how to tackle such a problem in a query. Should I rather split table?

I am using Access 2002

Thank you for your help

Olaf
 
T

Tom Ellison

Dear Olaf:

Splitting the table would be a bad idea.

Everything you could do by splitting the table could be done without
splitting the table. But, conceptually, think about how you would
handle this if the table were split.

Now, if you had a table of dates from 2003 and another with dates from
2004, you would likely want to JOIN the two on the Date field, where
the date from 2003 is the same month/day as the date from 2004. But
would an INNER JOIN work? That is, would you ever have a date in 2004
where there is no associated date in 2003? Or would there ever be a
date in 2003 not in 2004? And, remember, there was a February 29 in
2004!

Likely, the dates won't all match up. So, then, what do you want to
do when they do not? Omit that date? Show it when it exists in 2004
but not in 2003, but not show it if it exists in 2003 but not in 2004?
Or show it when it exists in either year? This determines just what
kind of JOIN you would use.

Please read carefully and try to respond to each question so we can
continue.

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

Olavious

Dear Tom,

thank you for the prompt answer and pointing out that I had only seen the tip of the iceberg ;-)
Never thought of the 29th February or orphant records in the other table (if they were split).

The company scenario:
- Trader wants to see his sales and corresponding sales from previous period to compare.
As well he wants to see all new customers/sales (without previous deals)
Usually he has no interest in customers who didn't buy this period
- Management in contrary wants to see as well those customers to ask trader why they didn't buy.

So the answer must be: we would need all records/sales from 2003 and 2004 where a particular customer had purchased goods.

If I had two tables I would query each for customer, period/previous period and sum of sales. Then I would link both in third query to include all records from both tables. The link would be something like customer and period, since date/period alone won't work. So far my theory. This is where I get stuck. Result should look like

Customer, Period, SumSales1, PrevPeriod, SumSales2
A 04 120 03 100
B 04 0 03 50
C 04 75 03 0

I never get there, since I can't get link to process e.g. Customer A record 04 (120) to find customer A record 03 (100)
To make things worse I have all details in only one table.

What am I missing?

Olaf

Dear Olaf:

Splitting the table would be a bad idea.

Everything you could do by splitting the table could be done without
splitting the table. But, conceptually, think about how you would
handle this if the table were split.

Now, if you had a table of dates from 2003 and another with dates from
2004, you would likely want to JOIN the two on the Date field, where
the date from 2003 is the same month/day as the date from 2004. But
would an INNER JOIN work? That is, would you ever have a date in 2004
where there is no associated date in 2003? Or would there ever be a
date in 2003 not in 2004? And, remember, there was a February 29 in
2004!

Likely, the dates won't all match up. So, then, what do you want to
do when they do not? Omit that date? Show it when it exists in 2004
but not in 2003, but not show it if it exists in 2003 but not in 2004?
Or show it when it exists in either year? This determines just what
kind of JOIN you would use.

Please read carefully and try to respond to each question so we can
continue.

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


I am trying to compare, for example, this quarter's sales to a particular customer with last year's sales to him during same period.

Data for last 4 years is in one table

Fields: [Date],[Customer],[Sale],[Amount]

e.g. All Sales to Customer "A" in second quarter 04 compared to all sales to him in second quarter 03.
I would like to see even those sales in last year which don' have sales in this year's period.

Pls give me an idea how to tackle such a problem in a query. Should I rather split table?

I am using Access 2002

Thank you for your help

Olaf
Click to expand...
 
T

Tom Ellison

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


Dear Tom,

thank you for the prompt answer and pointing out that I had only seen the tip of the iceberg ;-)
Never thought of the 29th February or orphant records in the other table (if they were split).

The company scenario:
- Trader wants to see his sales and corresponding sales from previous period to compare.
As well he wants to see all new customers/sales (without previous deals)
Usually he has no interest in customers who didn't buy this period
- Management in contrary wants to see as well those customers to ask trader why they didn't buy.

So the answer must be: we would need all records/sales from 2003 and 2004 where a particular customer had purchased goods.

If I had two tables I would query each for customer, period/previous period and sum of sales. Then I would link both in third query to include all records from both tables. The link would be something like customer and period, since date/period alone won't work. So far my theory. This is where I get stuck. Result should look like

Customer, Period, SumSales1, PrevPeriod, SumSales2
A 04 120 03 100
B 04 0 03 50
C 04 75 03 0

I never get there, since I can't get link to process e.g. Customer A record 04 (120) to find customer A record 03 (100)
To make things worse I have all details in only one table.

What am I missing?

Olaf

Dear Olaf:

Splitting the table would be a bad idea.

Everything you could do by splitting the table could be done without
splitting the table. But, conceptually, think about how you would
handle this if the table were split.

Now, if you had a table of dates from 2003 and another with dates from
2004, you would likely want to JOIN the two on the Date field, where
the date from 2003 is the same month/day as the date from 2004. But
would an INNER JOIN work? That is, would you ever have a date in 2004
where there is no associated date in 2003? Or would there ever be a
date in 2003 not in 2004? And, remember, there was a February 29 in
2004!

Likely, the dates won't all match up. So, then, what do you want to
do when they do not? Omit that date? Show it when it exists in 2004
but not in 2003, but not show it if it exists in 2003 but not in 2004?
Or show it when it exists in either year? This determines just what
kind of JOIN you would use.

Please read carefully and try to respond to each question so we can
continue.

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


I am trying to compare, for example, this quarter's sales to a particular customer with last year's sales to him during same period.

Data for last 4 years is in one table

Fields: [Date],[Customer],[Sale],[Amount]

e.g. All Sales to Customer "A" in second quarter 04 compared to all sales to him in second quarter 03.
I would like to see even those sales in last year which don' have sales in this year's period.

Pls give me an idea how to tackle such a problem in a query. Should I rather split table?

I am using Access 2002

Thank you for your help

Olaf
Click to expand...
Click to expand...
 
O

Olavious

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


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
 
O

Olavious

Dear Tom,

rather late, but very satisfying! Thank you very much for your tips, took a while to get it working but here (maybe others find it helpful) a copy of the query I managed to patch together:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT tblCustomers.CustomerCounter, tblCustomers.[Cust IDHH], tblCustomers..[Cust Name],
NZ((Select Sum([MDetBagsQuantity]) From tblMainDetails
Where tblCustomers.CustomerCounter=tblMainDetails.MDetBuyer and tblMainDetails.MDetSaleMonth = [End Date]-Day([End Date])+1;),0) AS [Month Sales],
NZ((Select Sum([MDetBagsQuantity]) From tblMainDetails
Where tblCustomers.CustomerCounter=tblMainDetails.MDetBuyer and tblMainDetails.MDetSaleMonth Between [Start Date] And [End Date];),0) AS Sales,
NZ((Select Sum([MDetBagsQuantity]) From tblMainDetails
Where tblCustomers.CustomerCounter=tblMainDetails.MDetBuyer and tblMainDetails.MDetSaleMonth Between dateadd("yyyy",-1,[Start Date]) And dateadd("yyyy",-1,[End Date]);),0) AS [Prev Sales]
FROM tblCustomers
ORDER BY tblCustomers.CustomerCounter;


I get customers who bought in both years(periods) or in either year(period)!

Thanks again

Olaf

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


Dear Tom,

thank you for the prompt answer and pointing out that I had only seen the tip of the iceberg ;-)
Never thought of the 29th February or orphant records in the other table (if they were split).

The company scenario:
- Trader wants to see his sales and corresponding sales from previous period to compare.
As well he wants to see all new customers/sales (without previous deals)
Usually he has no interest in customers who didn't buy this period
- Management in contrary wants to see as well those customers to ask trader why they didn't buy.

So the answer must be: we would need all records/sales from 2003 and 2004 where a particular customer had purchased goods.

If I had two tables I would query each for customer, period/previous period and sum of sales. Then I would link both in third query to include all records from both tables. The link would be something like customer and period, since date/period alone won't work. So far my theory. This is where I get stuck. Result should look like

Customer, Period, SumSales1, PrevPeriod, SumSales2
A 04 120 03 100
B 04 0 03 50
C 04 75 03 0

I never get there, since I can't get link to process e.g. Customer A record 04 (120) to find customer A record 03 (100)
To make things worse I have all details in only one table.

What am I missing?

Olaf

Dear Olaf:

Splitting the table would be a bad idea.

Everything you could do by splitting the table could be done without
splitting the table. But, conceptually, think about how you would
handle this if the table were split.

Now, if you had a table of dates from 2003 and another with dates from
2004, you would likely want to JOIN the two on the Date field, where
the date from 2003 is the same month/day as the date from 2004. But
would an INNER JOIN work? That is, would you ever have a date in 2004
where there is no associated date in 2003? Or would there ever be a
date in 2003 not in 2004? And, remember, there was a February 29 in
2004!

Likely, the dates won't all match up. So, then, what do you want to
do when they do not? Omit that date? Show it when it exists in 2004
but not in 2003, but not show it if it exists in 2003 but not in 2004?
Or show it when it exists in either year? This determines just what
kind of JOIN you would use.

Please read carefully and try to respond to each question so we can
continue.

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


I am trying to compare, for example, this quarter's sales to a particularcustomer with last year's sales to him during same period.

Data for last 4 years is in one table

Fields: [Date],[Customer],[Sale],[Amount]

e.g. All Sales to Customer "A" in second quarter 04 compared to all sales to him in second quarter 03.
I would like to see even those sales in last year which don' have sales in this year's period.

Pls give me an idea how to tackle such a problem in a query. Should I rather split table?

I am using Access 2002

Thank you for your help

Olaf
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Appending records that use "last year's" records... 6
compare this year to last year 2
Query from unrelated tables help please 4
How do I query a recordset to return only the last records? 4
DB tracks yearly records, this year's values depends on last year' 2
Sort by Total 7
One to many Relationship in one record 6
Sumif and IF ??? 1

Top