Results 1 to 15 of 15
  1. #1
    ramimarachli is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    9

    DMax syntax

    Hi everybody,

    To be brief and to the point, I've forms, tables, queries, modules, and reports all making up an application, one of the reports titled Accounts based on a query called accounts which is based on a table called Accounts
    I'm trying to get the sum of the Balance field from table Account where ItemID is the largest value
    I using the following syntax in expression builder but keeps on giving me error:



    = DSum("[Balance]", "Accounts", "[ItemNo] = DMax(ItemNo)")

    any help is highly appreciated

    Thanks in advance

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Do you really want the DSum() function to run a DMax() function for every record in the "Accounts" table/query?

  3. #3
    ramimarachli is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    9
    Quote Originally Posted by RuralGuy View Post
    Do you really want the DSum() function to run a DMax() function for every record in the "Accounts" table/query?

    Thank you for your prompt interaction

    No, not for every record in the table "Accounts", but only for one column which is "Balance" based on another column "ItemNo"

    Thanks

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Can you get this information some other way? DMax(ItemNo)

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You might be able to use: = DSum("[Balance]", "Accounts", "[ItemNo] = " & DMax(ItemNo))

  6. #6
    ramimarachli is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    9
    Quote Originally Posted by RuralGuy View Post
    You might be able to use: = DSum("[Balance]", "Accounts", "[ItemNo] = " & DMax(ItemNo))
    using the syntax you provided resulted in getting the following message:

    The expression you entered has a function containing the wrong number of arguments

    any suggestions

    Thanks

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    How about: = DSum("[Balance]", "Accounts", "[ItemNo] = " & DMax("ItemNo","Accounts"))

  8. #8
    ramimarachli is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    9
    Quote Originally Posted by RuralGuy View Post
    How about: = DSum("[Balance]", "Accounts", "[ItemNo] = " & DMax("ItemNo","Accounts"))
    Thanks RuralGuy it worked perfectly as for the given idea that I was thinking of and which I communicated to you, but I found that I need to add more to the criteria to get what I need. in other words I need to add what can interpreted as sum balance field from Accounts table where the ItemNo is max for each InternalFileNo.

    I hope that I was able to make my idea clear, and would like to thank you very much for your help so far.

    Thanks

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I'm not sure I can help you beyond this point. Sorry. I rarely use the Domain Functions unless there is absolutely no other way.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    As RuralGuy indicated, not very clear what you need. Domain aggregate functions are tricky, as you discovered when you found the filter was not adequate, and can really slow down a query.

    Maybe:
    DSum("[Balance]", "Accounts", "[ItemNo] = " & DMax("ItemNo","Accounts", "InternalFileNo=" & [InternalFileNo]))

    Consider a report using Grouping & Sorting features with aggregate calcs in footers.


    Why do you have a Balance field in table? Aggregate data should not be saved to table. Calculate balance from detail transaction records whenever needed.
    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.

  11. #11
    ramimarachli is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    9
    Quote Originally Posted by June7 View Post
    As RuralGuy indicated, not very clear what you need. Domain aggregate functions are tricky, as you discovered when you found the filter was not adequate, and can really slow down a query.

    Maybe:
    DSum("[Balance]", "Accounts", "[ItemNo] = " & DMax("ItemNo","Accounts", "InternalFileNo=" & [InternalFileNo]))

    Consider a report using Grouping & Sorting features with aggregate calcs in footers.


    Why do you have a Balance field in table? Aggregate data should not be saved to table. Calculate balance from detail transaction records whenever needed.
    The syntax of RuralGuy and even yours are working but I got the sum of the max values from the table for each client. The problem is if I have two entries of some client and three entries of another client the function sums only the values corresponding to ItemNo 2 of the two clients while I need it to sum the value corresponding to the ItemNo 2 of the first client with the value corresponding the ItemNo 3 of the second client and so on.
    Click image for larger version. 

Name:	sample.jpg 
Views:	29 
Size:	75.3 KB 
ID:	13955

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    A picture is worth a thousand words!

    Try:

    SELECT InternalFileNo, Sum(DLookup("Balance", "Accounts", "InternalFileNo=" & [InternalFileNo] & " AND ItemNo=" & DMax("ItemNo", "Accounts", "InternalFileNo=" & [InternalFileNo]))) AS SumMaxItem FROM Accounts GROUP BY InternalFileNo;
    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.

  13. #13
    ramimarachli is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    9
    Quote Originally Posted by June7 View Post
    A picture is worth a thousand words!

    Try:

    SELECT InternalFileNo, Sum(DLookup("Balance", "Accounts", "InternalFileNo=" & [InternalFileNo] & " AND ItemNo=" & DMax("ItemNo", "Accounts", "InternalFileNo=" & [InternalFileNo]))) AS SumMaxItem FROM Accounts GROUP BY InternalFileNo;
    Thank you June7 for your help

    The syntax you suggest triggers the this message

    Click image for larger version. 

Name:	txt.jpg 
Views:	25 
Size:	19.4 KB 
ID:	13958

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I did a test with the example data you posted.

    SELECT Sum(LastItemBal) AS SumLastItemBal FROM (SELECT DISTINCT Accounts.InternalFileNo, DLookUp("Balance","Accounts","InternalFileNo=" & [InternalFileNo] & " AND ItemNo=" & DMax("ItemNo","Accounts","InternalFileNo=" & [InternalFileNo])) AS LastItemBal
    FROM Accounts) AS Q1;

    That query returns a single value which is a grand total of all the last ItemNo Balance for each InternalFileNo.

    This gets the same result:

    SELECT Sum(Balance) AS SumLastBal FROM (SELECT * FROM Accounts WHERE AccountID IN (SELECT TOP 1 AccountID FROM Accounts AS Dupe WHERE Dupe.InternalFileNo=Accounts.InternalFileNo ORDER BY InternalFileNo, Dupe.ItemNo DESC));

    Since the second query doesn't use domain aggregate functions, it is the better option.

    If you want to view detail records that contribute to the Sum, then don't do the outer query. Use the remaining nested query as the data source for a report. Use report Grouping & Sorting features with aggregate calcs in footers. This will allow viewing detail info as well as summary calcs.

    If you want to learn more about subqueries, start with http://allenbrowne.com/subquery-01.html#TopN
    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.

  15. #15
    ramimarachli is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    9
    Quote Originally Posted by June7 View Post
    I did a test with the example data you posted.

    SELECT Sum(LastItemBal) AS SumLastItemBal FROM (SELECT DISTINCT Accounts.InternalFileNo, DLookUp("Balance","Accounts","InternalFileNo=" & [InternalFileNo] & " AND ItemNo=" & DMax("ItemNo","Accounts","InternalFileNo=" & [InternalFileNo])) AS LastItemBal
    FROM Accounts) AS Q1;

    That query returns a single value which is a grand total of all the last ItemNo Balance for each InternalFileNo.

    This gets the same result:

    SELECT Sum(Balance) AS SumLastBal FROM (SELECT * FROM Accounts WHERE AccountID IN (SELECT TOP 1 AccountID FROM Accounts AS Dupe WHERE Dupe.InternalFileNo=Accounts.InternalFileNo ORDER BY InternalFileNo, Dupe.ItemNo DESC));

    Since the second query doesn't use domain aggregate functions, it is the better option.

    If you want to view detail records that contribute to the Sum, then don't do the outer query. Use the remaining nested query as the data source for a report. Use report Grouping & Sorting features with aggregate calcs in footers. This will allow viewing detail info as well as summary calcs.

    If you want to learn more about subqueries, start with http://allenbrowne.com/subquery-01.html#TopN
    Thank you, appreciate your help, problem solved by using the function syntax you provided in subreport in the footer of the main report.

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

Similar Threads

  1. DMAX in VBA
    By Meanfish in forum Access
    Replies: 1
    Last Post: 11-03-2012, 11:06 AM
  2. Replies: 6
    Last Post: 07-24-2012, 03:02 PM
  3. How to use DMAX
    By tomneedshelp in forum Access
    Replies: 2
    Last Post: 03-01-2012, 07:22 AM
  4. Using Nz and Dmax
    By timmy in forum Programming
    Replies: 5
    Last Post: 07-04-2011, 06:42 AM
  5. DMAX syntax
    By tuyo in forum Programming
    Replies: 1
    Last Post: 03-24-2011, 12:15 AM

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