problem with continuous form to display data with operators

[Howlsta]

Hi Peops,

I want to display info like this as a continuous form.

Highest AIS Total Percentage
0
1
>+2

Highest AIS can be up to 9. I don't reckon i'll be able to do a count of all >=2 on one record of a query.
Thanks to some help I have done similar queries but the >=2 part has threw me. My query is like this:

SELECT childinfo.[Highest AIS], Count(childinfo.chilRestraint) AS [Number], Count([childinfo].[chilRestraint])/(SELECT
Count(*) FROM childinfo) AS [Percent]
FROM childinfo
GROUP BY childinfo.[Highest AIS]
ORDER BY Count(childinfo.chilRestraint) DESC;

because of the >=2 part I don't know if a query will do it here. Will I have to do a whole load of sums in control sources of text boxes to get what I want?

thanks

Rich

 

[Howlsta]

I've come up with a solution of sorts.
In the form footer i've done a dcount of all AIS's with 2 or higher. I altered my original query to just pick-up AIS of less than 2, so there are two lines on the continuous form.

Problem is that I have a gap between the last row and the textbox and label in the form footer. On my other forms i've got total in the form footer and there is no gap. I've checked my form properties and can't see any discrepancies. Help!

EDIT: Okay seem to have an idea of how to close the gap - it seems if you 'stretch' the grid in the footer it gets closer.


If anyone has a better way to do this i'm interested as adding the >2 count in the footer will mean percentages will not be right and i'll have to write code for that too. I a query can be manipulated like this it would be great!

 

[Howlsta]

Managed to find a way to get the query to display exactly how I wanted with this SQL:

SELECT IIf([Highest AIS]>=2,2,[Highest AIS]) AS AISGroup, Count(childinfo.[Highest AIS]) AS [Number], Count([childinfo].[Highest AIS])/(SELECT
Count(*) FROM childinfo) AS [Percent]
FROM childinfo
GROUP BY IIf([Highest AIS]>=2,2,[Highest AIS]);

or this as the field criteria:

AISGroup: IIf([Highest AIS]>=2,2,[Highest AIS])

very happy with this!!!!!!!