Dmin and Dmax in query design

[scratchtrax]

I have got a query that I thought was doing what I wanted, but now I'm not
sure.
I am using the following:

<DMax("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """") And

DMin("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &

Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")

I am hoping that this will look up a series of prices, remove the highest
and lowest values and return the remaining numbers.
For example, the group of numbers before using the above expression is:
$300, $300, $300 & $640. It is supposed to be that after removing the
highest and lowest values, that would remove all the numbers from this
series. What I am hoping for here is to get nothing returned as there are
only two different numbers in this group of numbers.
However when I use the above expression I get the following returned: $300,
$300 & $300.
It almost seems like it runs and works with the first part of the expression
but not the second. Is it a syntax error or is the whole thing incorrect?

I previously posted a similar question entitled 'Dmin Vs Dmax in query
design' and Allen Browne was kind enough show me what I needed, thank you
again Allen. I will revisit your previous suggestions and see if I can't
find what is wrong.

 

[Duane Hookom]

Using the Northwind sample database...assuming you want to return the
"middle" orders for each customer based on the Freight. The following SQL
will remove the min and max freight orders for each customer:

SELECT Orders.*
FROM Orders
WHERE (((Orders.Freight)>(SELECT MIN(Freight) FROM Orders O WHERE
O.CustomerID = Orders.CustomerID) And (Orders.Freight)<(SELECT MAX(Freight)
FROM Orders O WHERE O.CustomerID = Orders.CustomerID)))
ORDER BY Orders.CustomerID, Orders.Freight;
--
Duane Hookom
Microsoft Access MVP

I have got a query that I thought was doing what I wanted, but now I'm not
sure.
I am using the following:

<DMax("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """") And

DMin("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &

Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")

I am hoping that this will look up a series of prices, remove the highest
and lowest values and return the remaining numbers.
For example, the group of numbers before using the above expression is:
$300, $300, $300 & $640. It is supposed to be that after removing the
highest and lowest values, that would remove all the numbers from this
series. What I am hoping for here is to get nothing returned as there are
only two different numbers in this group of numbers.
However when I use the above expression I get the following returned: $300,
$300 & $300.
It almost seems like it runs and works with the first part of the expression
but not the second. Is it a syntax error or is the whole thing incorrect?

I previously posted a similar question entitled 'Dmin Vs Dmax in query
design' and Allen Browne was kind enough show me what I needed, thank you
again Allen. I will revisit your previous suggestions and see if I can't
find what is wrong.

 

[scratchtrax]

That's great, thank you Duane.

However, I was hoping to do this within the query design. What would the
expression be if I stayed within the expression builder?


--
http://njgin.aclink.org

Using the Northwind sample database...assuming you want to return the
"middle" orders for each customer based on the Freight. The following SQL
will remove the min and max freight orders for each customer:

SELECT Orders.*
FROM Orders
WHERE (((Orders.Freight)>(SELECT MIN(Freight) FROM Orders O WHERE
O.CustomerID = Orders.CustomerID) And (Orders.Freight)<(SELECT MAX(Freight)
FROM Orders O WHERE O.CustomerID = Orders.CustomerID)))
ORDER BY Orders.CustomerID, Orders.Freight;
--
Duane Hookom
Microsoft Access MVP

I have got a query that I thought was doing what I wanted, but now I'm not
sure.
I am using the following:

<DMax("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """") And

DMin("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &

Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")

I am hoping that this will look up a series of prices, remove the highest
and lowest values and return the remaining numbers.
For example, the group of numbers before using the above expression is:
$300, $300, $300 & $640. It is supposed to be that after removing the
highest and lowest values, that would remove all the numbers from this
series. What I am hoping for here is to get nothing returned as there are
only two different numbers in this group of numbers.
However when I use the above expression I get the following returned: $300,
$300 & $300.
It almost seems like it runs and works with the first part of the expression
but not the second. Is it a syntax error or is the whole thing incorrect?

I previously posted a similar question entitled 'Dmin Vs Dmax in query
design' and Allen Browne was kind enough show me what I needed, thank you
again Allen. I will revisit your previous suggestions and see if I can't
find what is wrong.

 

[Duane Hookom]

The expression builder doesn't build SQL syntax :-( I simply converted your
DMin() and DMax() statements to subqueries.

--
Duane Hookom
Microsoft Access MVP

That's great, thank you Duane.

However, I was hoping to do this within the query design. What would the
expression be if I stayed within the expression builder?


--
http://njgin.aclink.org

Using the Northwind sample database...assuming you want to return the
"middle" orders for each customer based on the Freight. The following SQL
will remove the min and max freight orders for each customer:

SELECT Orders.*
FROM Orders
WHERE (((Orders.Freight)>(SELECT MIN(Freight) FROM Orders O WHERE
O.CustomerID = Orders.CustomerID) And (Orders.Freight)<(SELECT MAX(Freight)
FROM Orders O WHERE O.CustomerID = Orders.CustomerID)))
ORDER BY Orders.CustomerID, Orders.Freight;
--
Duane Hookom
Microsoft Access MVP

I have got a query that I thought was doing what I wanted, but now I'm not
sure.
I am using the following:

<DMax("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """") And
DMin("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")

I am hoping that this will look up a series of prices, remove the highest
and lowest values and return the remaining numbers.
For example, the group of numbers before using the above expression is:
$300, $300, $300 & $640. It is supposed to be that after removing the
highest and lowest values, that would remove all the numbers from this
series. What I am hoping for here is to get nothing returned as there are
only two different numbers in this group of numbers.
However when I use the above expression I get the following returned: $300,
$300 & $300.
It almost seems like it runs and works with the first part of the expression
but not the second. Is it a syntax error or is the whole thing incorrect?

I previously posted a similar question entitled 'Dmin Vs Dmax in query
design' and Allen Browne was kind enough show me what I needed, thank you
again Allen. I will revisit your previous suggestions and see if I can't
find what is wrong.

 

[scratchtrax]

Well Duane I'm trying what you've given me within the expression builder and
I keep getting "Cannot have aggregate function in WHERE clause..."

Does it look like I have a syntax error in the expression that I've posted
earlier?

Thanks again for all of your help.
--
http://njgin.aclink.org

That's great, thank you Duane.

However, I was hoping to do this within the query design. What would the
expression be if I stayed within the expression builder?


--
http://njgin.aclink.org

Using the Northwind sample database...assuming you want to return the
"middle" orders for each customer based on the Freight. The following SQL
will remove the min and max freight orders for each customer:

SELECT Orders.*
FROM Orders
WHERE (((Orders.Freight)>(SELECT MIN(Freight) FROM Orders O WHERE
O.CustomerID = Orders.CustomerID) And (Orders.Freight)<(SELECT MAX(Freight)
FROM Orders O WHERE O.CustomerID = Orders.CustomerID)))
ORDER BY Orders.CustomerID, Orders.Freight;
--
Duane Hookom
Microsoft Access MVP

I have got a query that I thought was doing what I wanted, but now I'm not
sure.
I am using the following:

<DMax("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """") And
DMin("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")

I am hoping that this will look up a series of prices, remove the highest
and lowest values and return the remaining numbers.
For example, the group of numbers before using the above expression is:
$300, $300, $300 & $640. It is supposed to be that after removing the
highest and lowest values, that would remove all the numbers from this
series. What I am hoping for here is to get nothing returned as there are
only two different numbers in this group of numbers.
However when I use the above expression I get the following returned: $300,
$300 & $300.
It almost seems like it runs and works with the first part of the expression
but not the second. Is it a syntax error or is the whole thing incorrect?

I previously posted a similar question entitled 'Dmin Vs Dmax in query
design' and Allen Browne was kind enough show me what I needed, thank you
again Allen. I will revisit your previous suggestions and see if I can't
find what is wrong.

 

[Duane Hookom]

I'm not sure what expression you are referring to. Did you attempt to
implement my suggestion? What is your complete SQL statement?
--
Duane Hookom
Microsoft Access MVP

Well Duane I'm trying what you've given me within the expression builder and
I keep getting "Cannot have aggregate function in WHERE clause..."

Does it look like I have a syntax error in the expression that I've posted
earlier?

Thanks again for all of your help.
--
http://njgin.aclink.org

That's great, thank you Duane.

However, I was hoping to do this within the query design. What would the
expression be if I stayed within the expression builder?


--
http://njgin.aclink.org

Using the Northwind sample database...assuming you want to return the
"middle" orders for each customer based on the Freight. The following SQL
will remove the min and max freight orders for each customer:

SELECT Orders.*
FROM Orders
WHERE (((Orders.Freight)>(SELECT MIN(Freight) FROM Orders O WHERE
O.CustomerID = Orders.CustomerID) And (Orders.Freight)<(SELECT MAX(Freight)
FROM Orders O WHERE O.CustomerID = Orders.CustomerID)))
ORDER BY Orders.CustomerID, Orders.Freight;
--
Duane Hookom
Microsoft Access MVP


:

I have got a query that I thought was doing what I wanted, but now I'm not
sure.
I am using the following:

<DMax("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """") And
DMin("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")

I am hoping that this will look up a series of prices, remove the highest
and lowest values and return the remaining numbers.
For example, the group of numbers before using the above expression is:
$300, $300, $300 & $640. It is supposed to be that after removing the
highest and lowest values, that would remove all the numbers from this
series. What I am hoping for here is to get nothing returned as there are
only two different numbers in this group of numbers.
However when I use the above expression I get the following returned: $300,
$300 & $300.
It almost seems like it runs and works with the first part of the expression
but not the second. Is it a syntax error or is the whole thing incorrect?

I previously posted a similar question entitled 'Dmin Vs Dmax in query
design' and Allen Browne was kind enough show me what I needed, thank you
again Allen. I will revisit your previous suggestions and see if I can't
find what is wrong.

 

[scratchtrax]

This expression: <DMax("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """") And

DMin("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &

Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")

I'd like to stay in the expression builder as I am hoping to have this as
both SQL and an expression builder (design view) query. I would like to use
the SQL statement as part of a vba expression to one button to find the
records. Then the expression builder expression attached to another button
for exporting the found records to Excel. So I am trying to get this as both
an expression builder query and a SQL query.

The full SQL statement looks something like this:

SELECT tblITEMS.ITEMNAMEDESCRIPTION, tblITEMS.UNITPRICE AS [Normalized Price]
FROM tblPROJECTS INNER JOIN tblITEMS ON tblPROJECTS.PROJECTID =
tblITEMS.PROJECTID
WHERE
(((tblITEMS.UNITPRICE)<DMax("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """
& Replace([ITEMNAMEDESCRIPTION],"""","""""") & """") And
(tblITEMS.UNITPRICE)>DMin("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")) AND
((tblPROJECTS.BIDDATE)>=DateAdd("m",-13,Date())) AND
((tblPROJECTS.WORKTYPE)=[Forms]![frmMAINMENU]![cboWorkTypePick]));

I say 'something like this' because I have not implemented this statement
yet. This is just the expression builder statement switched to sql view,
copied and pasted. So, I'm sure there are adjustments that would need to be
made for this to work properly. However, I am just trying to see if in fact
this top query builder statement has any syntax errors, as it seems as if its
only running the first have of the statement. It will remove the highest
value but not the lowest value.
Thank you for following up Duane. I realize this is a pain in the neck and
I do appreciate your time and responses.

--
http://njgin.aclink.org

I'm not sure what expression you are referring to. Did you attempt to
implement my suggestion? What is your complete SQL statement?
--
Duane Hookom
Microsoft Access MVP

Well Duane I'm trying what you've given me within the expression builder and
I keep getting "Cannot have aggregate function in WHERE clause..."

Does it look like I have a syntax error in the expression that I've posted
earlier?

Thanks again for all of your help.
--
http://njgin.aclink.org

That's great, thank you Duane.

However, I was hoping to do this within the query design. What would the
expression be if I stayed within the expression builder?


--
http://njgin.aclink.org


:

Using the Northwind sample database...assuming you want to return the
"middle" orders for each customer based on the Freight. The following SQL
will remove the min and max freight orders for each customer:

SELECT Orders.*
FROM Orders
WHERE (((Orders.Freight)>(SELECT MIN(Freight) FROM Orders O WHERE
O.CustomerID = Orders.CustomerID) And (Orders.Freight)<(SELECT MAX(Freight)
FROM Orders O WHERE O.CustomerID = Orders.CustomerID)))
ORDER BY Orders.CustomerID, Orders.Freight;
--
Duane Hookom
Microsoft Access MVP


:

I have got a query that I thought was doing what I wanted, but now I'm not
sure.
I am using the following:

<DMax("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """") And
DMin("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")

I am hoping that this will look up a series of prices, remove the highest
and lowest values and return the remaining numbers.
For example, the group of numbers before using the above expression is:
$300, $300, $300 & $640. It is supposed to be that after removing the
highest and lowest values, that would remove all the numbers from this
series. What I am hoping for here is to get nothing returned as there are
only two different numbers in this group of numbers.
However when I use the above expression I get the following returned: $300,
$300 & $300.
It almost seems like it runs and works with the first part of the expression
but not the second. Is it a syntax error or is the whole thing incorrect?

I previously posted a similar question entitled 'Dmin Vs Dmax in query
design' and Allen Browne was kind enough show me what I needed, thank you
again Allen. I will revisit your previous suggestions and see if I can't
find what is wrong.

 

[Duane Hookom]

I would use what works. This seems to be the subquery solution that I have
suggested. I'm not sure we mean the same thing when referring to expression
builder. The subquery can't be built in the expression builder.

An alternative is to create a totals query
SELECT ItemNameDescription,
Min(UnitPrice) as MinPrice,
Max(UnitPrice) as MaxPrice
FROM tblItems
GROUP BY ItemNameDescription;

Then join this query into your main query and set the criteria under the
UnitPrice field to:

MinPrice and <MaxPrice

--
Duane Hookom
Microsoft Access MVP

This expression: <DMax("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """") And

DMin("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &

Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")

I'd like to stay in the expression builder as I am hoping to have this as
both SQL and an expression builder (design view) query. I would like to use
the SQL statement as part of a vba expression to one button to find the
records. Then the expression builder expression attached to another button
for exporting the found records to Excel. So I am trying to get this as both
an expression builder query and a SQL query.

The full SQL statement looks something like this:

SELECT tblITEMS.ITEMNAMEDESCRIPTION, tblITEMS.UNITPRICE AS [Normalized Price]
FROM tblPROJECTS INNER JOIN tblITEMS ON tblPROJECTS.PROJECTID =
tblITEMS.PROJECTID
WHERE
(((tblITEMS.UNITPRICE)<DMax("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """
& Replace([ITEMNAMEDESCRIPTION],"""","""""") & """") And
(tblITEMS.UNITPRICE)>DMin("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")) AND
((tblPROJECTS.BIDDATE)>=DateAdd("m",-13,Date())) AND
((tblPROJECTS.WORKTYPE)=[Forms]![frmMAINMENU]![cboWorkTypePick]));

I say 'something like this' because I have not implemented this statement
yet. This is just the expression builder statement switched to sql view,
copied and pasted. So, I'm sure there are adjustments that would need to be
made for this to work properly. However, I am just trying to see if in fact
this top query builder statement has any syntax errors, as it seems as if its
only running the first have of the statement. It will remove the highest
value but not the lowest value.
Thank you for following up Duane. I realize this is a pain in the neck and
I do appreciate your time and responses.

--
http://njgin.aclink.org

I'm not sure what expression you are referring to. Did you attempt to
implement my suggestion? What is your complete SQL statement?
--
Duane Hookom
Microsoft Access MVP

Well Duane I'm trying what you've given me within the expression builder and
I keep getting "Cannot have aggregate function in WHERE clause..."

Does it look like I have a syntax error in the expression that I've posted
earlier?

Thanks again for all of your help.
--
http://njgin.aclink.org


:

That's great, thank you Duane.

However, I was hoping to do this within the query design. What would the
expression be if I stayed within the expression builder?


--
http://njgin.aclink.org


:

Using the Northwind sample database...assuming you want to return the
"middle" orders for each customer based on the Freight. The following SQL
will remove the min and max freight orders for each customer:

SELECT Orders.*
FROM Orders
WHERE (((Orders.Freight)>(SELECT MIN(Freight) FROM Orders O WHERE
O.CustomerID = Orders.CustomerID) And (Orders.Freight)<(SELECT MAX(Freight)
FROM Orders O WHERE O.CustomerID = Orders.CustomerID)))
ORDER BY Orders.CustomerID, Orders.Freight;
--
Duane Hookom
Microsoft Access MVP


:

I have got a query that I thought was doing what I wanted, but now I'm not
sure.
I am using the following:

<DMax("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """") And
DMin("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")

I am hoping that this will look up a series of prices, remove the highest
and lowest values and return the remaining numbers.
For example, the group of numbers before using the above expression is:
$300, $300, $300 & $640. It is supposed to be that after removing the
highest and lowest values, that would remove all the numbers from this
series. What I am hoping for here is to get nothing returned as there are
only two different numbers in this group of numbers.
However when I use the above expression I get the following returned: $300,
$300 & $300.
It almost seems like it runs and works with the first part of the expression
but not the second. Is it a syntax error or is the whole thing incorrect?

I previously posted a similar question entitled 'Dmin Vs Dmax in query
design' and Allen Browne was kind enough show me what I needed, thank you
again Allen. I will revisit your previous suggestions and see if I can't
find what is wrong.

 

[scratchtrax]

I'm sorry I'm not being clear, your suggestions so far I'm sure will work and
I am grateful for them, thank you very much.

For now I'm trying to figure out why, when I'm in the expression builder and
I use this statement: "<DMax("UnitPrice","tblItems","ITEMNAMEDESCRIPTION =
""" & Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")" -the results are as
I expect them, But when I use this statement:
"DMin("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")" -the results are not as I
expect them, as there is a lowest value that remains. I'm trying to figure
if its syntax, or am I using 'Dmin' incorrectly, or is it working and the
correct results are not as I expect them. Thanks again.

--
http://njgin.aclink.org

I would use what works. This seems to be the subquery solution that I have
suggested. I'm not sure we mean the same thing when referring to expression
builder. The subquery can't be built in the expression builder.

An alternative is to create a totals query
SELECT ItemNameDescription,
Min(UnitPrice) as MinPrice,
Max(UnitPrice) as MaxPrice
FROM tblItems
GROUP BY ItemNameDescription;

Then join this query into your main query and set the criteria under the
UnitPrice field to:

MinPrice and <MaxPrice

--
Duane Hookom
Microsoft Access MVP

This expression: <DMax("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """") And

DMin("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &

Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")

I'd like to stay in the expression builder as I am hoping to have this as
both SQL and an expression builder (design view) query. I would like to use
the SQL statement as part of a vba expression to one button to find the
records. Then the expression builder expression attached to another button
for exporting the found records to Excel. So I am trying to get this as both
an expression builder query and a SQL query.

The full SQL statement looks something like this:

SELECT tblITEMS.ITEMNAMEDESCRIPTION, tblITEMS.UNITPRICE AS [Normalized Price]
FROM tblPROJECTS INNER JOIN tblITEMS ON tblPROJECTS.PROJECTID =
tblITEMS.PROJECTID
WHERE
(((tblITEMS.UNITPRICE)<DMax("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """
& Replace([ITEMNAMEDESCRIPTION],"""","""""") & """") And
(tblITEMS.UNITPRICE)>DMin("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")) AND
((tblPROJECTS.BIDDATE)>=DateAdd("m",-13,Date())) AND
((tblPROJECTS.WORKTYPE)=[Forms]![frmMAINMENU]![cboWorkTypePick]));

I say 'something like this' because I have not implemented this statement
yet. This is just the expression builder statement switched to sql view,
copied and pasted. So, I'm sure there are adjustments that would need to be
made for this to work properly. However, I am just trying to see if in fact
this top query builder statement has any syntax errors, as it seems as if its
only running the first have of the statement. It will remove the highest
value but not the lowest value.
Thank you for following up Duane. I realize this is a pain in the neck and
I do appreciate your time and responses.

--
http://njgin.aclink.org

I'm not sure what expression you are referring to. Did you attempt to
implement my suggestion? What is your complete SQL statement?
--
Duane Hookom
Microsoft Access MVP


:

Well Duane I'm trying what you've given me within the expression builder and
I keep getting "Cannot have aggregate function in WHERE clause..."

Does it look like I have a syntax error in the expression that I've posted
earlier?

Thanks again for all of your help.
--
http://njgin.aclink.org


:

That's great, thank you Duane.

However, I was hoping to do this within the query design. What would the
expression be if I stayed within the expression builder?


--
http://njgin.aclink.org


:

Using the Northwind sample database...assuming you want to return the
"middle" orders for each customer based on the Freight. The following SQL
will remove the min and max freight orders for each customer:

SELECT Orders.*
FROM Orders
WHERE (((Orders.Freight)>(SELECT MIN(Freight) FROM Orders O WHERE
O.CustomerID = Orders.CustomerID) And (Orders.Freight)<(SELECT MAX(Freight)
FROM Orders O WHERE O.CustomerID = Orders.CustomerID)))
ORDER BY Orders.CustomerID, Orders.Freight;
--
Duane Hookom
Microsoft Access MVP


:

I have got a query that I thought was doing what I wanted, but now I'm not
sure.
I am using the following:

<DMax("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """") And
DMin("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")

I am hoping that this will look up a series of prices, remove the highest
and lowest values and return the remaining numbers.
For example, the group of numbers before using the above expression is:
$300, $300, $300 & $640. It is supposed to be that after removing the
highest and lowest values, that would remove all the numbers from this
series. What I am hoping for here is to get nothing returned as there are
only two different numbers in this group of numbers.
However when I use the above expression I get the following returned: $300,
$300 & $300.
It almost seems like it runs and works with the first part of the expression
but not the second. Is it a syntax error or is the whole thing incorrect?

I previously posted a similar question entitled 'Dmin Vs Dmax in query
design' and Allen Browne was kind enough show me what I needed, thank you
again Allen. I will revisit your previous suggestions and see if I can't
find what is wrong.

 

[scratchtrax]

Maybe a better question is: How can you use a subquery to export the found
records to Excel?

--
http://njgin.aclink.org

I'm sorry I'm not being clear, your suggestions so far I'm sure will work and
I am grateful for them, thank you very much.

For now I'm trying to figure out why, when I'm in the expression builder and
I use this statement: "<DMax("UnitPrice","tblItems","ITEMNAMEDESCRIPTION =
""" & Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")" -the results are as
I expect them, But when I use this statement:
"DMin("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")" -the results are not as I
expect them, as there is a lowest value that remains. I'm trying to figure
if its syntax, or am I using 'Dmin' incorrectly, or is it working and the
correct results are not as I expect them. Thanks again.

--
http://njgin.aclink.org

I would use what works. This seems to be the subquery solution that I have
suggested. I'm not sure we mean the same thing when referring to expression
builder. The subquery can't be built in the expression builder.

An alternative is to create a totals query
SELECT ItemNameDescription,
Min(UnitPrice) as MinPrice,
Max(UnitPrice) as MaxPrice
FROM tblItems
GROUP BY ItemNameDescription;

Then join this query into your main query and set the criteria under the
UnitPrice field to:

MinPrice and <MaxPrice

--
Duane Hookom
Microsoft Access MVP

This expression: <DMax("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """") And
DMin("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")

I'd like to stay in the expression builder as I am hoping to have this as
both SQL and an expression builder (design view) query. I would like to use
the SQL statement as part of a vba expression to one button to find the
records. Then the expression builder expression attached to another button
for exporting the found records to Excel. So I am trying to get this as both
an expression builder query and a SQL query.

The full SQL statement looks something like this:

SELECT tblITEMS.ITEMNAMEDESCRIPTION, tblITEMS.UNITPRICE AS [Normalized Price]
FROM tblPROJECTS INNER JOIN tblITEMS ON tblPROJECTS.PROJECTID =
tblITEMS.PROJECTID
WHERE
(((tblITEMS.UNITPRICE)<DMax("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """
& Replace([ITEMNAMEDESCRIPTION],"""","""""") & """") And
(tblITEMS.UNITPRICE)>DMin("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")) AND
((tblPROJECTS.BIDDATE)>=DateAdd("m",-13,Date())) AND
((tblPROJECTS.WORKTYPE)=[Forms]![frmMAINMENU]![cboWorkTypePick]));

I say 'something like this' because I have not implemented this statement
yet. This is just the expression builder statement switched to sql view,
copied and pasted. So, I'm sure there are adjustments that would need to be
made for this to work properly. However, I am just trying to see if in fact
this top query builder statement has any syntax errors, as it seems as if its
only running the first have of the statement. It will remove the highest
value but not the lowest value.
Thank you for following up Duane. I realize this is a pain in the neck and
I do appreciate your time and responses.

--
http://njgin.aclink.org


:

I'm not sure what expression you are referring to. Did you attempt to
implement my suggestion? What is your complete SQL statement?
--
Duane Hookom
Microsoft Access MVP


:

Well Duane I'm trying what you've given me within the expression builder and
I keep getting "Cannot have aggregate function in WHERE clause..."

Does it look like I have a syntax error in the expression that I've posted
earlier?

Thanks again for all of your help.
--
http://njgin.aclink.org


:

That's great, thank you Duane.

However, I was hoping to do this within the query design. What would the
expression be if I stayed within the expression builder?


--
http://njgin.aclink.org


:

Using the Northwind sample database...assuming you want to return the
"middle" orders for each customer based on the Freight. The following SQL
will remove the min and max freight orders for each customer:

SELECT Orders.*
FROM Orders
WHERE (((Orders.Freight)>(SELECT MIN(Freight) FROM Orders O WHERE
O.CustomerID = Orders.CustomerID) And (Orders.Freight)<(SELECT MAX(Freight)
FROM Orders O WHERE O.CustomerID = Orders.CustomerID)))
ORDER BY Orders.CustomerID, Orders.Freight;
--
Duane Hookom
Microsoft Access MVP


:

I have got a query that I thought was doing what I wanted, but now I'm not
sure.
I am using the following:

<DMax("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """") And
DMin("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")

I am hoping that this will look up a series of prices, remove the highest
and lowest values and return the remaining numbers.
For example, the group of numbers before using the above expression is:
$300, $300, $300 & $640. It is supposed to be that after removing the
highest and lowest values, that would remove all the numbers from this
series. What I am hoping for here is to get nothing returned as there are
only two different numbers in this group of numbers.
However when I use the above expression I get the following returned: $300,
$300 & $300.
It almost seems like it runs and works with the first part of the expression
but not the second. Is it a syntax error or is the whole thing incorrect?

I previously posted a similar question entitled 'Dmin Vs Dmax in query
design' and Allen Browne was kind enough show me what I needed, thank you
again Allen. I will revisit your previous suggestions and see if I can't
find what is wrong.

 

[Duane Hookom]

I just took my sample using the Orders table from Northwind and select
Tools=>Office Links.... to push the results of the query to Excel.
--
Duane Hookom
Microsoft Access MVP

Maybe a better question is: How can you use a subquery to export the found
records to Excel?

--
http://njgin.aclink.org

I'm sorry I'm not being clear, your suggestions so far I'm sure will work and
I am grateful for them, thank you very much.

For now I'm trying to figure out why, when I'm in the expression builder and
I use this statement: "<DMax("UnitPrice","tblItems","ITEMNAMEDESCRIPTION =
""" & Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")" -the results are as
I expect them, But when I use this statement:
"DMin("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")" -the results are not as I
expect them, as there is a lowest value that remains. I'm trying to figure
if its syntax, or am I using 'Dmin' incorrectly, or is it working and the
correct results are not as I expect them. Thanks again.

--
http://njgin.aclink.org

I would use what works. This seems to be the subquery solution that I have
suggested. I'm not sure we mean the same thing when referring to expression
builder. The subquery can't be built in the expression builder.

An alternative is to create a totals query
SELECT ItemNameDescription,
Min(UnitPrice) as MinPrice,
Max(UnitPrice) as MaxPrice
FROM tblItems
GROUP BY ItemNameDescription;

Then join this query into your main query and set the criteria under the
UnitPrice field to:
MinPrice and <MaxPrice

--
Duane Hookom
Microsoft Access MVP


:

This expression: <DMax("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """") And
DMin("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")

I'd like to stay in the expression builder as I am hoping to have this as
both SQL and an expression builder (design view) query. I would like to use
the SQL statement as part of a vba expression to one button to find the
records. Then the expression builder expression attached to another button
for exporting the found records to Excel. So I am trying to get this as both
an expression builder query and a SQL query.

The full SQL statement looks something like this:

SELECT tblITEMS.ITEMNAMEDESCRIPTION, tblITEMS.UNITPRICE AS [Normalized Price]
FROM tblPROJECTS INNER JOIN tblITEMS ON tblPROJECTS.PROJECTID =
tblITEMS.PROJECTID
WHERE
(((tblITEMS.UNITPRICE)<DMax("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """
& Replace([ITEMNAMEDESCRIPTION],"""","""""") & """") And
(tblITEMS.UNITPRICE)>DMin("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")) AND
((tblPROJECTS.BIDDATE)>=DateAdd("m",-13,Date())) AND
((tblPROJECTS.WORKTYPE)=[Forms]![frmMAINMENU]![cboWorkTypePick]));

I say 'something like this' because I have not implemented this statement
yet. This is just the expression builder statement switched to sql view,
copied and pasted. So, I'm sure there are adjustments that would need to be
made for this to work properly. However, I am just trying to see if in fact
this top query builder statement has any syntax errors, as it seems as if its
only running the first have of the statement. It will remove the highest
value but not the lowest value.
Thank you for following up Duane. I realize this is a pain in the neck and
I do appreciate your time and responses.

--
http://njgin.aclink.org


:

I'm not sure what expression you are referring to. Did you attempt to
implement my suggestion? What is your complete SQL statement?
--
Duane Hookom
Microsoft Access MVP


:

Well Duane I'm trying what you've given me within the expression builder and
I keep getting "Cannot have aggregate function in WHERE clause..."

Does it look like I have a syntax error in the expression that I've posted
earlier?

Thanks again for all of your help.
--
http://njgin.aclink.org


:

That's great, thank you Duane.

However, I was hoping to do this within the query design. What would the
expression be if I stayed within the expression builder?


--
http://njgin.aclink.org


:

Using the Northwind sample database...assuming you want to return the
"middle" orders for each customer based on the Freight. The following SQL
will remove the min and max freight orders for each customer:

SELECT Orders.*
FROM Orders
WHERE (((Orders.Freight)>(SELECT MIN(Freight) FROM Orders O WHERE
O.CustomerID = Orders.CustomerID) And (Orders.Freight)<(SELECT MAX(Freight)
FROM Orders O WHERE O.CustomerID = Orders.CustomerID)))
ORDER BY Orders.CustomerID, Orders.Freight;
--
Duane Hookom
Microsoft Access MVP


:

I have got a query that I thought was doing what I wanted, but now I'm not
sure.
I am using the following:

<DMax("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """") And
DMin("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")

I am hoping that this will look up a series of prices, remove the highest
and lowest values and return the remaining numbers.
For example, the group of numbers before using the above expression is:
$300, $300, $300 & $640. It is supposed to be that after removing the
highest and lowest values, that would remove all the numbers from this
series. What I am hoping for here is to get nothing returned as there are
only two different numbers in this group of numbers.
However when I use the above expression I get the following returned: $300,
$300 & $300.
It almost seems like it runs and works with the first part of the expression
but not the second. Is it a syntax error or is the whole thing incorrect?

I previously posted a similar question entitled 'Dmin Vs Dmax in query
design' and Allen Browne was kind enough show me what I needed, thank you
again Allen. I will revisit your previous suggestions and see if I can't
find what is wrong.

 

[scratchtrax]

If I get the SQL typed correctly it works great, thank you. I then can save
that as an object and reffer to it for exporting (I think, I haven't tried
yet, but I did just get this to work. If it weren't for my fat fingers it
would have worked when you first suggested it). Northwind, of course.
Thanks
--
http://njgin.aclink.org

Using the Northwind sample database...assuming you want to return the
"middle" orders for each customer based on the Freight. The following SQL
will remove the min and max freight orders for each customer:

SELECT Orders.*
FROM Orders
WHERE (((Orders.Freight)>(SELECT MIN(Freight) FROM Orders O WHERE
O.CustomerID = Orders.CustomerID) And (Orders.Freight)<(SELECT MAX(Freight)
FROM Orders O WHERE O.CustomerID = Orders.CustomerID)))
ORDER BY Orders.CustomerID, Orders.Freight;
--
Duane Hookom
Microsoft Access MVP

I have got a query that I thought was doing what I wanted, but now I'm not
sure.
I am using the following:

<DMax("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &
Replace([ITEMNAMEDESCRIPTION],"""","""""") & """") And

DMin("UnitPrice","tblItems","ITEMNAMEDESCRIPTION = """ &

Replace([ITEMNAMEDESCRIPTION],"""","""""") & """")

I am hoping that this will look up a series of prices, remove the highest
and lowest values and return the remaining numbers.
For example, the group of numbers before using the above expression is:
$300, $300, $300 & $640. It is supposed to be that after removing the
highest and lowest values, that would remove all the numbers from this
series. What I am hoping for here is to get nothing returned as there are
only two different numbers in this group of numbers.
However when I use the above expression I get the following returned: $300,
$300 & $300.
It almost seems like it runs and works with the first part of the expression
but not the second. Is it a syntax error or is the whole thing incorrect?

I previously posted a similar question entitled 'Dmin Vs Dmax in query
design' and Allen Browne was kind enough show me what I needed, thank you
again Allen. I will revisit your previous suggestions and see if I can't
find what is wrong.