Averages of data in a record source

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All, I searched for some help on this but could not find exactly what I
was looking for.

I have a Text box on my form for NATURES OF DISTRESS, and in it, I have
about 20 different options, derived from a lookup to another table.

What I am trying to do is have a nice pie chart (or at least a report for
now) of the different types of NATURES OF DISTRESS. Is this possible since I
am only using that ONE thing for a report?

Thanks
 
hi,

It is possible to have a pie chart with the averages for each NATURE OF
DISTRESS. In the report's design view, choose Insert --> Graph.

The graph's type would be a pie chart.

The graph's row source would be something like:
SELECT tbl_name.[distresstype], AVG(tbl_name.num) AS AvgOfnum FROM tbl_name
GROUP BY tbl_name.distresstypeHAVING (((tbl_name.distresstype) Is Not Null));


Hope this helps,
geebee
 
Thanks for the reply geebee, unfortunately I could not get it to work, I rcvd
syntax errors. It is no doubt due to my ineptness and not your instructions.

Let me clarify a couple of things because I realize now I may not have said
it all before. I am trying to get an average of each Nature of Distress,
compared to the total amount of records (a number which I have on each form
also, called SENE_CASE_#).

So, right now, I have 17 records entered, I want to know of that 17, what
percentage was each type of Nature of Distress.

Any furthur help greatly appreciated.



geebee said:
hi,

It is possible to have a pie chart with the averages for each NATURE OF
DISTRESS. In the report's design view, choose Insert --> Graph.

The graph's type would be a pie chart.

The graph's row source would be something like:
SELECT tbl_name.[distresstype], AVG(tbl_name.num) AS AvgOfnum FROM tbl_name
GROUP BY tbl_name.distresstypeHAVING (((tbl_name.distresstype) Is Not Null));


Hope this helps,
geebee



Scuda said:
Hi All, I searched for some help on this but could not find exactly what I
was looking for.

I have a Text box on my form for NATURES OF DISTRESS, and in it, I have
about 20 different options, derived from a lookup to another table.

What I am trying to do is have a nice pie chart (or at least a report for
now) of the different types of NATURES OF DISTRESS. Is this possible since I
am only using that ONE thing for a report?

Thanks
Click to expand...
Click to expand...
 
hi,

Sorry it took so long to get back to you...
There is no need to take the total number of records from the form. A better
way to get the total number of records would be to create a saved query. In
this case, I created totalquery to get the total
number of records:

save as totalquery:
SELECT Count(tblname.distress) AS CountOfDistress
FROM tblname;

Then, you could create your pie chart, with percentages of each distress as
compared to the total number calculated in a query:

pie chart row source:
SELECT tblname.[distress], Count(tblname.[distress]) AS CountOfdistress1,
totalquery.CountOfDistress,
Format(Count([tblname].[distress])/[totalquery].[CountOfDistress],"Percent")
AS percentage
FROM tblname, totalquery
GROUP BY tblname.distress, totalquery.CountOfDistress;

Hope this helps,
geebee


Scuda said:
Thanks for the reply geebee, unfortunately I could not get it to work, I rcvd
syntax errors. It is no doubt due to my ineptness and not your instructions.

Let me clarify a couple of things because I realize now I may not have said
it all before. I am trying to get an average of each Nature of Distress,
compared to the total amount of records (a number which I have on each form
also, called SENE_CASE_#).

So, right now, I have 17 records entered, I want to know of that 17, what
percentage was each type of Nature of Distress.

Any furthur help greatly appreciated.



geebee said:
hi,

It is possible to have a pie chart with the averages for each NATURE OF
DISTRESS. In the report's design view, choose Insert --> Graph.

The graph's type would be a pie chart.

The graph's row source would be something like:
SELECT tbl_name.[distresstype], AVG(tbl_name.num) AS AvgOfnum FROM tbl_name
GROUP BY tbl_name.distresstypeHAVING (((tbl_name.distresstype) Is Not Null));


Hope this helps,
geebee



Scuda said:
Hi All, I searched for some help on this but could not find exactly what I
was looking for.

I have a Text box on my form for NATURES OF DISTRESS, and in it, I have
about 20 different options, derived from a lookup to another table.

What I am trying to do is have a nice pie chart (or at least a report for
now) of the different types of NATURES OF DISTRESS. Is this possible since I
am only using that ONE thing for a report?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Hi geebee, thanks again for the help. Question though, I am getting a syntax
error in my query, this is what I have:

SELECT Count(SENE Sar Log.nature of distress) AS CountOfNature of Distress
FROM [SENE Sar Log];

Whereas SENE Sar Log is the name of my table, and my Tetx field is NATURE OF
DISTRESS.

What am I doing wrong.

Thanks again for the patience.


geebee said:
hi,

Sorry it took so long to get back to you...
There is no need to take the total number of records from the form. A better
way to get the total number of records would be to create a saved query. In
this case, I created totalquery to get the total
number of records:

save as totalquery:
SELECT Count(tblname.distress) AS CountOfDistress
FROM tblname;

Then, you could create your pie chart, with percentages of each distress as
compared to the total number calculated in a query:

pie chart row source:
SELECT tblname.[distress], Count(tblname.[distress]) AS CountOfdistress1,
totalquery.CountOfDistress,
Format(Count([tblname].[distress])/[totalquery].[CountOfDistress],"Percent")
AS percentage
FROM tblname, totalquery
GROUP BY tblname.distress, totalquery.CountOfDistress;

Hope this helps,
geebee


Scuda said:
Thanks for the reply geebee, unfortunately I could not get it to work, I rcvd
syntax errors. It is no doubt due to my ineptness and not your instructions.

Let me clarify a couple of things because I realize now I may not have said
it all before. I am trying to get an average of each Nature of Distress,
compared to the total amount of records (a number which I have on each form
also, called SENE_CASE_#).

So, right now, I have 17 records entered, I want to know of that 17, what
percentage was each type of Nature of Distress.

Any furthur help greatly appreciated.



geebee said:
hi,

It is possible to have a pie chart with the averages for each NATURE OF
DISTRESS. In the report's design view, choose Insert --> Graph.

The graph's type would be a pie chart.

The graph's row source would be something like:
SELECT tbl_name.[distresstype], AVG(tbl_name.num) AS AvgOfnum FROM tbl_name
GROUP BY tbl_name.distresstypeHAVING (((tbl_name.distresstype) Is Not Null));


Hope this helps,
geebee



:

Hi All, I searched for some help on this but could not find exactly what I
was looking for.

I have a Text box on my form for NATURES OF DISTRESS, and in it, I have
about 20 different options, derived from a lookup to another table.

What I am trying to do is have a nice pie chart (or at least a report for
now) of the different types of NATURES OF DISTRESS. Is this possible since I
am only using that ONE thing for a report?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
hi,

Access can be picky when dealing with entities containing spaces in their
names. So I would suggest not using spaces in table names, column names, and
column alias names.

To correct this, I would suggest first renaming your table [SENE Sar Log] to
[tbl_SENE_Sar_Log], your column [nature of distress] to [nature_of_distress],
and finally your column alias
[CountOfNature of Distress] to [CountOfNature_of_Distress], yielding a
revised query of :

SELECT Count(tbl_SENE_Sar_Log.[nature_of_distress]) AS
CountOfNature_of_Distress
FROM [tbl_SENE_Sar_Log];


geebee


Scuda said:
Hi geebee, thanks again for the help. Question though, I am getting a syntax
error in my query, this is what I have:

SELECT Count(SENE Sar Log.nature of distress) AS CountOfNature of Distress
FROM [SENE Sar Log];

Whereas SENE Sar Log is the name of my table, and my Tetx field is NATURE OF
DISTRESS.

What am I doing wrong.

Thanks again for the patience.


geebee said:
hi,

Sorry it took so long to get back to you...
There is no need to take the total number of records from the form. A better
way to get the total number of records would be to create a saved query. In
this case, I created totalquery to get the total
number of records:

save as totalquery:
SELECT Count(tblname.distress) AS CountOfDistress
FROM tblname;

Then, you could create your pie chart, with percentages of each distress as
compared to the total number calculated in a query:

pie chart row source:
SELECT tblname.[distress], Count(tblname.[distress]) AS CountOfdistress1,
totalquery.CountOfDistress,
Format(Count([tblname].[distress])/[totalquery].[CountOfDistress],"Percent")
AS percentage
FROM tblname, totalquery
GROUP BY tblname.distress, totalquery.CountOfDistress;

Hope this helps,
geebee


Scuda said:
Thanks for the reply geebee, unfortunately I could not get it to work, I rcvd
syntax errors. It is no doubt due to my ineptness and not your instructions.

Let me clarify a couple of things because I realize now I may not have said
it all before. I am trying to get an average of each Nature of Distress,
compared to the total amount of records (a number which I have on each form
also, called SENE_CASE_#).

So, right now, I have 17 records entered, I want to know of that 17, what
percentage was each type of Nature of Distress.

Any furthur help greatly appreciated.



:

hi,

It is possible to have a pie chart with the averages for each NATURE OF
DISTRESS. In the report's design view, choose Insert --> Graph.

The graph's type would be a pie chart.

The graph's row source would be something like:
SELECT tbl_name.[distresstype], AVG(tbl_name.num) AS AvgOfnum FROM tbl_name
GROUP BY tbl_name.distresstypeHAVING (((tbl_name.distresstype) Is Not Null));


Hope this helps,
geebee



:

Hi All, I searched for some help on this but could not find exactly what I
was looking for.

I have a Text box on my form for NATURES OF DISTRESS, and in it, I have
about 20 different options, derived from a lookup to another table.

What I am trying to do is have a nice pie chart (or at least a report for
now) of the different types of NATURES OF DISTRESS. Is this possible since I
am only using that ONE thing for a report?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
I fully agree that this is best practise, and should be done, but it may be
quite a task in a large system.

If you must retain the spaces [ ] around the names should solve the
immediate problem.
SELECT Count([SENE Sar Log.nature of distress]) AS [CountOfNature of
Distress]
FROM [SENE Sar Log];

but lose the spaces ASAP

geebee said:
hi,

Access can be picky when dealing with entities containing spaces in their
names. So I would suggest not using spaces in table names, column names,
and
column alias names.

To correct this, I would suggest first renaming your table [SENE Sar Log]
to
[tbl_SENE_Sar_Log], your column [nature of distress] to
[nature_of_distress],
and finally your column alias
[CountOfNature of Distress] to [CountOfNature_of_Distress], yielding a
revised query of :

SELECT Count(tbl_SENE_Sar_Log.[nature_of_distress]) AS
CountOfNature_of_Distress
FROM [tbl_SENE_Sar_Log];


geebee


Scuda said:
Hi geebee, thanks again for the help. Question though, I am getting a
syntax
error in my query, this is what I have:

SELECT Count(SENE Sar Log.nature of distress) AS CountOfNature of
Distress
FROM [SENE Sar Log];

Whereas SENE Sar Log is the name of my table, and my Tetx field is NATURE
OF
DISTRESS.

What am I doing wrong.

Thanks again for the patience.


geebee said:
hi,

Sorry it took so long to get back to you...
There is no need to take the total number of records from the form. A
better
way to get the total number of records would be to create a saved
query. In
this case, I created totalquery to get the total
number of records:

save as totalquery:
SELECT Count(tblname.distress) AS CountOfDistress
FROM tblname;

Then, you could create your pie chart, with percentages of each
distress as
compared to the total number calculated in a query:

pie chart row source:
SELECT tblname.[distress], Count(tblname.[distress]) AS
CountOfdistress1,
totalquery.CountOfDistress,
Format(Count([tblname].[distress])/[totalquery].[CountOfDistress],"Percent")
AS percentage
FROM tblname, totalquery
GROUP BY tblname.distress, totalquery.CountOfDistress;

Hope this helps,
geebee


:

Thanks for the reply geebee, unfortunately I could not get it to
work, I rcvd
syntax errors. It is no doubt due to my ineptness and not your
instructions.

Let me clarify a couple of things because I realize now I may not
have said
it all before. I am trying to get an average of each Nature of
Distress,
compared to the total amount of records (a number which I have on
each form
also, called SENE_CASE_#).

So, right now, I have 17 records entered, I want to know of that 17,
what
percentage was each type of Nature of Distress.

Any furthur help greatly appreciated.



:

hi,

It is possible to have a pie chart with the averages for each
NATURE OF
DISTRESS. In the report's design view, choose Insert --> Graph.

The graph's type would be a pie chart.

The graph's row source would be something like:
SELECT tbl_name.[distresstype], AVG(tbl_name.num) AS AvgOfnum FROM
tbl_name
GROUP BY tbl_name.distresstypeHAVING (((tbl_name.distresstype) Is
Not Null));


Hope this helps,
geebee



:

Hi All, I searched for some help on this but could not find
exactly what I
was looking for.

I have a Text box on my form for NATURES OF DISTRESS, and in it,
I have
about 20 different options, derived from a lookup to another
table.

What I am trying to do is have a nice pie chart (or at least a
report for
now) of the different types of NATURES OF DISTRESS. Is this
possible since I
am only using that ONE thing for a report?

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