Dmin and Dmax in query design

S

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.
 
D

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


scratchtrax said:
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.
 
S

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


Duane Hookom said:
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


scratchtrax said:
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.
 
D

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


scratchtrax said:
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


Duane Hookom said:
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


scratchtrax said:
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.
 
S

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


scratchtrax said:
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


Duane Hookom said:
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


scratchtrax said:
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.
 
D

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


scratchtrax said:
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


scratchtrax said:
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


Duane Hookom said:
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.
 
S

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


Duane Hookom said:
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


scratchtrax said:
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


scratchtrax said:
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.
 
D

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


scratchtrax said:
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


Duane Hookom said:
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


scratchtrax said:
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.
 
S

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


Duane Hookom said:
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


scratchtrax said:
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


Duane Hookom said:
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.
 
S

scratchtrax

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

--
http://njgin.aclink.org


scratchtrax said:
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


Duane Hookom said:
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


scratchtrax said:
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.
 
D

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


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

--
http://njgin.aclink.org


scratchtrax said:
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


Duane Hookom said:
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.
 
S

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


Duane Hookom said:
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


scratchtrax said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top