max function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database that contains activities for assets. The field I am query
are barcode, transaction date and Indexid. I would like to know where the
barcode is located at a certain point in time. If I group by barcode and use
max for the transactions date, how can I identify the associated indexid?
 
Unfortunately, this is not a simple problem. You must either create and
save your aggregate query (the one with the Max function), then join that
back to the original table OR use a subquery. It's a little hard to explain
here, especially when I don't have any table or field names to work with.

But on my website (www.rogersaccesslibrary.com) is a small Access Sample
database called "MaxQueryProblem.mdb", which explains the problem and
demostrates both solutions.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Thank you for your response. I visited your website and understand. In the
examples given, there were only two field in the subquery. I am looking for
a query that will render the index number for the barcode with the latest
date.

Table Fields Duplicated allowed
Barcode Yes
Transaction Date Yes
EventId No

The following is my query, but doesn't this assume that the items were
entered in squential order?

SELECT qryEvent.fldBarcode, Max(qryEvent.fldEventID) AS MaxOffldEventID
FROM qryEvent
GROUP BY qryEvent.fldBarcode
ORDER BY qryEvent.fldBarcode;

This query is linked to the original table. Unfortunately, the unique
identifier is the EventId field.

Am I over thinking this?
 
Sorry for the delay in responding, I was unavailable Thursday and Friday.

As I see it, you want the Maximum DATE, so your first query should be
something like:
SELECT qryEvent.fldBarcode, Max(qryEvent.[Transaction Date]) AS
MaxOfTransactionDate
FROM qryEvent
GROUP BY qryEvent.fldBarcode

Now save this (let's call it qryMaxBarcode) and create a new query, joining
this query back with the original table. You'll want to join it on:
qryMaxBarcode.fldBarcode <--> qryEvent.fldBarcode
qryMaxBarcode.MaxOfTransactionDate<--> qryEvent.[Transaction Date]


--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Back
Top