Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2012
    Location
    Toronto Ontario
    Posts
    10

    Unhappy Unanswered: Date and Sum is being a challenge!

    Hello,

    I was wondering if anyone would be able to assist as I have been trying to solve this issue with the intention to use this query to sum up and graph out our total daily sales.

    My first step was to create the query which is almost there but the query is not summing up anything.

    My intention here is to display unique dates with total sales for each day. (two columns)


    SELECT Timing,SUM(Total) AS [Totals]
    FROM tblservice
    GROUP BY Timing;


    FYI: My "Timing" is a short date & "Total" is the sale price of one service.
    I have attached an image of what I see in the output.
    Attached Thumbnails Attached Thumbnails output.PNG  

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    My only thought is that 'Timing' has a time of day as well as the date. IF this is the case then this may solve the problem
    Code:
    SELECT CDate(Int([Timing])) AS [Date], Sum([tblservice].Total) AS Totals FROM tblservice
    GROUP BY CDate(Int([Timing]))
    ORDER BY CDate(Int([Timing]));
    If this is not the problem then, at present, I have no other ideas.
    Let us know if this works!


    MTB

  3. #3
    Join Date
    Mar 2012
    Location
    Toronto Ontario
    Posts
    10

    Talking This Solution Work Great!

    Thanks

    This seems to filters out the time of day which solves the problem. Now records are shown as expected.

    I'll keep the CDate in mind never thought there was a time stamp in the table also even though I changed it to short date.

    Thanks Again!



    Quote Originally Posted by MikeTheBike View Post
    Hi

    My only thought is that 'Timing' has a time of day as well as the date. IF this is the case then this may solve the problem
    Code:
    SELECT CDate(Int([Timing])) AS [Date], Sum([tblservice].Total) AS Totals FROM tblservice
    GROUP BY CDate(Int([Timing]))
    ORDER BY CDate(Int([Timing]));
    If this is not the problem then, at present, I have no other ideas.
    Let us know if this works!


    MTB

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You usually see this kind of problem when setting a Default Value for a Date/Time Field to Now(), which returns a Date and Time, instead of Date(), which only returns the Date.

    Linq ;0)>
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Mar 2012
    Location
    Toronto Ontario
    Posts
    10

    I See - Should I?

    Quote Originally Posted by Missinglinq View Post
    You usually see this kind of problem when setting a Default Value for a Date/Time Field to Now(), which returns a Date and Time, instead of Date(), which only returns the Date.

    Linq ;0)>
    I See what your saying thank you,
    Should I return change the Now() to Date() then while having pre-existing records?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    run an update query
    sort of the same thing that MikeTheBike suggested
    update mycolumn set mycolumn = int(mycolumn)
    this takes advantage of the way JET stores datetme data. the integer portion is the number of days since an abstract point in tiem, the decimal is the proportion of a day the time represents

    so thats the SQL approach
    if you want to use the query designer
    then select the table + column to update
    select you are using an update query
    in the update box say = int(mydatecolumn)
    where mydatecolumn is whatever your date column is called
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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