Totaling Groups

[Rob]

I have a query that has the following information.

ID Title Probability Impact Exposure


Numbers located within the Exposure value vary and in some case repeat. I
need to get a count of the number of times an Exposure value shows up. This
info I will later place on a "Risk Cube" for easy viewing. I hope that that
this is an easy task.

I am new to Access and have made good progress with it so far but this has
me confused.

Thanks for the help.

 

[Bob Barrows [MVP]]

I have a query that has the following information.

ID Title Probability Impact Exposure


Numbers located within the Exposure value vary and in some case
repeat. I need to get a count of the number of times an Exposure
value shows up. This info I will later place on a "Risk Cube" for
easy viewing. I hope that that this is an easy task.

I am new to Access and have made good progress with it so far but
this has me confused.

Thanks for the help.

So you have data like

1 title1 .55 huge 33
2 title2 .78 minimal 48
3 title3 .65 small 33
4 title4 .85 medium 65
5 title5 .32 small 48

And you want a query to return two columns, exposure and exposurecount,
like this?

Exposure ExposureCount
33 2
48 2

What you need is a group by query. Create a new query, switch it to SQL
View and paste the following in. Put in your table name and run it;

Select Exposure, Count(*) As ExposureCount
FROM tablename
GROUP BY Exposure
HAVING Count(*) > 1

Switch to design mode to see what it looks like in the grid.

 

[Rob]

So you have data like

1 title1 .55 huge 33
2 title2 .78 minimal 48
3 title3 .65 small 33
4 title4 .85 medium 65
5 title5 .32 small 48

And you want a query to return two columns, exposure and exposurecount,
like this?

Exposure ExposureCount
33 2
48 2

What you need is a group by query. Create a new query, switch it to SQL
View and paste the following in. Put in your table name and run it;

Select Exposure, Count(*) As ExposureCount
FROM tablename
GROUP BY Exposure
HAVING Count(*) > 1

Switch to design mode to see what it looks like in the grid.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


Thanks it works. Once you start using Access you just cannot stop.