G
Guest
Does anyone know what the limit on Access's handling of Decimal Fields is ?
While developing some dynamic SQL fields (with precision 19 and scale 10)
caused Access to crash repeatedly. (I guess there is some poor person @
Microsoft trying to decipher the crash responses I caused.) When I changed
the field to to integers, the problem was resolved. I really need to be
working to around 8 decimal places.......what data type suits? (Floating
point is not suitable due to inaccurate calculation...)
The SQL where the problem manifested itself is below:
SELECT D.DomainName as Domain , dbo.GetLevel(MD.RecordId) as Level ,
SUM(CASE WHEN MD.MeasureDate = '2005-10-01' THEN MD.Weighting ELSE 0 END)
AS [Oct-2005],
SUM(CASE WHEN MD.MeasureDate = '2005-11-01' THEN MD.Weighting ELSE 0 END)
AS [Nov-2005],
SUM(CASE WHEN MD.MeasureDate = '2005-12-01' THEN MD.Weighting ELSE 0 END)
AS [Dec-2005],
SUM(CASE WHEN MD.MeasureDate = '2006-01-01' THEN MD.Weighting ELSE 0 END)
AS [Jan-2006],
SUM(CASE WHEN MD.MeasureDate = '2006-02-01' THEN MD.Weighting ELSE 0 END)
AS [Feb-2006],
SUM(CASE WHEN MD.MeasureDate = '2006-03-01' THEN MD.Weighting ELSE 0 END)
AS [Mar-2006]
FROM dbo.MeasureDetails MD INNER JOIN dbo.Measures M ON MD.measureId =
M.measureId
INNER Join dbo.Domains D ON M.domainId = D.domainId
WHERE M.TimeFrame = 'Monthly '
AND MD.MeasureDate BETWEEN '2005-10-01' AND '2006-04-01'
AND dbo.GetLevel(MD.RecordId) NOT IN (' ')
GROUP BY D.DomainName, dbo.GetLevel(MD.RecordId)
ORDER BY D.DomainName, dbo.GetLevel(MD.RecordId)
While developing some dynamic SQL fields (with precision 19 and scale 10)
caused Access to crash repeatedly. (I guess there is some poor person @
Microsoft trying to decipher the crash responses I caused.) When I changed
the field to to integers, the problem was resolved. I really need to be
working to around 8 decimal places.......what data type suits? (Floating
point is not suitable due to inaccurate calculation...)
The SQL where the problem manifested itself is below:
SELECT D.DomainName as Domain , dbo.GetLevel(MD.RecordId) as Level ,
SUM(CASE WHEN MD.MeasureDate = '2005-10-01' THEN MD.Weighting ELSE 0 END)
AS [Oct-2005],
SUM(CASE WHEN MD.MeasureDate = '2005-11-01' THEN MD.Weighting ELSE 0 END)
AS [Nov-2005],
SUM(CASE WHEN MD.MeasureDate = '2005-12-01' THEN MD.Weighting ELSE 0 END)
AS [Dec-2005],
SUM(CASE WHEN MD.MeasureDate = '2006-01-01' THEN MD.Weighting ELSE 0 END)
AS [Jan-2006],
SUM(CASE WHEN MD.MeasureDate = '2006-02-01' THEN MD.Weighting ELSE 0 END)
AS [Feb-2006],
SUM(CASE WHEN MD.MeasureDate = '2006-03-01' THEN MD.Weighting ELSE 0 END)
AS [Mar-2006]
FROM dbo.MeasureDetails MD INNER JOIN dbo.Measures M ON MD.measureId =
M.measureId
INNER Join dbo.Domains D ON M.domainId = D.domainId
WHERE M.TimeFrame = 'Monthly '
AND MD.MeasureDate BETWEEN '2005-10-01' AND '2006-04-01'
AND dbo.GetLevel(MD.RecordId) NOT IN (' ')
GROUP BY D.DomainName, dbo.GetLevel(MD.RecordId)
ORDER BY D.DomainName, dbo.GetLevel(MD.RecordId)