Change data

[jotb]

Most of the data I have collected is in number form. An example would be 11070 but I need to change that 11070 to 7070. The 11070 stands for 1 minute and 10.70 seconds. I need to change to 70.70 seconds. I'm not working with decimals but I want the data to look like 7070 instead of 11070. I have about 10k just like that example that need to be changed. Here are few other example's.

11182 should read 7182
11341 should read 7341
13941 should read 9941
14121 should read 10121

I hope someone out can help because I have absolutely no clue where to begin here.

Thank you in advance,
jotb

 

[28816]

Where: MSSDD is your minute, second, decimal format
And: SSSDD is your second, decimal formast
SSSDD = (Fix(MSSDD / 10000) * 6000) + (MSSDD Mod 10000)

 

[jotb]

Hello:

Thank you for sending me this information. However, I'm still confused! Do I need to change this information performing an update query and if so, what is the procedure? Is it possible that I need to change the data type from the database table? If it's possible can you walk me through each step to make the necessary changes. I recently purchased the Access 2000 for windows for dummies book by John Kaufeld but for some reason, I could not find how to change this data. Please help if you have the time.

Thank you in advance,
Joe

 

[GingerR]

clever 28816.

Joe--here's what you can do. this is just one example of how to get what you want.

make a new query. base it on your table.
in the query grid, bring down your field. since you didnt specify it's name, let's call it Field. in the second column, type:

(Fix([Field]/10000)*6000)+([Field] Mod 10000)

(courtesy of 28816) you will have to substitute your field name for the work 'field'.
run the query (the red ! at the top of the screen.)
did it work? worked for me.
now, depending on what you need to do...
1) if the data you currently have is the only data you'll ever have like this and you have to alter it just one time forevermore, then make a new field in your table, called i.e. NewTime, and yes write an update query where NewTime = the the code listed above. this will put the altered data into the new field for you to use.
2) if the data can just stay in the table like it is cause you're going to be getting more of it in that format all the time, you might just want to use that code above whereever you need to display the data in any forms, queries or reports. Another option for this process would be to put that code into a function (called i.e. ChangeTime) then whenever you need it, you call the function in a report or form or whatever (ChangeTime(Field)). If that's what you need to do, look up FUNCTIONS or write back.

ok? hope that helps.

 

[jotb]

Hello Ginger:

I messed up again! The field type is named FINAL TIME but final times can range from 58.10 seconds to 154.75 or 114.75 seconds. If course this depends on the distance which is my other field. Distances will range from 5.0 Furlongs to 12.0 furlongs. If I want to change all the distances that are 6.0F from 111.74 to 71.74 seconds, (all 6.0F times are different but range from 108.00 to 115.00, will I need to query with distance and final time? Also, in your reply before, you asked me to type in (fix( or is it (fix)and do I include all spaces in the rest of the formula? Last question do I type this formula in the field row or in the criteria? I will wait for your reply...

Thank you in advance,
Joe

 

[jotb]

Hello Ginger:

After I send the above reply, I went back into access and tried once more. I made a new query and the first field was distance only for 5.5F and second field was final time. In the third column I put in that formula and clicked on datasheet view and the times changed. It worked! Yes! I saved the query but I'm still left with one problem. On the table the times did not change. How do I get the times to change on the table without creating a new field? The reason I do not want to change the field is because within that field I have distances at 5.0F which do not need to be converted since the final times are less than 1 minute. One last question. When I performed the query and used the distance at 5.5F in the criteria, could I have also used 6.0f, 6.5f, 7.0f, 8.0f and so on in the OR section all the way straight down? Please reply and thank you for your time and patience.

Best regards,
Joe

 

[GingerR]

1) if it's less than one minute, the formula works too. it just converts it to the same thing as the orig. so maybe you dont have to distinguish between distances?

2) will someone be continually entering data into the table in that funky format that you are currently trying to alter? or from now on will all data be entered the 'new' way?

 

[jotb]

Hello Ginger:

Happy Holiday! Thank you for all your help. I will be adding new data each week in the correct format. I entered the data previously in error. However, today I converted the data correctly. I performed the query from the instructions you gave me. I ran the query then cut the new data times and pasted the new times in the database field final time. I sorted the data from the query and database. I probably could have done this another way but it worked the same. Anyway, I would like perform another query. Maybe you can help. I need to find out what the average 2fr (second fraction)is with 3 different distances 6.0,6.5,7.0. (distance is another field). There are 2 other fields that I need to run the same query which are (track and surface). Most of the times for the 2nd fraction range from 43.00 to 47.00. I want to combine all three distances above to retrieve 1 average for all 3 distances. I know how to do each one seperately but need to do all 3 distances together. Let me know if this can be done and what type of query I must use?
Thank you and best regards,
Joe