Min/Max as query criteria

[LB79]

Hi all,

Is there a way to used Min or Max in the query criteria?

Thanks

 

[vbaInet]

You can and you need to have Totals on.

 

[vbaInet]

I don't understand your table.

 

[LB79]

Sorry - It all changed during submission - Ill repost in a moment.

 

[vbaInet]

You shouldn't need to delete your post. It will make my reply meaningless.

 

[LB79]

I was actually in the process of deleting it to repost it before you replied but obviously you were too quick for me.
Not quite sure how to post the table. If I paste it in the text box the format is fine but then goes screwy after I submit. I tried to add it as a picture but it seems that I need to upload it to the net first and link it.
Ill repost once I figure out how.

 

[vbaInet]

You can attach a spreadhsheet with the BEFORE and AFTER set of records.

 

[LB79]

Thanks - Just realised I can upload that picture afterall.
In thie table I have 4 records that have some data in common. In my query I want to restrict the data so only the record with the lowest VALUE3 shows. Ive tried using the Min in Total but it still brings up all the data. Where am I going wrong with this?

Thanks

 

[vbaInet]

Are there other records besides ATWSL?

 

[LB79]

Yes with different One/Two combinations. Actually each of those in that picture is a seperate record.

 

[vbaInet]

You can use a subquery or you can do this:

1. Create a new query based on your current query.

2. Put in a new column -->

3Min: DMin("MinOfValue3", "QueryName", "One = '" & One & "' AND Two = '" & Two & "'")

3. Then in the criteria row of MinOfValue3 put:

[3Min]