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

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote