Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jan 2005
    Location
    Tucson, Arizona, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sum function on Date (A2KSR1)

    Hello,

    I have a large database, over 500k records, which I have inherited.

    It has a field PickupTOD which is a date/time field. We have up to 25 different times entered in the field for any given date, there is also a Passenger field which tells how many passengers were on a bus at the given time.

    My problem is I need to get a count of total passengers for each day last year, 2004, and do not want to add them up by hand, it will be a very big number, but just to total them;

    1/1/2004 250
    1/2/2004 342
    etc.

    What do I do to get this date only function out of the PickupTOD field when I make the query grid?

    Any help would be appreciated. I have been playing with this for an hour and can only get the data in;

    1/1/2004 04:00 AM 4
    1/1/2004 04:15 AM 3
    etc.

    format. Any clue for the cluless?

    Thank you in advance

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Sum function on Date (A2KSR1)

    Create a query based on your table. Add a calculated column:

    PickupDate: Int([PickupTOD])

    and set the Format property of this column to Short Date or something similar. Also add the PickupTOD field to the query grid.
    Select View | Totals (or click the Totals button on the toolbar) to change the query to a Totals query. Leave the Total option for PickupDate as it is (Group By), and set it to Count for PickupTOD.
    That's it!

  3. #3
    New Lounger
    Join Date
    Jan 2005
    Location
    Tucson, Arizona, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum function on Date (A2KSR1)

    Hmm, I get an error, ODBC call failed, it is a SQL database I am accessing through the ODBC function of Access. I get a help that says use the convert function. Now I am really lost.


    Any ideas?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Sum function on Date (A2KSR1)

    Does this work?

    <code>PickupDate: DateSerial(Year([PickupTOD]),Month([PickupTOD]),Day([PickupTOD]))</code>

    If not, somebody with experience in both Access and SQL Server will have to help you further.

  5. #5
    New Lounger
    Join Date
    Jan 2005
    Location
    Tucson, Arizona, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum function on Date (A2KSR1)

    Getting closer, I get it to count but it still counts by the time and not just the date. It did get through SQL <img src=/S/angel.gif border=0 alt=angel width=15 height=21> .

    I need it to count the number of passengers per day and it is giving me the number of passengers per bus run.

    Much closer though. I am getting a digit in the calculated field now though and cannot figure out what it represents.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Sum function on Date (A2KSR1)

    Let's hope that someone who knows how dates and times work in SQL Server reads this.

  7. #7
    New Lounger
    Join Date
    Jan 2005
    Location
    Tucson, Arizona, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum function on Date (A2KSR1)

    I got it to work, I never would have without your help so thank you very much.

    I had to change the Count to Sum in the passengers field, remove the PickupTOD and just use the calculated field you gave me and it worked great.

    You are the best, thanks Hans

Posting Permissions

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