Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Use Dmin (or other aggregate functions) on subset of records within query

    In a query each record includes "BatchID" , "Month", "year". There are a number of records for each BatchID, what I want to do is add a field to each record showing the min year in that batch. I have tried everything I can think of or so far find on the net with out success. My starting point was;

    Dmin("Year","tblRevonlySelYear","BatchID='" & BatchID & "'")

    That returns an error.

    I think I could build a query that returned the min year for each batch then use that to build an update query to put the min year into each record in the first query, that seems very cumbersome and I was hoping there may be a more efficient way.

    thanks for any suggestion.

    Peter

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    What is the end result going to be - a saved set of values in a table, a report, or a display on a form? It seems to me you should be able to do a GroupBy query that gives you the minimum year for each BatchID, and then join that to your query based on BatchID and include the result from the GroupBy query, so it would be two relatively simple queries. Unless you are planning to save the data permanently in a table, I don't see a need for an update query.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    I also need to sort by month as well and in fact I needed Dmax rather than Dmin. Many thanks for the simple suggestion Occam's Razor got me this time. Using the month in the second query does not work as the ID's do not stay together so back to the drawing board.
    Last edited by mitchbvi; 2014-11-24 at 12:24.

  4. #4
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    I had posted a reply and went to edit it and seem to loose the lot. First thank for taking the time to respond to my post unfortunately I feel I did not outline properly what the problem is.

    The query is part of the routine to produce a report and what I want to do is have the report listed so it shows each month sequentially. The difficulty is that within each batch there are sometimes adjustments to prior months and years and infrequently there are two batches with the same last month.I have attached a PDF which shows data and the desired result, there is of course a other information but I thought this way kept it simple.

    Thanks
    Attached Files Attached Files
    Last edited by mitchbvi; 2014-11-24 at 13:41. Reason: spelling

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    You shouldn't be using an aggregate function in a query. It is very inefficient.

    The best way is to create query which just returns the min year for that batch:
    SELECT BatchID, Min([Year]) AS MinOfYear FROM tblRevonlySelYear GROUP BY BatchID

    Name this anything you want: qryBatchMinYear for example. You can then join to this query in your SQL statement.

    BTW, note I used brackets around Year; I did this just in case, since Year is an Access function. Just be be safe, you generally want to avoid using such Access reserved words as field names; in this case, I'd have used BatchYear instead.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. The Following User Says Thank You to MarkLiquorman For This Useful Post:

    mitchbvi (2014-12-01)

  7. #6
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Hi Mark

    first my apologies for not replying to your post but my internet has been down for two days.

    Secondly I discovered that I need Max rather than Min, I had started out asking for Min thinking if i solved that Max would work.

    I have managed to solve my problem but I had to use an aggregate function in a query. Once I had established what batches had to be processed I concatenated Year, Month (padded if one digit) and BatchID, used grouping to get the Max and then in the report sorted by the concatenated field then year and Month which put all the entries in the correct order.

    My DB is not that large about 40K entries and in any year about 4 to 5K so I guess the efficiency is not that critical. However I would prefer to follow your advice but cannot work out how to do it.
    This is the sql statement I get when I try and follow your suggestion.

    SELECT tblIncome_Expenditure.BatchID, tblIncome_Expenditure.Year, Max(tblRevOnlySelYear.Year)
    AS MaxOfYear
    FROM tblRevOnlySelYear INNER JOIN tblIncome_Expenditure
    ON tblRevOnlySelYear.BatchID = tblIncome_Expenditure.BatchID
    GROUP BY tblIncome_Expenditure.BatchID, tblIncome_Expenditure.Year;

    This does not give me the correct result.

    Again my thanks for your time.

    Peter

  8. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Look again at what I first wrote. You had to create another query which just returned the ID# and Min value (or Max value if that is what you needed). You then join that query to the original table on ID# and WHERE the dates are the same.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. The Following User Says Thank You to MarkLiquorman For This Useful Post:

    mitchbvi (2014-12-02)

  10. #8
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Thanks Mark , I am dense I know as I am still missing something but at least problem for now is solved and I will work on your suggestion.

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
  •