Page 1 of 4 123 ... LastLast
Results 1 to 15 of 46
  1. #1
    Join Date
    Jan 2005
    Location
    Portland, Oregon
    Posts
    40

    Unanswered: Is DISTINCT really DISTINCT?

    When updating a date/time field called "Meeting Time" through a web page, it isnt posting properly to Access. The field is a Long Time. I can either enter the time as "hh:mm:ss AM" or "hh:mm AM" and i get the same result which is seen is the attached image file. This list should display DISTINCT meeting times and the amount of meetings starting thereon. As seen in the image, there are 2 records for "6:30 PM" even though i used DISTINCT in my statement. Help me i am perplexed.

    SELECT DISTINCT MeetingTime, COUNT(MeetingTime) AS Amount
    FROM tblMeetings GROUP BY MeetingTime
    ORDER BY MeetingTime
    Last edited by hildebran; 01-21-05 at 18:33. Reason: Claimed field was Long Date whan its actually Long Time.

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    try formatting meeting time column so it would show full date time info. maybe they have different seconds, or days.
    ghozy.

  3. #3
    Join Date
    Jan 2005
    Location
    Portland, Oregon
    Posts
    40
    Quote Originally Posted by ghozy
    try formatting meeting time column so it would show full date time info. maybe they have different seconds, or days.
    The dates are typed in specifically as seen. As a Long Time you can enter seconds but we dont.

  4. #4
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    matter of fact since you Group By meetingtime, you don't need the DISTINC keyword in SELECT statement. I'm not sure if this could fix your problem though. is 6:30 PM entry the only problem you have?
    ghozy.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just for the jolly factor, can you format the displayed time value as hh:mm:ss to prove they are actually exactly the same value.

    -PatP

  6. #6
    Join Date
    Jan 2005
    Location
    Portland, Oregon
    Posts
    40
    Quote Originally Posted by ghozy
    matter of fact since you Group By meetingtime, you don't need the DISTINC keyword in SELECT statement. I'm not sure if this could fix your problem though. is 6:30 PM entry the only problem you have?
    Good catch but didnt fix the problem. Originally DISTINCT wasnt in the statement. I added it just out of frustration.

  7. #7
    Join Date
    Jan 2005
    Location
    Portland, Oregon
    Posts
    40
    Pat Phelan, I changed to out put display long time to see if the time is actually stored the same and.........attachment.
    p.s.: I attached an image of the data in the actual table.
    Last edited by hildebran; 01-22-05 at 11:30. Reason: Added a second image and text explaining image.

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    PatP's suggestion - show the seconds and what do you see?
    izy

    LATER: you did
    hmmmm
    currently using SS 2008R2

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    go mad: show the day, month, year as well!

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Jan 2005
    Location
    Portland, Oregon
    Posts
    40
    Quote Originally Posted by izyrider
    go mad: show the day, month, year as well!

    izy
    It's a 'Long Time' format. There is not Date info sotred.

  11. #11
    Join Date
    Jan 2005
    Location
    Portland, Oregon
    Posts
    40
    Quote Originally Posted by izyrider
    PatP's suggestion - show the seconds and what do you see?
    izy

    LATER: you did
    hmmmm
    Identical information!? Next.

  12. #12
    Join Date
    Jan 2005
    Location
    Portland, Oregon
    Posts
    40

    Another Crazy thing.......

    On the webpage the time is actually a link to display all those meetings. If you click on either of the 6:30:00 links you get all 16 meetings as a result not 1 meeting for the first link or 15 meetings for the second link.

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    another thought:

    you are selecting DISTINCT (or GROUPing BY) an access date-time - which is a floating point number -1.79769313486231E308 to -4.94065645841247E-324 and 4.94065645841247E-324 to 1.79769313486232E308

    but for the display, access is rounding the float to the nearest 1.15741E-05

    ...there are a huge number of DISTINCT values in the half-second before and half-second after 06:30:00 ...but ALL those values will be displayed as 06:30:00
    ...they will of course SELECT DISTINCT separately because the underlying values are distinct.

    try displaying the time as a number with max decimals and see if there is a difference.
    you might not see the difference with only 15 places. try multiplying by a million or billion.

    if still nothing shows, stick the times into a recordset and see if this6AM = that6AM

    are any of these times coming in from calculations?

    izy
    Last edited by izyrider; 01-22-05 at 11:45. Reason: forgot the +ve range
    currently using SS 2008R2

  14. #14
    Join Date
    Jan 2005
    Location
    Portland, Oregon
    Posts
    40
    Quote Originally Posted by izyrider
    another thought:

    you are selecting DISTINCT (or GROUPing BY) an access date-time - which is a floating point number -1.79769313486231E308 to -4.94065645841247E-324 and 4.94065645841247E-324 to 1.79769313486232E308

    but for the display, access is rounding the float to the nearest 1.15741E-05

    ...there are a huge number of DISTINCT values in the half-second before and half-second after 06:30:00 ...but ALL those values will be displayed as 06:30:00
    ...they will of course SELECT DISTINCT separately because the underlying values are distinct.

    try displaying the time as a number with max decimals and see if there is a difference.
    you might not see the difference with only 15 places. try multiplying by a million or billion.

    if still nothing shows, stick the times into a recordset and see if this6AM = that6AM

    are any of these times coming in from calculations?

    izy
    izyrider, I see you point. As a side note, i just entered another meeting into the db using the web interface. I entered '6:30:00 PM' exactly and got the same result. Is that not exact enough for Access to know what the value is. Does it subdivide even more?

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    dunno.

    ...but an observation:
    06:30:00 expressed access style as a fractional day is
    0.270833333333333??????????

    according to Excel, the recurring 3 ends at the 16th decimal (but I suspect this is an Excel float calculation limit rather than the truth). a bit of manual long division would reveal the truth if you want to try it.

    it is tough to express a recurring decimal in binary!

    izy
    currently using SS 2008R2

Posting Permissions

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