Help with DMin?

[Kevin C Niven]

I am trying to use a DMin function in what should be a simple query,
and I think I just don't get it. My brain is mush at this point, and
I wonder if someone could give me some guidance.

I have a Query that is like this:

MyQuery
guy date thing
Fred 2/7/2010 ABC
Fred 7/2/2010 ABC
Barney 1/1/2011 ABC
Barney 2/3/2008 ABC
Wilma 1/4/2010 ABC
Wilma 1/4/2010 ABC
Barney 8/5/2009 XYZ
Fred 3/9/2008 XYZ
Fred 9/8/2011 XYZ
Wilma 3/2/2009 XYZ

I wish to do another query on the above query that returns this:

MyNewQuery
guy date thing
Barney 2/3/2008 ABC
Fred 3/9/2008 XYZ

Note that what has happened is that there are are two sets of "things"
in the above MyQuery. There are 6 ABCs and 4 XYZs. For each set of
things, I want to get the row with the oldest date. To belabor the
point, there could be, say, 250 sets, in which case, MyNewQuery would
return 250 lines with the oldest date in each set.

How do I write a query to do this? Thank you very much in advance for
any help.

A wrinkle is that I'm not sure what to do if there is a tie for oldest
date. I guess in that case, I'd return the alphanumerically first
"guy". Can you tell me how to do this as well?

Kevin

 

[John W. Vinson]

I am trying to use a DMin function in what should be a simple query,
and I think I just don't get it. My brain is mush at this point, and
I wonder if someone could give me some guidance.

I have a Query that is like this:

MyQuery
guy date thing
Fred 2/7/2010 ABC
Fred 7/2/2010 ABC
Barney 1/1/2011 ABC
Barney 2/3/2008 ABC
Wilma 1/4/2010 ABC
Wilma 1/4/2010 ABC
Barney 8/5/2009 XYZ
Fred 3/9/2008 XYZ
Fred 9/8/2011 XYZ
Wilma 3/2/2009 XYZ

I wish to do another query on the above query that returns this:

MyNewQuery
guy date thing
Barney 2/3/2008 ABC
Fred 3/9/2008 XYZ

Note that what has happened is that there are are two sets of "things"
in the above MyQuery. There are 6 ABCs and 4 XYZs. For each set of
things, I want to get the row with the oldest date. To belabor the
point, there could be, say, 250 sets, in which case, MyNewQuery would
return 250 lines with the oldest date in each set.

How do I write a query to do this? Thank you very much in advance for
any help.

A wrinkle is that I'm not sure what to do if there is a tie for oldest
date. I guess in that case, I'd return the alphanumerically first
"guy". Can you tell me how to do this as well?

Kevin

You don't actually need the DMin() function - a subquery will probably be
faster. Create a query based on the table, and put

=(SELECT Min([date]) FROM tablename AS X WHERE X.[thing] = yourtable.[thing])

This will give you all the ties for each Thing; to see only one, make it a
Totals query, group by Thing and select First for the other fields.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Kevin C Niven]

My sincere thanks to both Marsh and John for their replies. I just
tried this and it works beautifully. Thank you! The DMin churning
was practically crashing my machine.


Best regards,
Kevin