Query needs updating

[fredElliotRules]

Hi,

I've generated the following SQL statement...

SELECT (Int([age]/10)*10) AS MYG, Count([MYG]) AS Frequency
FROM Data
WHERE ((([Data].[Date of referal]) Between #1/1/2006# And #1/1/2007#) And (([Data].[Age])>10))
GROUP BY (Int([age]/10)*10);

the [MYG] field is basically an integer 10,20,30,40,50 etc I wish to change this field to 10 - 20, 20 - 30, 30 - 40 etc.

Is this possible using SQL?

 

[lawsonium]

Yes. Using an update and simple maths to add two integers. However, I would use a script as my SQL isn't very strong.

Matt.

 

[fredElliotRules]

Or as I have just found out wrapping the expression with str() to convert the field to a string and using ADODB.recordset for the rest. Thanks matey.

 

[lawsonium]

Good plan ;-)

 

[fredElliotRules]

    mysql = "SELECT str((Int([Age] / " & ageGroups & ") * " & ageGroups & ")) & ' - ' & str(9 + (Int([Age] / " & ageGroups & ") * " & ageGroups & ")) AS [AGEGROUPS], Count([AGEGROUPS]) AS Frequency " & _
    "FROM Data " & _
    "WHERE (((Data.[Date of referal]) Between #" & txtOnOpOne & "# And #" & txtOnOpTwo & "#)) " & _
    "GROUP BY (Int([Age]/" & ageGroups & ")*" & ageGroups & ");"

Works fine, no need for ADODB.recordset (It produces an error anyway).