Results 1 to 6 of 6
  1. #1
    HarryRam is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Location
    India
    Posts
    15

    Query for Summing by grouping the value of Other column

    Hi,

    need help in summarizing a query based on the value on other field.

    Attached the database for your reference. database contains:-
    1. tblResponse - the data mainly get stored
    2. tblresponse Query & tblresponseTimequery - contains data from 'tblresponse' & few additional columns of calculation.
    3. QueryStatusTimecountreport - is developed based on the data under tblresponseTimequery

    Column STATUS under tblresponseTimequery contains the types of activity


    Column TIMEDiff calculates the time taken to perform the activity.

    I need to generate query which needs to sum up the time in Timediff column basis on the status under each & every users.
    Example:
    User 1-New 2-Response 3-Closure 5-Reminder
    HariRam sumofTimeDiff sumofTimeDiff sumofTimeDiff sumofTimeDiff
    Assumption sumofTimeDiff sumofTimeDiff sumofTimeDiff sumofTimeDiff

    I tried to generate the query using Dsum function with below calculation
    Dsum("TimeDiff","TblResponsetimequery","Status=""1-New""") with the 'Total' as 'Expression' but shows an error as 'Incorrect data format.....'.

    So, can someone please look into it and advise as how this query can be written to retrieve the information as required.

    Thanks in advance.

    Regards,
    Harry
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The DLookup references a field named Slot in TblTimeslot but there is no such table.

    The TurnTime function returns a string value, not a number. This can cause issues with using this value in subsequent calcs.

    Did you try a CROSSTAB query to get the matrix output?
    TRANSFORM Sum([tblresponse Query].TIMEDIFF) AS SumOfTIMEDIFF
    SELECT [tblresponse Query].AssignedTo
    FROM [tblresponse Query]
    GROUP BY [tblresponse Query].AssignedTo
    PIVOT [tblresponse Query].Status;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    HarryRam is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Location
    India
    Posts
    15
    TblTimeslot has been removed from the below file, as it was not in a reference.

    I was able to derive the requirement thru Crosstab. but I want to get the same output thru the query, as I will require this to merge to another queries of similar calculation.
    Is there any possibilities?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    CROSSTAB is a query. It can be joined to other queries.

    Domain aggregates can perform slowly in queries. However, this expression does work for me. Your version with the doubled quote marks also works. Don't know why it errors for you.

    Total: DSum("TimeDiff","tblResponseTimeQuery","Status='1-New'")


    Here is technique to emulate a CROSSTAB query http://www.datapigtechnologies.com/f.../crosstab.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    HarryRam is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Location
    India
    Posts
    15
    Hi,

    Just tried to execute the query with the same DSUM function, but received the same error msg.
    Refer to the attachment.

    Addition to this, just tried to add a calculated column in tblresponse table, wherein able to get the time difference at the table level itself.
    TimeTaken: IIf(IsNull([AssistanceRequested]),(([EndedTime]-[StartedTime])*1440),(([AssistanceRequestedTime]-[StartedTime])+([EndedTime]-[RestartedTime]))*1440)

    But, when I use the below DSUM against the same, all the column shows the value as 900.
    Seems its totalling up all the Cells, irrespective of what is in the Status column.

    Can you plse review this as well & advise as why the DSUM function output is not as per the expected.
    Attached Thumbnails Attached Thumbnails queryerror.GIF  

  6. #6
    HarryRam is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Location
    India
    Posts
    15
    Hi,
    I just used SUM IIF functions together & were able to retrieve the information as required.
    Sum(IIf([Status] = "1-New",[TimeTaken],0))

    Now I'm able to design the query as per expected.

    Hence subject issue resolved

    Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-05-2014, 10:52 PM
  2. Summing a query column help
    By Eskoraczewski in forum Access
    Replies: 1
    Last Post: 02-01-2014, 04:27 AM
  3. Replies: 1
    Last Post: 02-27-2012, 09:50 AM
  4. Grouping, Summing, and Ranking Problem
    By cadsvc in forum Reports
    Replies: 3
    Last Post: 04-16-2011, 11:34 AM
  5. Summing a column
    By nashr1928 in forum Forms
    Replies: 2
    Last Post: 05-10-2010, 05:42 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums