Page 1 of 4 123 ... LastLast
Results 1 to 15 of 47
  1. #1
    Join Date
    Oct 2007
    Posts
    11

    Unanswered: Input duration of song and sum them

    hi people, i m doing a term project database on music collection.
    problem i m facing right now is keying in my duration of song.because if i use the date/time format in my data type, they read it 3:54 as 3:54:00AM
    and i want to calculate the total duration of the album through some query function (i m not sure abt this)

    anyone can help? and problem is, i dont understnad SQL.

    thanks a lot!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I would use the date/time datatype. You can display the output any way you want (check the Format() function). That way, you can easily sum the times in a query (look at "totals" queries).
    Paul

  3. #3
    Join Date
    Oct 2007
    Posts
    11
    but it seems that i have to choose short time. but i cant do it this way as it seems cos they read it as hours and mins, even though the AM does not show. so when i sum up 3:54 and 2:22, i will end up with a 0.1234 for eg. cos its only a fraction for a day.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You must not have looked at the Format function like I recommended, which will let you format the result any way you want.
    Paul

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I wouldn't use the Date/Time datatype for the simple reason that minutes and seconds alone do not really constitute "time." What you're really talking about is duration. I'd approach the problem by having two integer fields for each song's duration, Minutes and Seconds.

    You could then have a calculated SecondsDuration field with something like

    Me.SecondsDuration = (Me.Minutes * 60) + Me.Seconds

    You can then total the SecondsDuration for all song tracks and convert the resultant total of seconds to Hours:Minuteseconds, using Intgeger division and the MOD function.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Oct 2007
    Posts
    11
    as in change something in e format box for my duration? can teach me how? sorry i m really new at this.

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Well, as you say, there are more than one way to skin a cat. I would use date/time because that's what the values contained are. Minutes and seconds alone may not constitute "time", but they are units of time. Summing them up will be simple, and displaying the result is a simple formatting issue. The date/time data type will store days/hours/minutes/seconds in a format it understands (double), whereas you need to perform math to get the values into a format you can work with.

    In the end, the cat gets skinned either way, so I guess the OP should go with whatever they're comfortable with.
    Paul

  8. #8
    Join Date
    Oct 2007
    Posts
    11
    Quote Originally Posted by Missinglinq
    I wouldn't use the Date/Time datatype for the simple reason that minutes and seconds alone do not really constitute "time." What you're really talking about is duration. I'd approach the problem by having two integer fields for each song's duration, Minutes and Seconds.

    You could then have a calculated SecondsDuration field with something like

    Me.SecondsDuration = (Me.Minutes * 60) + Me.Seconds

    You can then total the SecondsDuration for all song tracks and convert the resultant total of seconds to Hours:Minuteseconds, using Intgeger division and the MOD function.
    sorry, but i dont know how to do all those. only know how to have 2 columns with minutes and seconds. help pls?

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The answer, as mentioned, is to store the value as datetime. Calculations for total album length then become tonnes easier (see DateAdd() function for one!).

    Displaying the data correctly is a presentation issue and so should be handled as such. Make sure you get the plumbing sorted before you turn on the taps.

    Also, as mentioned before, look up the Format() function in the help files - should be fairly self explanatory if you spend 5-10 mins going over it.

    Have a try and post what you try back here, along with the result each attempt produces.
    George
    Home | Blog

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Missinglinq
    Me.SecondsDuration = (Me.Minutes * 60) + Me.Seconds
    i don't believe that what you meant to say actually came out right

    in case you guys are wondering, not every database system supports the idea of adding datetimes

    can't do it in sql server, for instance

    and george, it's a song collection -- why would i want to use Dateadd() on a song's duration?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you should be able to sum date time vales.. after all like all database storage prodcuts Access/JET stores date times as a number ( in its case its the number of days since some arbitary point in time, the decimal part is the representation of the minutes and seconds in that day).

    so summing all the decimals will give a amount of date/time value. after that it how you display it. you may need to sum it as cdbl(<mydattimevalue>) to expressly convert the date time value to a number.

    if for display purposes you display it as format(<mytimevalue>,"HHH:MMS") that should give the total number of hours.. it may not, if not then you need to be a bit more creative: you may need a custom format function to translate your date/time value into number of hours, minutes & seconds

    Code:
    public Function GetHoursMinutesSeconds(byval dblValue as double) as string
      Dim intNoHours as integer
      Dim intNoMins as integer
      Dim intNoSecs as integer
      if dblValue>0 then 'check to see if we have an integer element
        intnohours=int(dblvalue)*24 'may need to check the int function forget hat the function in access is...
        dblvalue=dblvalue-intnohours\24 'use integer maths to remove the number of days
      endif
      dblValue=dblValue*24 ' how many hours does this represent
      intnohours=intnohours + int(dblvlaue)
      dblvalue=(dblvalue-int(dblvlaue))*60 ' gives us the number of minutes left
      intnomins= int(dblvalue)
      intnoSecs = (dblvalue-intnomins)*60 'gives us the number of seconds remaining
      GetHoursMinutesSeconds= format(intNoHours,"#,##0:") & format(intNoMins,"00:")  & format(intNoSecs,"00") 
    end function
    that function isn't tested
    it isn't particulary elegant.. Im sure some of the true guru's here can improve on it, or offer a better solution/style, but the basic algolrythm is there.
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's all well and good, but you are exploiting a physical implementation detail which, as i already mentioned, doesn't work everywhere

    the implementation of datetimes as offsets from a base datetime is also arbitrary and fickle

    from a semantic point of view it makes no sense whatsoever to add '2007-09-09 09:37' and '2007-10-03 12:34'

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Rudy, you've said what you wouldn't do (I think), but not what you would do. The question was asked in an Access forum, so I'm not sure cross-platform compatibility is an issue.
    Paul

  14. #14
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by elephantcanfly
    ...problem i m facing right now is keying in my duration of song.because if i use the date/time format in my data type, they read it 3:54 as 3:54:00AM ...
    Enter a time as hh:mm:ss

    If you have a form where you're entering the duration, check the number of colons: if N=1, then prepend (insert in front of) the entry with "0:"

    When viewing the data...

    Assume a field named Duration that is a datetime field in TestTable, paste the following into a field name of a query

    Tracks_Duration: CStr(Int(24*60*Sum([TestTable].[Duration]))) & ":" & CStr((24*60*60*Sum([TestTable].[Duration])) Mod 60)
    Last edited by loquin; 10-03-07 at 17:11.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  15. #15
    Join Date
    Oct 2007
    Posts
    11
    i checked up format () function. but when i key in format as "nn:ss". i can enter as say 02:34. but when i click on e cell, it shows, 2:34:00 AM still.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •