Report for 2 different time periods

[Dave van Wagenen]

I am new enough to ACCESS that I am not sure that this is the correct News Group for this post. If I'm in the wrong place I apologize.

I have been asked to create a report which contains the following 4 columns and rows.

reporting month, current ytd, same month last year, last year ytd for the same period
Total1 nnn nnn nnn nnn
detail a aaa aaa aaa aaa
detail b bbb bbb bbb bbb


The report will be run during the 1st week of the month so reporting month is actually (current month - 1)

Questions:

1. Do I have to run 4 separate queries to capture the report data? If so how do I combine the results into a single page report?
2. Can I compute the needed date parameters and pass them to the queries when I request the report or do I have to manually enter them for each query?
3. How do I get the total lines to be the first line in the group.
4. Can the report be scheduled to run automatically on the 3rd of each month?

I would like the finished reports to be similar to the following:

Report 1 Clients Served by Gender 9/3/2003

August YTD August YTD
2003 2003 2002 2002
Total Clients 123 456 111 321
Female 97 300 60 180
Male 24 156 51 141



Report 2 Clients Served by Age 9/3/2003

August YTD August YTD
2003 2003 2002 2002
Total Clients 123 456 111 321
Under 18 3 3 1 2
18 - 45 100 400 100 300
Over 45 23 53 10 19

Thank you for your help!

 

[Duane Hookom]

You should be able to do this in one query for Gender and one for Age. However you haven't provided any clue as to how your values are calculated. If this were Sales by ProductID it would be something like:

SELECT ProductID,
Abs(Sum([Sales] * (Format([SaleDate],"yyyymm") = Format(DateAdd("m",-1,Date()),"yyyymm")))) As LastMonth,
Abs(Sum([Sales] * Year([SaleDate]) = Year(DateAdd("m",-1,Date())))) As ThisYear,
Abs(Sum([Sales] * (Format([SaleDate],"yyyymm") = Format(DateAdd("m",-13,Date()),"yyyymm")))) As PrevMonth,
Abs(Sum([Sales] * Year([SaleDate]) = Year(DateAdd("m",-13,Date())))) As PrevYear
FROM tblSales
GROUP BY ProductID;

The above is "air SQL" and may need to be corrected some but I hope you get the idea.

--
Duane Hookom
MS Access MVP


I am new enough to ACCESS that I am not sure that this is the correct News Group for this post. If I'm in the wrong place I apologize.

I have been asked to create a report which contains the following 4 columns and rows.

reporting month, current ytd, same month last year, last year ytd for the same period
Total1 nnn nnn nnn nnn
detail a aaa aaa aaa aaa
detail b bbb bbb bbb bbb


The report will be run during the 1st week of the month so reporting month is actually (current month - 1)

Questions:

1. Do I have to run 4 separate queries to capture the report data? If so how do I combine the results into a single page report?
2. Can I compute the needed date parameters and pass them to the queries when I request the report or do I have to manually enter them for each query?
3. How do I get the total lines to be the first line in the group.
4. Can the report be scheduled to run automatically on the 3rd of each month?

I would like the finished reports to be similar to the following:

Report 1 Clients Served by Gender 9/3/2003

August YTD August YTD
2003 2003 2002 2002
Total Clients 123 456 111 321
Female 97 300 60 180
Male 24 156 51 141



Report 2 Clients Served by Age 9/3/2003

August YTD August YTD
2003 2003 2002 2002
Total Clients 123 456 111 321
Under 18 3 3 1 2
18 - 45 100 400 100 300
Over 45 23 53 10 19

Thank you for your help!

 

[Dave van Wagenen]

Sorry,sometime I'm to close to the forest to see the trees. Total Clients are based on a count of new records added during the reporting period.

Dave v.
You should be able to do this in one query for Gender and one for Age. However you haven't provided any clue as to how your values are calculated. If this were Sales by ProductID it would be something like:

SELECT ProductID,
Abs(Sum([Sales] * (Format([SaleDate],"yyyymm") = Format(DateAdd("m",-1,Date()),"yyyymm")))) As LastMonth,
Abs(Sum([Sales] * Year([SaleDate]) = Year(DateAdd("m",-1,Date())))) As ThisYear,
Abs(Sum([Sales] * (Format([SaleDate],"yyyymm") = Format(DateAdd("m",-13,Date()),"yyyymm")))) As PrevMonth,
Abs(Sum([Sales] * Year([SaleDate]) = Year(DateAdd("m",-13,Date())))) As PrevYear
FROM tblSales
GROUP BY ProductID;

The above is "air SQL" and may need to be corrected some but I hope you get the idea.

--
Duane Hookom
MS Access MVP


I am new enough to ACCESS that I am not sure that this is the correct News Group for this post. If I'm in the wrong place I apologize.

I have been asked to create a report which contains the following 4 columns and rows.

reporting month, current ytd, same month last year, last year ytd for the same period
Total1 nnn nnn nnn nnn
detail a aaa aaa aaa aaa
detail b bbb bbb bbb bbb


The report will be run during the 1st week of the month so reporting month is actually (current month - 1)

Questions:

1. Do I have to run 4 separate queries to capture the report data? If so how do I combine the results into a single page report?
2. Can I compute the needed date parameters and pass them to the queries when I request the report or do I have to manually enter them for each query?
3. How do I get the total lines to be the first line in the group.
4. Can the report be scheduled to run automatically on the 3rd of each month?

I would like the finished reports to be similar to the following:

Report 1 Clients Served by Gender 9/3/2003

August YTD August YTD
2003 2003 2002 2002
Total Clients 123 456 111 321
Female 97 300 60 180
Male 24 156 51 141



Report 2 Clients Served by Age 9/3/2003

August YTD August YTD
2003 2003 2002 2002
Total Clients 123 456 111 321
Under 18 3 3 1 2
18 - 45 100 400 100 300
Over 45 23 53 10 19

Thank you for your help!

 

[Duane Hookom]

And how do we know which new records are added during the reporting period?
Did the information I provided help?

--
Duane Hookom
MS Access MVP


Sorry,sometime I'm to close to the forest to see the trees. Total Clients
are based on a count of new records added during the reporting period.

Dave v.
You should be able to do this in one query for Gender and one for Age.
However you haven't provided any clue as to how your values are calculated.
If this were Sales by ProductID it would be something like:

SELECT ProductID,
Abs(Sum([Sales] * (Format([SaleDate],"yyyymm") =
Format(DateAdd("m",-1,Date()),"yyyymm")))) As LastMonth,
Abs(Sum([Sales] * Year([SaleDate]) = Year(DateAdd("m",-1,Date())))) As
ThisYear,
Abs(Sum([Sales] * (Format([SaleDate],"yyyymm") =
Format(DateAdd("m",-13,Date()),"yyyymm")))) As PrevMonth,
Abs(Sum([Sales] * Year([SaleDate]) = Year(DateAdd("m",-13,Date())))) As
PrevYear
FROM tblSales
GROUP BY ProductID;

The above is "air SQL" and may need to be corrected some but I hope you get
the idea.

--
Duane Hookom
MS Access MVP


I am new enough to ACCESS that I am not sure that this is the correct News
Group for this post. If I'm in the wrong place I apologize.

I have been asked to create a report which contains the following 4 columns
and rows.

reporting month, current ytd, same month last year,
last year ytd for the same period
Total1 nnn nnn nnn
nnn
detail a aaa aaa aaa
aaa
detail b bbb bbb bbb
bbb


The report will be run during the 1st week of the month so reporting month
is actually (current month - 1)

Questions:

1. Do I have to run 4 separate queries to capture the report data? If so how
do I combine the results into a single page report?
2. Can I compute the needed date parameters and pass them to the queries
when I request the report or do I have to manually enter them for each
query?
3. How do I get the total lines to be the first line in the group.
4. Can the report be scheduled to run automatically on the 3rd of each
month?

I would like the finished reports to be similar to the following:

Report 1 Clients Served by Gender
9/3/2003

August YTD August YTD
2003 2003 2002 2002
Total Clients 123 456 111 321
Female 97 300 60 180
Male 24 156 51 141



Report 2 Clients Served by Age
9/3/2003

August YTD August YTD
2003 2003 2002 2002
Total Clients 123 456 111 321
Under 18 3 3 1 2
18 - 45 100 400 100 300
Over 45 23 53 10 19

Thank you for your help!