Results 1 to 7 of 7
  1. #1
    schwachmama is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    7

    Can you use Dmax and the group by clause or dmax with multiple criteria?

    Is there any way to get the max based on two criteria or one criteria and a group by? so for example I have a table with 3 fields, [GroupByVariable], [Conc], and [D_Conc];

    GroupByVariable Conc D_Conc
    Sediment 25 1
    Sediment 30 0
    Sediment 20 1
    Soil 17 1
    Soil 8 0

    I want a query to get the max of field [Conc] based on [D_Conc] = 1 and [GroupByVariable]. It would look something like this...



    GroupByVariable MaxConcBasedOnD_Conc1
    Sediment 25
    Soil 17

    I'd like to do this in one query and also not by crosstab because I want to keep building...like get min/max/avg/count/ for each [D_Conc] 0 and 1 and then a total count for all 0 and 1.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try

    SELECT GroupByVariable, Max(Conc) As MaxConc
    FROM TableName
    WHERE D_Conc = 1
    GROUP BY GroupByVariable

    or if you want both:

    SELECT GroupByVariable, D_Conc, Max(Conc) As MaxConc
    FROM TableName
    GROUP BY GroupByVariable, D_Conc
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    schwachmama is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    7
    Thanks pbaldy but that would work if I only wanted [D_Conc] =1, but what I really want is a bunch of different combos in one query e.g., Count(Conc) when [D_Conc]=0, Count(Conc) when [D_Conc]=1, Min(Conc) when [D_Conc]=1, Max(Conc) when [D_Conc]=1, Min(Conc) when [D_Conc]=0, Max(Conc) when [D_Conc]=0, etc.

    Is there a way to do multiple criteria so that I can have the above as new fields?

    Quote Originally Posted by pbaldy View Post
    Try

    SELECT GroupByVariable, Max(Conc) As MaxConc
    FROM TableName
    WHERE D_Conc = 1
    GROUP BY GroupByVariable

    or if you want both:

    SELECT GroupByVariable, D_Conc, Max(Conc) As MaxConc
    FROM TableName
    GROUP BY GroupByVariable, D_Conc

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you try the second one, adding the other fields?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    schwachmama is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    7
    Yes, a parameter prompt came up...if I type in 1 I get all the [D_Conc] results but i don't want to run the query a couple of times, i want to be able to see the results of [D_Conc] variables (0 and 1) side by side.

    Quote Originally Posted by pbaldy View Post
    Did you try the second one, adding the other fields?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If a parameter prompt came up, something is spelled wrong.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    schwachmama is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    7
    Ok...i don't get the prompt now but its displaying in rows...what I've done previously is your first option but would end up creating 3 queries...1st for when [D_Conc]=1, 2nd when [D_Conc] = 0, and 3rd doing an inner join so that I can display all the results side by side so that I can do some addition calcs comparing [D_Conc] when its 1 and 0. I'd like to do it in one query.

    Quote Originally Posted by schwachmama View Post
    Yes, a parameter prompt came up...if I type in 1 I get all the [D_Conc] results but i don't want to run the query a couple of times, i want to be able to see the results of [D_Conc] variables (0 and 1) side by side.

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

Similar Threads

  1. How to use DMAX
    By tomneedshelp in forum Access
    Replies: 2
    Last Post: 03-01-2012, 07:22 AM
  2. Using DMax within a Subform
    By bigroo in forum Forms
    Replies: 10
    Last Post: 01-17-2012, 10:56 AM
  3. Using Nz and Dmax
    By timmy in forum Programming
    Replies: 5
    Last Post: 07-04-2011, 06:42 AM
  4. DMAX syntax
    By tuyo in forum Programming
    Replies: 1
    Last Post: 03-24-2011, 12:15 AM
  5. DMax Condition
    By Luis Ferreira in forum Forms
    Replies: 3
    Last Post: 11-05-2010, 09:48 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