Totals for month and year

  • Thread starter Thread starter Leo
  • Start date Start date
L

Leo

I have a query (based on a table by the same name and fields) with 3 fields
named AccountNumber ( a primary key with autonumber in the table), DateOfHd (
a date field), EpogenInUnits ( a number field).

What I need is a form or a report that will have four columns as Year,
Month, Total Number of dialysis (every date will be counted as a dialysis)
for that month and total number of Epogen for that month. The form or the
report should start with January of a year and end with December of the year.

How do I accomplish this?
Please help.
Thanks
Leo
 
You need to change your query to something like:

SELECT Month([DateofHD]) AS MonthOfService, Year([DateofHD]) AS
YearOfService, Sum(tblDialysis.EpogenUnits) AS SumOfEpogenUnits
FROM tblDialysis
GROUP BY Month([DateofHD]), Year([DateofHD]);

The report can then easily give you the monthly totals and yearly total.

If you wanted to limit it to one year you would add that parameter to the
query. Like
Year([DateofHD]) = 2007

Regards

Kevin
 
One additional field added to the query.

SELECT Month([DateofHD]) AS MonthOfService
, Year([DateofHD]) AS YearOfService
, Count([DateofHD) as CountDialysis
, Sum(tblDialysis.EpogenUnits) AS SumOfEpogenUnits
FROM tblDialysis
GROUP BY Month([DateofHD]), Year([DateofHD]);

If you are not sure how to build this query and can only work in Query design
view, post back for step-by-step instructions.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

kc-mass said:
You need to change your query to something like:

SELECT Month([DateofHD]) AS MonthOfService, Year([DateofHD]) AS
YearOfService, Sum(tblDialysis.EpogenUnits) AS SumOfEpogenUnits
FROM tblDialysis
GROUP BY Month([DateofHD]), Year([DateofHD]);

The report can then easily give you the monthly totals and yearly total.

If you wanted to limit it to one year you would add that parameter to the
query. Like
Year([DateofHD]) = 2007

Regards

Kevin


Leo said:
I have a query (based on a table by the same name and fields) with 3 fields
named AccountNumber ( a primary key with autonumber in the table),
DateOfHd (
a date field), EpogenInUnits ( a number field).

What I need is a form or a report that will have four columns as Year,
Month, Total Number of dialysis (every date will be counted as a dialysis)
for that month and total number of Epogen for that month. The form or the
report should start with January of a year and end with December of the
year.

How do I accomplish this?
Please help.
Thanks
Leo
Click to expand...
Click to expand...
 
I really appreciate both of your help. I think I can manage from here on. I
will do that in SQL view of the query and if I run into problems I will seek
your help again.

Once again thanks for all the help.

Regard
Leo

John Spencer said:
One additional field added to the query.

SELECT Month([DateofHD]) AS MonthOfService
, Year([DateofHD]) AS YearOfService
, Count([DateofHD) as CountDialysis
, Sum(tblDialysis.EpogenUnits) AS SumOfEpogenUnits
FROM tblDialysis
GROUP BY Month([DateofHD]), Year([DateofHD]);

If you are not sure how to build this query and can only work in Query design
view, post back for step-by-step instructions.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

kc-mass said:
You need to change your query to something like:

SELECT Month([DateofHD]) AS MonthOfService, Year([DateofHD]) AS
YearOfService, Sum(tblDialysis.EpogenUnits) AS SumOfEpogenUnits
FROM tblDialysis
GROUP BY Month([DateofHD]), Year([DateofHD]);

The report can then easily give you the monthly totals and yearly total.

If you wanted to limit it to one year you would add that parameter to the
query. Like
Year([DateofHD]) = 2007

Regards

Kevin


Leo said:
I have a query (based on a table by the same name and fields) with 3 fields
named AccountNumber ( a primary key with autonumber in the table),
DateOfHd (
a date field), EpogenInUnits ( a number field).

What I need is a form or a report that will have four columns as Year,
Month, Total Number of dialysis (every date will be counted as a dialysis)
for that month and total number of Epogen for that month. The form or the
report should start with January of a year and end with December of the
year.

How do I accomplish this?
Please help.
Thanks
Leo
Click to expand...
Click to expand...
Click to expand...
 
I really appreciate both of your help. I think I can manage from here on. I
will do that in SQL view of the query and if I run into problems I will seek
your help again.

Once again thanks for all the help.

Regards
Leo


John Spencer said:
One additional field added to the query.

SELECT Month([DateofHD]) AS MonthOfService
, Year([DateofHD]) AS YearOfService
, Count([DateofHD) as CountDialysis
, Sum(tblDialysis.EpogenUnits) AS SumOfEpogenUnits
FROM tblDialysis
GROUP BY Month([DateofHD]), Year([DateofHD]);

If you are not sure how to build this query and can only work in Query design
view, post back for step-by-step instructions.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

kc-mass said:
You need to change your query to something like:

SELECT Month([DateofHD]) AS MonthOfService, Year([DateofHD]) AS
YearOfService, Sum(tblDialysis.EpogenUnits) AS SumOfEpogenUnits
FROM tblDialysis
GROUP BY Month([DateofHD]), Year([DateofHD]);

The report can then easily give you the monthly totals and yearly total.

If you wanted to limit it to one year you would add that parameter to the
query. Like
Year([DateofHD]) = 2007

Regards

Kevin


Leo said:
I have a query (based on a table by the same name and fields) with 3 fields
named AccountNumber ( a primary key with autonumber in the table),
DateOfHd (
a date field), EpogenInUnits ( a number field).

What I need is a form or a report that will have four columns as Year,
Month, Total Number of dialysis (every date will be counted as a dialysis)
for that month and total number of Epogen for that month. The form or the
report should start with January of a year and end with December of the
year.

How do I accomplish this?
Please help.
Thanks
Leo
Click to expand...
Click to expand...
Click to expand...
 
Back
Top