August 25, 2008 at 1:34 pm
I am converting an old Access database in use for over 9 years into an Access Front-end (for the users) and a SQL Backend (for better storage, etc.) I am a complete novice with 35 years of Access and database experience- none in SQL.
We collect data daily in Avaya's CMS of staff logins and logi=out, and total time in each of the designated AUX codes: Breaks, Lunch, PD (personal time), etc. Daily I move that information using excel , I then bring that data from an Excel spreadsheet to my Access database. To convert Avaya time for the AUX codes into real minets we use the Time divided by 86400. This gives us a time we can then match againist scheduled time. Example : 1803 avaya = 30:03 actual time. ; 1023 avaya = 17:03 actual.
Now when I attempt to use the formula with the imported data from Avaya to the SQL system, and use the Formula I get example : 550 avaya = 407407407E-3 ; or 384 Avaya = 444444444E-3.
The data comes into the SQL as a Float? an the formaula creates a Float. If I change the data import to a int, the results are all zeros.
Any suggestion on where I can calculate this correctly, as before using the old database, by the way.... I have to move everything to SQL by the end of September.
Oh, lastly I attempted to convert the old database to sql using Microsoft conversion programs, and nothing worked for our Database too many special calculated tables and queries. I have to do it by hand, and I can't find an references for complicated conversions to sql from Access 2003.
Thanks Pete
August 25, 2008 at 2:42 pm
How does 1803/86400 = 30 minutes 3 seconds (30:03)? Or is 1803 the result of the time time divided by 86400? What is the base for the time?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 25, 2008 at 3:17 pm
Correct it is Minutes, 30 Minutes and 03 seconds.
Pete
August 25, 2008 at 3:22 pm
Aaya is giveng us the time in Seconds, we then us 86400 to get Minutes and seconds. So I suppose I could divide the second by Avaya by 60 to get the same results in SQL, Right?
August 25, 2008 at 3:32 pm
pete.trudell (8/25/2008)
Aaya is giveng us the time in Seconds, we then us 86400 to get Minutes and seconds. So I suppose I could divide the second by Avaya by 60 to get the same results in SQL, Right?
Basically, although remember that is just going to give you the 30 minutes in the case of 1803.
What you need will depend on what you are using it for?
Do you need minutes and seconds (30:03) or minutes and partial minutes (30.05) (both examples using the 1803 value)?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 26, 2008 at 8:23 am
Actually we do need it in Minutes and seconds format as this is ported to a form which list the actual times for each function, and then their scheduled time, the supervisors themn will see where there is need for improvement.
The Avaya gives us , login and logout times, breaks, lunches, and pd time. So I need to comvert all of those in Hrs:Mins:Sec. That is why we used the Excel formula in Access of Time/86400.
Maybe it's the format of the Avaya time that needs to be changed, or changed on the spreadsheet before being transported into the Daily SQL Query.
Whoa, Whoa, Whoa--- Got it, I'll make all the adjustments in Excel, then when I run my macro to import the data i'll bring in the times already calculated.
Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply