Problem with counting

  • Thread starter Thread starter MikeA
  • Start date Start date
M

MikeA

I have a table that contains data from multiple years. I need to be able to
produce a summary report that displays counts of various records for any
given month in any given year. This part I have done. However, I also need to
put the total of all records YTD from the selected year on the report as
well. How can I get that number for the YTD when the query is selecting only
records from the selected month?

ie

Mar 2008

count a 23
count b 15
count c 55

Monthly total 93

YTD 2008 203

Thanks
 
Duane,

Thanks for replying. I tried that & wasn't getting anywhere so i thought I
was on the wrong path.

I have 2 reports both run from queries. The 1st selects records for a
particuar year and month and then I used formulas to count the various types
of records.
This is the query for the 1st report.

SELECT tbl_SAR.[SAR Number], tbl_Category.CategoryName, tbl_SAR.[Date Sent],
tbl_SAR.SARID, tbl_Category.CategoryID, DatePart("yyyy",[Date Sent]) AS
Expr3, DatePart("m",[Date Sent]) AS Expr2
FROM (tbl_Category RIGHT JOIN tbl_SubCategory ON tbl_Category.CategoryID =
tbl_SubCategory.CategoryID) RIGHT JOIN tbl_SAR ON
tbl_SubCategory.SubCategoryID = tbl_SAR.SubCategoryID
WHERE (((DatePart("yyyy",[Date Sent]))=[year]) AND ((DatePart("m",[Date
Sent]))=[Month]));

This is a sample of the counting logic. =Sum(Abs([CategoryID]=9))

This report works OK.

I then created a 2nd report that just counts records. I used this query.

SELECT Count(tbl_SAR.SARID) AS CountOfSARID, DatePart('yyyy',[Date Sent]) AS
Expr1
FROM tbl_SAR
GROUP BY DatePart('yyyy',[Date Sent])
HAVING (((DatePart('yyyy',[Date Sent]))=[YTD Year]));

Currently, there is only 2008 data but over time I will need to specify the
year I want to report.

Seperately, both report do what I want. But when I add the second report
with just the total to the 1st as a subreport, all that shows up on the
report from the subreport is the name of the report.

I don't have much experience with subreport, so i canot see what step I am
missing. It doesn't look like the 2nd query is selecting data.

When I open the report it asks me for the Year and Month from the 1st query,
but asks me for the YTD from the 2nd query 3 time. The 1st report comes out
OK, but like I mentioned the only thing I get from the 2nd report is the name
of the report.

Can you point me in the correct direction here.

Thanks

Mike
 
Where does [YTD Year] come from?
HAVING (((DatePart('yyyy',[Date Sent]))=[YTD Year]));
If it is a parameter prompt, change it to reference a control on a form.
Also make sure there is nothing in the Link Master/Child properties of the
subreport control.

--
Duane Hookom
Microsoft Access MVP


MikeA said:
Duane,

Thanks for replying. I tried that & wasn't getting anywhere so i thought I
was on the wrong path.

I have 2 reports both run from queries. The 1st selects records for a
particuar year and month and then I used formulas to count the various types
of records.
This is the query for the 1st report.

SELECT tbl_SAR.[SAR Number], tbl_Category.CategoryName, tbl_SAR.[Date Sent],
tbl_SAR.SARID, tbl_Category.CategoryID, DatePart("yyyy",[Date Sent]) AS
Expr3, DatePart("m",[Date Sent]) AS Expr2
FROM (tbl_Category RIGHT JOIN tbl_SubCategory ON tbl_Category.CategoryID =
tbl_SubCategory.CategoryID) RIGHT JOIN tbl_SAR ON
tbl_SubCategory.SubCategoryID = tbl_SAR.SubCategoryID
WHERE (((DatePart("yyyy",[Date Sent]))=[year]) AND ((DatePart("m",[Date
Sent]))=[Month]));

This is a sample of the counting logic. =Sum(Abs([CategoryID]=9))

This report works OK.

I then created a 2nd report that just counts records. I used this query.

SELECT Count(tbl_SAR.SARID) AS CountOfSARID, DatePart('yyyy',[Date Sent]) AS
Expr1
FROM tbl_SAR
GROUP BY DatePart('yyyy',[Date Sent])
HAVING (((DatePart('yyyy',[Date Sent]))=[YTD Year]));

Currently, there is only 2008 data but over time I will need to specify the
year I want to report.

Seperately, both report do what I want. But when I add the second report
with just the total to the 1st as a subreport, all that shows up on the
report from the subreport is the name of the report.

I don't have much experience with subreport, so i canot see what step I am
missing. It doesn't look like the 2nd query is selecting data.

When I open the report it asks me for the Year and Month from the 1st query,
but asks me for the YTD from the 2nd query 3 time. The 1st report comes out
OK, but like I mentioned the only thing I get from the 2nd report is the name
of the report.

Can you point me in the correct direction here.

Thanks

Mike



--
MikeA


Duane Hookom said:
Use a subreport.
Click to expand...
Click to expand...
 
Yes, it is a prompt from the query that counts the records for the YTD total.
I was looking for a way to tell it what year to total.

Not sure what you are suggesting with a form. Are you saying that I should
create a form representing a table and enter the variable data there to be
accessed by the query when doing the selection? Year, Month, YTD year?

There is nothing in the Link Master/Child properties of the subreport control.

thanks for being patient. I'm a novice with Access.
--
MikeA


Duane Hookom said:
Where does [YTD Year] come from?
HAVING (((DatePart('yyyy',[Date Sent]))=[YTD Year]));
If it is a parameter prompt, change it to reference a control on a form.
Also make sure there is nothing in the Link Master/Child properties of the
subreport control.

--
Duane Hookom
Microsoft Access MVP


MikeA said:
Duane,

Thanks for replying. I tried that & wasn't getting anywhere so i thought I
was on the wrong path.

I have 2 reports both run from queries. The 1st selects records for a
particuar year and month and then I used formulas to count the various types
of records.
This is the query for the 1st report.

SELECT tbl_SAR.[SAR Number], tbl_Category.CategoryName, tbl_SAR.[Date Sent],
tbl_SAR.SARID, tbl_Category.CategoryID, DatePart("yyyy",[Date Sent]) AS
Expr3, DatePart("m",[Date Sent]) AS Expr2
FROM (tbl_Category RIGHT JOIN tbl_SubCategory ON tbl_Category.CategoryID =
tbl_SubCategory.CategoryID) RIGHT JOIN tbl_SAR ON
tbl_SubCategory.SubCategoryID = tbl_SAR.SubCategoryID
WHERE (((DatePart("yyyy",[Date Sent]))=[year]) AND ((DatePart("m",[Date
Sent]))=[Month]));

This is a sample of the counting logic. =Sum(Abs([CategoryID]=9))

This report works OK.

I then created a 2nd report that just counts records. I used this query.

SELECT Count(tbl_SAR.SARID) AS CountOfSARID, DatePart('yyyy',[Date Sent]) AS
Expr1
FROM tbl_SAR
GROUP BY DatePart('yyyy',[Date Sent])
HAVING (((DatePart('yyyy',[Date Sent]))=[YTD Year]));

Currently, there is only 2008 data but over time I will need to specify the
year I want to report.

Seperately, both report do what I want. But when I add the second report
with just the total to the 1st as a subreport, all that shows up on the
report from the subreport is the name of the report.

I don't have much experience with subreport, so i canot see what step I am
missing. It doesn't look like the 2nd query is selecting data.

When I open the report it asks me for the Year and Month from the 1st query,
but asks me for the YTD from the 2nd query 3 time. The 1st report comes out
OK, but like I mentioned the only thing I get from the 2nd report is the name
of the report.

Can you point me in the correct direction here.

Thanks

Mike



--
MikeA


Duane Hookom said:
Use a subreport.
--
Duane Hookom
Microsoft Access MVP


:

I have a table that contains data from multiple years. I need to be able to
produce a summary report that displays counts of various records for any
given month in any given year. This part I have done. However, I also need to
put the total of all records YTD from the selected year on the report as
well. How can I get that number for the YTD when the query is selecting only
records from the selected month?

ie

Mar 2008

count a 23
count b 15
count c 55

Monthly total 93

YTD 2008 203

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Here is my opinion on parameter prompts
http://www.tek-tips.com/faqs.cfm?fid=6763.

I assume your query will return just one year so it should work well the
record source of your subreport.


--
Duane Hookom
Microsoft Access MVP


MikeA said:
Yes, it is a prompt from the query that counts the records for the YTD total.
I was looking for a way to tell it what year to total.

Not sure what you are suggesting with a form. Are you saying that I should
create a form representing a table and enter the variable data there to be
accessed by the query when doing the selection? Year, Month, YTD year?

There is nothing in the Link Master/Child properties of the subreport control.

thanks for being patient. I'm a novice with Access.
--
MikeA


Duane Hookom said:
Where does [YTD Year] come from?
HAVING (((DatePart('yyyy',[Date Sent]))=[YTD Year]));
If it is a parameter prompt, change it to reference a control on a form.
Also make sure there is nothing in the Link Master/Child properties of the
subreport control.

--
Duane Hookom
Microsoft Access MVP


MikeA said:
Duane,

Thanks for replying. I tried that & wasn't getting anywhere so i thought I
was on the wrong path.

I have 2 reports both run from queries. The 1st selects records for a
particuar year and month and then I used formulas to count the various types
of records.
This is the query for the 1st report.

SELECT tbl_SAR.[SAR Number], tbl_Category.CategoryName, tbl_SAR.[Date Sent],
tbl_SAR.SARID, tbl_Category.CategoryID, DatePart("yyyy",[Date Sent]) AS
Expr3, DatePart("m",[Date Sent]) AS Expr2
FROM (tbl_Category RIGHT JOIN tbl_SubCategory ON tbl_Category.CategoryID =
tbl_SubCategory.CategoryID) RIGHT JOIN tbl_SAR ON
tbl_SubCategory.SubCategoryID = tbl_SAR.SubCategoryID
WHERE (((DatePart("yyyy",[Date Sent]))=[year]) AND ((DatePart("m",[Date
Sent]))=[Month]));

This is a sample of the counting logic. =Sum(Abs([CategoryID]=9))

This report works OK.

I then created a 2nd report that just counts records. I used this query.

SELECT Count(tbl_SAR.SARID) AS CountOfSARID, DatePart('yyyy',[Date Sent]) AS
Expr1
FROM tbl_SAR
GROUP BY DatePart('yyyy',[Date Sent])
HAVING (((DatePart('yyyy',[Date Sent]))=[YTD Year]));

Currently, there is only 2008 data but over time I will need to specify the
year I want to report.

Seperately, both report do what I want. But when I add the second report
with just the total to the 1st as a subreport, all that shows up on the
report from the subreport is the name of the report.

I don't have much experience with subreport, so i canot see what step I am
missing. It doesn't look like the 2nd query is selecting data.

When I open the report it asks me for the Year and Month from the 1st query,
but asks me for the YTD from the 2nd query 3 time. The 1st report comes out
OK, but like I mentioned the only thing I get from the 2nd report is the name
of the report.

Can you point me in the correct direction here.

Thanks

Mike



--
MikeA


:

Use a subreport.
--
Duane Hookom
Microsoft Access MVP


:

I have a table that contains data from multiple years. I need to be able to
produce a summary report that displays counts of various records for any
given month in any given year. This part I have done. However, I also need to
put the total of all records YTD from the selected year on the report as
well. How can I get that number for the YTD when the query is selecting only
records from the selected month?

ie

Mar 2008

count a 23
count b 15
count c 55

Monthly total 93

YTD 2008 203

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top