Dmin Vs Dmax in query design

  • Thread starter Thread starter scratchtrax
  • Start date Start date
S

scratchtrax

I am building a query in design view and I am attempting to select records
within a column that exclude the highest and lowest values (prices).

The table is named tblITEMS and I am attempting to use Dmin and Dmax as
criteria to exclude the highest and lowest records. Within design view I
show the ITEM column and the UNITPRICE column. In the criteria for the
UNITPRICE I put:
">DMin("UNITPRICE","tblITEMS")"
This returns all but the lowest values for each ITEM in the table - works
correctly, or as I am hoping that it will.

Now, same scenerio only this time I put: "<DMax("UNITPRICE","tblITEMS")" in
the criteria of UNITPRICE and it will only remove or exclude the highest
value for the whole column (in all of the table) and not the highest value
for each ITEM as it did with Dmin.

I can only assume that I'm supposed to add more to the criteria but I don't
know what exactly as I am hoping to do this for each ITEM category throughout
the table.
Thanks for listening, I'd be grateful for any thoughts regarding these
differences.
 
DMin() and DMax() accept a 3rd argument: the criteria to limit the lookup.
Use this criteria to limit it to the item in the row.

Assuming tblItems.Item is a text field, you concatenate the value of the
Item field into the 3rd argument like this:
DMin("UnitPrice", "tblItems", "Item = """ & [Item] & """")
Click to expand...

If those quotes don't make sense, see:
http://allenbrowne.com/casu-17.html

If Item is a Number type field (not a Text field), use:
DMin("UnitPrice", "tblItems", "Item = " & Nz([Item],0))
Click to expand...

Domain aggregate functions such as DMax() and DMin() tend to be slow to
execute, so it might be more efficient to use a subquery:
http://allenbrowne.com/subquery-01.html
 
Thank you very much for your quick response Allen. I will continue to review
the links you gave me.

I don't understand why I need the 3rd argument for Dmax but not Dmin. When
I use >DMin("UNITPRICE","tblITEMS") I get the results I'm looking for (lowest
price removed for each ITEM) but when I use <DMax("UNITPRICE","tblITEMS") I
do not. For Dmin its takes the min per item. For Dmax it only removes the
highest for the whole column.

I tried using the arguments that you suggested and because there are special
characters in the ITEM column, it does not work (unfortunately).

Thank you for your help.
--
http://njgin.aclink.org


Allen Browne said:
DMin() and DMax() accept a 3rd argument: the criteria to limit the lookup.
Use this criteria to limit it to the item in the row.

Assuming tblItems.Item is a text field, you concatenate the value of the
Item field into the 3rd argument like this:
DMin("UnitPrice", "tblItems", "Item = """ & [Item] & """")
Click to expand...

If those quotes don't make sense, see:
http://allenbrowne.com/casu-17.html

If Item is a Number type field (not a Text field), use:
DMin("UnitPrice", "tblItems", "Item = " & Nz([Item],0))
Click to expand...

Domain aggregate functions such as DMax() and DMin() tend to be slow to
execute, so it might be more efficient to use a subquery:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

scratchtrax said:
I am building a query in design view and I am attempting to select records
within a column that exclude the highest and lowest values (prices).

The table is named tblITEMS and I am attempting to use Dmin and Dmax as
criteria to exclude the highest and lowest records. Within design view I
show the ITEM column and the UNITPRICE column. In the criteria for the
UNITPRICE I put:
">DMin("UNITPRICE","tblITEMS")"
This returns all but the lowest values for each ITEM in the table - works
correctly, or as I am hoping that it will.

Now, same scenerio only this time I put: "<DMax("UNITPRICE","tblITEMS")"
in
the criteria of UNITPRICE and it will only remove or exclude the highest
value for the whole column (in all of the table) and not the highest value
for each ITEM as it did with Dmin.

I can only assume that I'm supposed to add more to the criteria but I
don't
know what exactly as I am hoping to do this for each ITEM category
throughout
the table.
Thanks for listening, I'd be grateful for any thoughts regarding these
differences.
Click to expand...
Click to expand...
 
Both should necessiate using the 3rd argument.

What special characters? If it contains the double-quote, you could try:
DMin("UnitPrice", "tblItems",
Click to expand...
"Item = """ & Replace([Item], """", """""") & """")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

scratchtrax said:
Thank you very much for your quick response Allen. I will continue to
review
the links you gave me.

I don't understand why I need the 3rd argument for Dmax but not Dmin.
When
I use >DMin("UNITPRICE","tblITEMS") I get the results I'm looking for
(lowest
price removed for each ITEM) but when I use <DMax("UNITPRICE","tblITEMS")
I
do not. For Dmin its takes the min per item. For Dmax it only removes
the
highest for the whole column.

I tried using the arguments that you suggested and because there are
special
characters in the ITEM column, it does not work (unfortunately).

Thank you for your help.
--
http://njgin.aclink.org


Allen Browne said:
DMin() and DMax() accept a 3rd argument: the criteria to limit the
lookup.
Use this criteria to limit it to the item in the row.

Assuming tblItems.Item is a text field, you concatenate the value of the
Item field into the 3rd argument like this:
DMin("UnitPrice", "tblItems", "Item = """ & [Item] & """")
Click to expand...

If those quotes don't make sense, see:
http://allenbrowne.com/casu-17.html

If Item is a Number type field (not a Text field), use:
DMin("UnitPrice", "tblItems", "Item = " & Nz([Item],0))
Click to expand...

Domain aggregate functions such as DMax() and DMin() tend to be slow to
execute, so it might be more efficient to use a subquery:
http://allenbrowne.com/subquery-01.html

scratchtrax said:
I am building a query in design view and I am attempting to select
records
within a column that exclude the highest and lowest values (prices).

The table is named tblITEMS and I am attempting to use Dmin and Dmax as
criteria to exclude the highest and lowest records. Within design view
I
show the ITEM column and the UNITPRICE column. In the criteria for the
UNITPRICE I put:
">DMin("UNITPRICE","tblITEMS")"
This returns all but the lowest values for each ITEM in the table -
works
correctly, or as I am hoping that it will.

Now, same scenerio only this time I put:
"<DMax("UNITPRICE","tblITEMS")"
in
the criteria of UNITPRICE and it will only remove or exclude the
highest
value for the whole column (in all of the table) and not the highest
value
for each ITEM as it did with Dmin.

I can only assume that I'm supposed to add more to the criteria but I
don't
know what exactly as I am hoping to do this for each ITEM category
throughout
the table.
Thanks for listening, I'd be grateful for any thoughts regarding these
differences.
Click to expand...
Click to expand...
Click to expand...
 
Allen that was it perfectly, thank you so much.

It is weird though that the "necessary" 3rd argument works while being omitted
50% of the time...isn't it?

Either way, or rather-both ways now, it works wonders and I thank you very
much.


--
http://njgin.aclink.org


Allen Browne said:
Both should necessiate using the 3rd argument.

What special characters? If it contains the double-quote, you could try:
DMin("UnitPrice", "tblItems",
Click to expand...
"Item = """ & Replace([Item], """", """""") & """")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

scratchtrax said:
Thank you very much for your quick response Allen. I will continue to
review
the links you gave me.

I don't understand why I need the 3rd argument for Dmax but not Dmin.
When
I use >DMin("UNITPRICE","tblITEMS") I get the results I'm looking for
(lowest
price removed for each ITEM) but when I use <DMax("UNITPRICE","tblITEMS")
I
do not. For Dmin its takes the min per item. For Dmax it only removes
the
highest for the whole column.

I tried using the arguments that you suggested and because there are
special
characters in the ITEM column, it does not work (unfortunately).

Thank you for your help.
--
http://njgin.aclink.org


Allen Browne said:
DMin() and DMax() accept a 3rd argument: the criteria to limit the
lookup.
Use this criteria to limit it to the item in the row.

Assuming tblItems.Item is a text field, you concatenate the value of the
Item field into the 3rd argument like this:
DMin("UnitPrice", "tblItems", "Item = """ & [Item] & """")

If those quotes don't make sense, see:
http://allenbrowne.com/casu-17.html

If Item is a Number type field (not a Text field), use:
DMin("UnitPrice", "tblItems", "Item = " & Nz([Item],0))

Domain aggregate functions such as DMax() and DMin() tend to be slow to
execute, so it might be more efficient to use a subquery:
http://allenbrowne.com/subquery-01.html

I am building a query in design view and I am attempting to select
records
within a column that exclude the highest and lowest values (prices).

The table is named tblITEMS and I am attempting to use Dmin and Dmax as
criteria to exclude the highest and lowest records. Within design view
I
show the ITEM column and the UNITPRICE column. In the criteria for the
UNITPRICE I put:
">DMin("UNITPRICE","tblITEMS")"
This returns all but the lowest values for each ITEM in the table -
works
correctly, or as I am hoping that it will.

Now, same scenerio only this time I put:
"<DMax("UNITPRICE","tblITEMS")"
in
the criteria of UNITPRICE and it will only remove or exclude the
highest
value for the whole column (in all of the table) and not the highest
value
for each ITEM as it did with Dmin.

I can only assume that I'm supposed to add more to the criteria but I
don't
know what exactly as I am hoping to do this for each ITEM category
throughout
the table.
Thanks for listening, I'd be grateful for any thoughts regarding these
differences.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top