Group by time error

[Bratlien]

I am attempting to set up a query which will perform aggregate functions on records from two fields (Speed, Volume) and group them by a time field (SensorTime). This should essentially result in several months of data being combined into a succint 24-hr time period, divided into 288 five-minute intervals.

The problem is that Access will group some of the time values together, but not others. After some detective work I discovered that many of the times that APPEAR to be equal are actually different values when carried out to the 15th decimal point. I learned that this is a result of rounding error within Excel, from whence I originally imported the data.

My question is: can I apply a ROUND function to times in the original table, thereby solving the problem in all the resulting queries (and if so, how)? Or will I need to round the time values in each individual query (this would take some time)? Or is there a better way to equalize these time values that I'm not aware of? I know Excel has a "Precision as displayed" option but I couldn't find anything similar in Access.

Eagerly awaiting advice!

-Bratlien

 

[neileg]

Yes, Access has a ROUND function. Since date/Time is actually stored as a number, you could use it to round your times. Or you could use the FORMAT function and specify the number of seconds you want.

You could apply that to your data in the table, but it would be better in the long run to construct a query that returns all of the table data, applying the rounding to the times. Then any time you would use the table, use the query instead.

 

[Bratlien]

Thanks neileg, I'll go with your suggestion of constructing a query that returns all the table data and round the times from there.

 

[Bratlien]

OK, one last quick question as I am just one step away from ending this problem: I made a query to display all the original table data and round the times off to the 10th decimal, but I get the error message "Syntax error in FROM clause." Here's the query.

SELECT xSensor1.*
FROM xSensor1
ROUND (xSensor1.SensorTime, 10);

If you could take a real quick look at this short query and just point out what piece of syntax I screwed up on, that would be fantastic!

 

[Brianwarnock]

I suspect that your query should be

SELECT Round(xSensor1.SensorTime) as RoundedTime
FROM xSensor1;

Brian

Edit Obviuosly you choose your own name for RoundedTime

 

[Bratlien]

Excellent! I don't know why I didn't see that in the first place. Thanks.