Compare stats over years

  • Thread starter Thread starter Darryl Elwin
  • Start date Start date
D

Darryl Elwin

Hi
I have a database that records events A B C D E for each month of the year.

I want to be avle to produce a reprot that shows totals
for A B C D E for a mont in this year, and the same month last year and
year to date totals

Up to now it has been manually done in Excel


I get stuck trying to work out how I pull this month and the same month from
last year on the same report

ideas welcome
 
Could you please take the time to provide a few sample records with the
desired final display in your report?
 
Hi
Sample data
DATE_ISSUED PROJECT_ID
01/01/2003 A
01/01/2003 A
01/01/2003 B
01/01/2003 C
01/01/2003 C
01/01/2003 D

01/02/2003 A
01/02/2003 B
01/02/2003 C

01/01/2004 A
01/01/2004 B
01/01/2004 B
01/01/2004 A
01/01/2004 C
01/01/2004 D

01/02/2004 A
01/02/2004 A
01/02/2004 A
01/02/2004 B
01/02/2004 B
01/02/2004 C
01/02/2004 D

The desired results is if I ran this report in Feb 04 the reports would show

PROJECTS ISSUED REPORT FOR 01/04

PROJECT ISSUED 01/04 ISSUED 01/03 YTD 01/04 to 12/04
A 2 2
2
B 2 1
2
C 1 2
1
D 1 1
1

In March 04 the reports would show

PROJECTS ISSUED REPORT FOR 02/04

PROJECT ISSUED 02/04 ISSUED 02/03 YTD 01/04 to 12/04
A 3 1
5
B 2 1
4
C 1 1
2
D 1 0
2


Hope this helps

Darryl
 
You could use a totals query:
SELECT PROJECT_ID, Sum(Abs(Year(Date_Issued) = Year(Date())-1 AND
Month(Date_Issued) =Month(Date()) ) as LastYearThisMonth.
...other similar fields
FROM tblSampleData
GROUP BY PROJECT_ID;

You will need to add similar expressions to count for this year and last
year etc.
 
Hi.

Firstly, thanks for the reply and thanks for the help.
Great to know there are people like you out there prepared to help people
like me.

I got the query working :) however I can't for the life of me work out how
to add in my next required field "vale"
I'm sure once I get this one in I'll be able to get the others done.

This is what I have so far, I not sure how to address the value field

SELECT [Building Consents].[Project ID],
Sum(Abs(Year([issued])=Year(Date())-1 And Month([issued])=Month(Date())-1))
AS projectlastyearlastmonth, Sum(Abs(Year([issued])=Year(Date()) And
Month([issued])=Month(Date())-1)) AS projectthisyearlastmonth,
Sum(Abs([value])=Year(Date()) And Month([issued])=Month(Date())-1) AS
valuethisyearlastmonth
FROM [Building Consents]
GROUP BY [Building Consents].[Project ID];

Query2 Project ID projectlastyearlastmonth projectthisyearlastmonth
valuethisyearlastmonth

1 1 0
b 14 10 0
g 2 0 0
j 0 0 0
I 0 0 0
m 0 0 0
a 5 6 0
f 2 1 0
c 28 20 0
d 5 8 0
k 1 0 0
l 3 0 0
h 0 0 0
o 0 0 0
e 0 0 0
n 0 0 0


Thanks
 
I don't know what you want from the value. Do you want a sum or count or
average or what?

--
Duane Hookom
MS Access MVP


Darryl Elwin said:
Hi.

Firstly, thanks for the reply and thanks for the help.
Great to know there are people like you out there prepared to help people
like me.

I got the query working :) however I can't for the life of me work out how
to add in my next required field "vale"
I'm sure once I get this one in I'll be able to get the others done.

This is what I have so far, I not sure how to address the value field

SELECT [Building Consents].[Project ID],
Sum(Abs(Year([issued])=Year(Date())-1 And Month([issued])=Month(Date())-1))
AS projectlastyearlastmonth, Sum(Abs(Year([issued])=Year(Date()) And
Month([issued])=Month(Date())-1)) AS projectthisyearlastmonth,
Sum(Abs([value])=Year(Date()) And Month([issued])=Month(Date())-1) AS
valuethisyearlastmonth
FROM [Building Consents]
GROUP BY [Building Consents].[Project ID];

Query2 Project ID projectlastyearlastmonth projectthisyearlastmonth
valuethisyearlastmonth

1 1 0
b 14 10 0
g 2 0 0
j 0 0 0
I 0 0 0
m 0 0 0
a 5 6 0
f 2 1 0
c 28 20 0
d 5 8 0
k 1 0 0
l 3 0 0
h 0 0 0
o 0 0 0
e 0 0 0
n 0 0 0


Thanks


Duane Hookom said:
You could use a totals query:
SELECT PROJECT_ID, Sum(Abs(Year(Date_Issued) = Year(Date())-1 AND
Month(Date_Issued) =Month(Date()) ) as LastYearThisMonth.
...other similar fields
FROM tblSampleData
GROUP BY PROJECT_ID;

You will need to add similar expressions to count for this year and last
year etc.

--
Duane Hookom
MS Access MVP


would
show year
and
Click to expand...
Click to expand...
 
Hi,
Sorry,
I want to sum value
A line in the report would show

Project ID Issued_04/03 Issued_04/04 value_04/03
value_04/04
A 23 20
$1000 $1500
B 10 20
$800 $2000

Thanks


Duane Hookom said:
I don't know what you want from the value. Do you want a sum or count or
average or what?

--
Duane Hookom
MS Access MVP


Darryl Elwin said:
Hi.

Firstly, thanks for the reply and thanks for the help.
Great to know there are people like you out there prepared to help people
like me.

I got the query working :) however I can't for the life of me work out how
to add in my next required field "vale"
I'm sure once I get this one in I'll be able to get the others done.

This is what I have so far, I not sure how to address the value field

SELECT [Building Consents].[Project ID],
Sum(Abs(Year([issued])=Year(Date())-1 And Month([issued])=Month(Date())-1))
AS projectlastyearlastmonth, Sum(Abs(Year([issued])=Year(Date()) And
Month([issued])=Month(Date())-1)) AS projectthisyearlastmonth,
Sum(Abs([value])=Year(Date()) And Month([issued])=Month(Date())-1) AS
valuethisyearlastmonth
FROM [Building Consents]
GROUP BY [Building Consents].[Project ID];

Query2 Project ID projectlastyearlastmonth projectthisyearlastmonth
valuethisyearlastmonth

1 1 0
b 14 10 0
g 2 0 0
j 0 0 0
I 0 0 0
m 0 0 0
a 5 6 0
f 2 1 0
c 28 20 0
d 5 8 0
k 1 0 0
l 3 0 0
h 0 0 0
o 0 0 0
e 0 0 0
n 0 0 0


Thanks


Duane Hookom said:
You could use a totals query:
SELECT PROJECT_ID, Sum(Abs(Year(Date_Issued) = Year(Date())-1 AND
Month(Date_Issued) =Month(Date()) ) as LastYearThisMonth.
...other similar fields
FROM tblSampleData
GROUP BY PROJECT_ID;

You will need to add similar expressions to count for this year and last
year etc.

--
Duane Hookom
MS Access MVP


Hi
Sample data
DATE_ISSUED PROJECT_ID
01/01/2003 A
01/01/2003 A
01/01/2003 B
01/01/2003 C
01/01/2003 C
01/01/2003 D

01/02/2003 A
01/02/2003 B
01/02/2003 C

01/01/2004 A
01/01/2004 B
01/01/2004 B
01/01/2004 A
01/01/2004 C
01/01/2004 D

01/02/2004 A
01/02/2004 A
01/02/2004 A
01/02/2004 B
01/02/2004 B
01/02/2004 C
01/02/2004 D

The desired results is if I ran this report in Feb 04 the reports would
show

PROJECTS ISSUED REPORT FOR 01/04

PROJECT ISSUED 01/04 ISSUED 01/03 YTD 01/04 to 12/04
A 2 2
2
B 2 1
2
C 1 2
1
D 1 1
1

In March 04 the reports would show

PROJECTS ISSUED REPORT FOR 02/04

PROJECT ISSUED 02/04 ISSUED 02/03 YTD 01/04 to 12/04
A 3 1
5
B 2 1
4
C 1 1
2
D 1 0
2


Hope this helps

Darryl



Could you please take the time to provide a few sample records
Click to expand...
with
the
desired final display in your report?

--
Duane Hookom
MS Access MVP


Hi
I have a database that records events A B C D E for each month
Click to expand...
of
the
year.

I want to be avle to produce a reprot that shows totals
for A B C D E for a mont in this year, and the same month last year
and
year to date totals

Up to now it has been manually done in Excel


I get stuck trying to work out how I pull this month and the same
month
from
last year on the same report

ideas welcome
Click to expand...
Click to expand...
Click to expand...
 
Try something like:
SELECT [Building Consents].[Project ID],
Sum(Abs(Year([issued])=Year(Date())-1 And Month([issued])=Month(Date())-1))
AS projectlastyearlastmonth, Sum(Abs(Year([issued])=Year(Date()) And
Month([issued])=Month(Date())-1)) AS projectthisyearlastmonth,
Sum(Abs(Year([Issued])=Year(Date()) And Month([issued])=Month(Date())-1) *
[value]) AS
valuethisyearlastmonth
FROM [Building Consents]
GROUP BY [Building Consents].[Project ID];

The basic expression is:
Sum(Abs([Your expression]) * [ValueToSum])
--
Duane Hookom
MS Access MVP


Darryl Elwin said:
Hi,
Sorry,
I want to sum value
A line in the report would show

Project ID Issued_04/03 Issued_04/04 value_04/03
value_04/04
A 23 20
$1000 $1500
B 10 20
$800 $2000

Thanks


Duane Hookom said:
I don't know what you want from the value. Do you want a sum or count or
average or what?

--
Duane Hookom
MS Access MVP


Darryl Elwin said:
Hi.

Firstly, thanks for the reply and thanks for the help.
Great to know there are people like you out there prepared to help people
like me.

I got the query working :) however I can't for the life of me work out how
to add in my next required field "vale"
I'm sure once I get this one in I'll be able to get the others done.

This is what I have so far, I not sure how to address the value field

SELECT [Building Consents].[Project ID],
Sum(Abs(Year([issued])=Year(Date())-1 And Month([issued])=Month(Date())-1))
AS projectlastyearlastmonth, Sum(Abs(Year([issued])=Year(Date()) And
Month([issued])=Month(Date())-1)) AS projectthisyearlastmonth,
Sum(Abs([value])=Year(Date()) And Month([issued])=Month(Date())-1) AS
valuethisyearlastmonth
FROM [Building Consents]
GROUP BY [Building Consents].[Project ID];

Query2 Project ID projectlastyearlastmonth projectthisyearlastmonth
valuethisyearlastmonth

1 1 0
b 14 10 0
g 2 0 0
j 0 0 0
I 0 0 0
m 0 0 0
a 5 6 0
f 2 1 0
c 28 20 0
d 5 8 0
k 1 0 0
l 3 0 0
h 0 0 0
o 0 0 0
e 0 0 0
n 0 0 0


Thanks


You could use a totals query:
SELECT PROJECT_ID, Sum(Abs(Year(Date_Issued) = Year(Date())-1 AND
Month(Date_Issued) =Month(Date()) ) as LastYearThisMonth.
...other similar fields
FROM tblSampleData
GROUP BY PROJECT_ID;

You will need to add similar expressions to count for this year and last
year etc.

--
Duane Hookom
MS Access MVP


Hi
Sample data
DATE_ISSUED PROJECT_ID
01/01/2003 A
01/01/2003 A
01/01/2003 B
01/01/2003 C
01/01/2003 C
01/01/2003 D

01/02/2003 A
01/02/2003 B
01/02/2003 C

01/01/2004 A
01/01/2004 B
01/01/2004 B
01/01/2004 A
01/01/2004 C
01/01/2004 D

01/02/2004 A
01/02/2004 A
01/02/2004 A
01/02/2004 B
01/02/2004 B
01/02/2004 C
01/02/2004 D

The desired results is if I ran this report in Feb 04 the reports would
show

PROJECTS ISSUED REPORT FOR 01/04

PROJECT ISSUED 01/04 ISSUED 01/03 YTD 01/04 to 12/04
A 2 2
2
B 2 1
2
C 1 2
1
D 1 1
1

In March 04 the reports would show

PROJECTS ISSUED REPORT FOR 02/04

PROJECT ISSUED 02/04 ISSUED 02/03 YTD 01/04 to 12/04
A 3 1
5
B 2 1
4
C 1 1
2
D 1 0
2


Hope this helps

Darryl



Could you please take the time to provide a few sample records with
the
desired final display in your report?

--
Duane Hookom
MS Access MVP


Hi
I have a database that records events A B C D E for each month of
the
year.

I want to be avle to produce a reprot that shows totals
for A B C D E for a mont in this year, and the same month last year
and
year to date totals

Up to now it has been manually done in Excel


I get stuck trying to work out how I pull this month and the same
month
from
last year on the same report

ideas welcome
Click to expand...
Click to expand...
Click to expand...
 
THANKS for that.

I now have the report prompting for date to run a comparaison, and have all
my other feilds in.

Interesting the errors in the old manual reports I'm finding already :)

Do you have any recommended reading to learn this type of coding

Cheers once again

Darryl


Duane Hookom said:
Try something like:
SELECT [Building Consents].[Project ID],
Sum(Abs(Year([issued])=Year(Date())-1 And Month([issued])=Month(Date())-1))
AS projectlastyearlastmonth, Sum(Abs(Year([issued])=Year(Date()) And
Month([issued])=Month(Date())-1)) AS projectthisyearlastmonth,
Sum(Abs(Year([Issued])=Year(Date()) And Month([issued])=Month(Date())-1) *
[value]) AS
valuethisyearlastmonth
FROM [Building Consents]
GROUP BY [Building Consents].[Project ID];

The basic expression is:
Sum(Abs([Your expression]) * [ValueToSum])
--
Duane Hookom
MS Access MVP


Darryl Elwin said:
Hi,
Sorry,
I want to sum value
A line in the report would show

Project ID Issued_04/03 Issued_04/04 value_04/03
value_04/04
A 23 20
$1000 $1500
B 10 20
$800 $2000

Thanks


Duane Hookom said:
I don't know what you want from the value. Do you want a sum or count or
average or what?

--
Duane Hookom
MS Access MVP


Hi.

Firstly, thanks for the reply and thanks for the help.
Great to know there are people like you out there prepared to help people
like me.

I got the query working :) however I can't for the life of me work
Click to expand...
out
how
to add in my next required field "vale"
I'm sure once I get this one in I'll be able to get the others done.

This is what I have so far, I not sure how to address the value field

SELECT [Building Consents].[Project ID],
Sum(Abs(Year([issued])=Year(Date())-1 And
Month([issued])=Month(Date())-1))
AS projectlastyearlastmonth, Sum(Abs(Year([issued])=Year(Date()) And
Month([issued])=Month(Date())-1)) AS projectthisyearlastmonth,
Sum(Abs([value])=Year(Date()) And Month([issued])=Month(Date())-1) AS
valuethisyearlastmonth
FROM [Building Consents]
GROUP BY [Building Consents].[Project ID];

Query2 Project ID projectlastyearlastmonth projectthisyearlastmonth
valuethisyearlastmonth

1 1 0
b 14 10 0
g 2 0 0
j 0 0 0
I 0 0 0
m 0 0 0
a 5 6 0
f 2 1 0
c 28 20 0
d 5 8 0
k 1 0 0
l 3 0 0
h 0 0 0
o 0 0 0
e 0 0 0
n 0 0 0


Thanks


You could use a totals query:
SELECT PROJECT_ID, Sum(Abs(Year(Date_Issued) = Year(Date())-1 AND
Month(Date_Issued) =Month(Date()) ) as LastYearThisMonth.
...other similar fields
FROM tblSampleData
GROUP BY PROJECT_ID;

You will need to add similar expressions to count for this year
Click to expand...
and
last
year etc.

--
Duane Hookom
MS Access MVP


Hi
Sample data
DATE_ISSUED PROJECT_ID
01/01/2003 A
01/01/2003 A
01/01/2003 B
01/01/2003 C
01/01/2003 C
01/01/2003 D

01/02/2003 A
01/02/2003 B
01/02/2003 C

01/01/2004 A
01/01/2004 B
01/01/2004 B
01/01/2004 A
01/01/2004 C
01/01/2004 D

01/02/2004 A
01/02/2004 A
01/02/2004 A
01/02/2004 B
01/02/2004 B
01/02/2004 C
01/02/2004 D

The desired results is if I ran this report in Feb 04 the reports
would
show

PROJECTS ISSUED REPORT FOR 01/04

PROJECT ISSUED 01/04 ISSUED 01/03 YTD 01/04 to 12/04
A 2 2
2
B 2 1
2
C 1 2
1
D 1 1
1

In March 04 the reports would show

PROJECTS ISSUED REPORT FOR 02/04

PROJECT ISSUED 02/04 ISSUED 02/03 YTD 01/04 to 12/04
A 3 1
5
B 2 1
4
C 1 1
2
D 1 0
2


Hope this helps

Darryl



Could you please take the time to provide a few sample records with
the
desired final display in your report?

--
Duane Hookom
MS Access MVP


Hi
I have a database that records events A B C D E for each
Click to expand...
month
of
the
year.

I want to be avle to produce a reprot that shows totals
for A B C D E for a mont in this year, and the same month last
year
and
year to date totals

Up to now it has been manually done in Excel


I get stuck trying to work out how I pull this month and the same
month
from
last year on the same report

ideas welcome
Click to expand...
Click to expand...
Click to expand...
 
I am not aware of books that suggest expressions like this. It is a matter
of understanding lots of different functions and features and figuring out
how to combine them.

--
Duane Hookom
MS Access MVP


Darryl Elwin said:
THANKS for that.

I now have the report prompting for date to run a comparaison, and have all
my other feilds in.

Interesting the errors in the old manual reports I'm finding already :)

Do you have any recommended reading to learn this type of coding

Cheers once again

Darryl


Duane Hookom said:
Try something like:
SELECT [Building Consents].[Project ID],
Sum(Abs(Year([issued])=Year(Date())-1 And Month([issued])=Month(Date())-1))
AS projectlastyearlastmonth, Sum(Abs(Year([issued])=Year(Date()) And
Month([issued])=Month(Date())-1)) AS projectthisyearlastmonth,
Sum(Abs(Year([Issued])=Year(Date()) And Month([issued])=Month(Date())-1) *
[value]) AS
valuethisyearlastmonth
FROM [Building Consents]
GROUP BY [Building Consents].[Project ID];

The basic expression is:
Sum(Abs([Your expression]) * [ValueToSum])
--
Duane Hookom
MS Access MVP


Darryl Elwin said:
Hi,
Sorry,
I want to sum value
A line in the report would show

Project ID Issued_04/03 Issued_04/04 value_04/03
value_04/04
A 23 20
$1000 $1500
B 10 20
$800 $2000

Thanks


I don't know what you want from the value. Do you want a sum or
Click to expand...
Click to expand...
count
or
average or what?

--
Duane Hookom
MS Access MVP


Hi.

Firstly, thanks for the reply and thanks for the help.
Great to know there are people like you out there prepared to help
people
like me.

I got the query working :) however I can't for the life of me work out
how
to add in my next required field "vale"
I'm sure once I get this one in I'll be able to get the others done.

This is what I have so far, I not sure how to address the value field

SELECT [Building Consents].[Project ID],
Sum(Abs(Year([issued])=Year(Date())-1 And
Month([issued])=Month(Date())-1))
AS projectlastyearlastmonth, Sum(Abs(Year([issued])=Year(Date()) And
Month([issued])=Month(Date())-1)) AS projectthisyearlastmonth,
Sum(Abs([value])=Year(Date()) And Month([issued])=Month(Date())-1) AS
valuethisyearlastmonth
FROM [Building Consents]
GROUP BY [Building Consents].[Project ID];

Query2 Project ID projectlastyearlastmonth projectthisyearlastmonth
valuethisyearlastmonth

1 1 0
b 14 10 0
g 2 0 0
j 0 0 0
I 0 0 0
m 0 0 0
a 5 6 0
f 2 1 0
c 28 20 0
d 5 8 0
k 1 0 0
l 3 0 0
h 0 0 0
o 0 0 0
e 0 0 0
n 0 0 0


Thanks


You could use a totals query:
SELECT PROJECT_ID, Sum(Abs(Year(Date_Issued) = Year(Date())-1 AND
Month(Date_Issued) =Month(Date()) ) as LastYearThisMonth.
...other similar fields
FROM tblSampleData
GROUP BY PROJECT_ID;

You will need to add similar expressions to count for this year and
last
year etc.

--
Duane Hookom
MS Access MVP


Hi
Sample data
DATE_ISSUED PROJECT_ID
01/01/2003 A
01/01/2003 A
01/01/2003 B
01/01/2003 C
01/01/2003 C
01/01/2003 D

01/02/2003 A
01/02/2003 B
01/02/2003 C

01/01/2004 A
01/01/2004 B
01/01/2004 B
01/01/2004 A
01/01/2004 C
01/01/2004 D

01/02/2004 A
01/02/2004 A
01/02/2004 A
01/02/2004 B
01/02/2004 B
01/02/2004 C
01/02/2004 D

The desired results is if I ran this report in Feb 04 the reports
would
show

PROJECTS ISSUED REPORT FOR 01/04

PROJECT ISSUED 01/04 ISSUED 01/03 YTD 01/04 to 12/04
A 2 2
2
B 2 1
2
C 1 2
1
D 1 1
1

In March 04 the reports would show

PROJECTS ISSUED REPORT FOR 02/04

PROJECT ISSUED 02/04 ISSUED 02/03 YTD 01/04 to 12/04
A 3 1
5
B 2 1
4
C 1 1
2
D 1 0
2


Hope this helps

Darryl



Could you please take the time to provide a few sample records
with
the
desired final display in your report?

--
Duane Hookom
MS Access MVP


Hi
I have a database that records events A B C D E for each month
of
the
year.

I want to be avle to produce a reprot that shows totals
for A B C D E for a mont in this year, and the same month last
year
and
year to date totals

Up to now it has been manually done in Excel


I get stuck trying to work out how I pull this month and the
same
month
from
last year on the same report

ideas welcome
Click to expand...
Click to expand...
Click to expand...
 
Back
Top