highest an dlowest values

[Confused]

Hi all,

I have a quick question for anyone who can help...does
anyone know how to find the min and max value in a field
when designing a query? I have a query that needs a
description field and selling price field with
information displayed on the min and max price.

Any ideas? I need this done by Sun morning!

Thanks!!

 

[Allen Browne]

1. Create a query into your table.

2. Depress the Totals button on toolbar (upper Sigma icon).
Access adds a Total row to the grid.

3. Drag the product field into the grid. Accept Group By in the Total row.

4. Drag the price field into the grid. Choose Min in the Total row.

5. Drag the price field into the grid again. Choose Max in the Total row.

The query groups by product, and returns MinOfPrice and MaxOfPrice.

 

[Confused]

Hi,

Thanks for replying. I tried doing exactly what you
said, but running the query still returns all of my
records...am I missing something?

Thanks.

 

[Duane Hookom]

View your SQL and copy and paste it into a reply in this message thread.

 

[Tom Ellison]

Dear Confused:

You want to see the minimum and maximum of . . . what?

Do you want the miniumum and maximum out of every row in the entire
table? Do you want the minimum and maximum out of each group of rows
with the same description? Or is there some other way you want to
define groups of rows in the table, for each of which you would see
the minimum and maximum?

If you define the group as being description and selling price, and
then show the minimum and maximum selling price out of a set of rows,
all of which have the same selling price, then the minimum and maximum
will be just that selling price.

In other words, it makes no sense to show each and every selling price
and also show the minimum and maximum selling price. Rather, you
would probably need to omit showing the selling price and just show
the description (or some other column or columns that divide the set
of rows into useful groups) and then have the database compare all the
varying selling prices for that group, giving you the minimum and
maximum (and average and a count of how many there are, and any other
desired aggregation) for that set. But if a group means every row
with each given description, then showing each and every selling price
contradicts the idea of showing the minimum and maximum for the group
with each description.

Did this help make sense of it?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Confused]

Hi,

Thanks for replying...here you go...

SELECT Inventory.Description, Min(Inventory.[Selling
Price]) AS [MinOfSelling Price], Max(Inventory.[Selling
Price]) AS [MaxOfSelling Price]
FROM Inventory
GROUP BY Inventory.Description
ORDER BY Min(Inventory.[Selling Price]);

Thanks for the help.

 

[Confused]

Hi,

Thanks for replying. What you said makes sense, but I
just need to show the description and price of the min
and max prices in my table. The query shows all prices
and I'm not sure how to get rid of them...I am probably
making more out of it than I should...and this is only an
assignment for school!lol

Thanks again.

 

[Gary Walter]

and this is only an
assignment for school!

I think I see the problem.
Some of the best minds on this group
are providing a solution to a *real-world
problem.*

Is this what you mean?

ID Description Selling Price
1 A $4.00
2 B $6.00
3 C $8.00
4 D $10.00

and you want

A $4.00
D $10.00

If so, here be one way:

SELECT "MinPrice" As MinMax,
Inventory.Description, Inventory.[Selling Price]
FROM Inventory
WHERE ((Inventory.[Selling Price])=
(SELECT MIN([Selling Price]) FROM Inventory t1))
UNION
SELECT "MaxPrice",
Inventory.Description, Inventory.[Selling Price]
FROM Inventory
WHERE ((Inventory.[Selling Price])=
(SELECT MAX([Selling Price]) FROM Inventory t2));

giving the following from our example data:

MinMax Description Selling Price
MaxPrice D $10.00
MinPrice A $4.00


BTW, "Description" is a reserved word in Access
so remember later when you design dbs to use
something like "ProdDesc" instead. Also, "later,"
you will save some aggravation by not including
spaces in your field names so they do not have to
be bracketed, i.e.,
[Selling Price] ---> SellingPrice

Good luck,

Gary Walter

 

[Confused]

Thanks for the help! You pinpointed what I need
displayed. I'm gonna try your suggestion about how to do
it too.

Thanks.

-----Original Message-----

and this is only an
assignment for school!

I think I see the problem.
Some of the best minds on this group
are providing a solution to a *real-world
problem.*

Is this what you mean?

ID Description Selling Price
1 A $4.00
2 B $6.00
3 C $8.00
4 D $10.00

and you want

A $4.00
D $10.00

If so, here be one way:

SELECT "MinPrice" As MinMax,
Inventory.Description, Inventory.[Selling Price]
FROM Inventory
WHERE ((Inventory.[Selling Price])=
(SELECT MIN([Selling Price]) FROM Inventory t1))
UNION
SELECT "MaxPrice",
Inventory.Description, Inventory.[Selling Price]
FROM Inventory
WHERE ((Inventory.[Selling Price])=
(SELECT MAX([Selling Price]) FROM Inventory t2));

giving the following from our example data:

MinMax Description Selling Price
MaxPrice D $10.00
MinPrice A $4.00


BTW, "Description" is a reserved word in Access
so remember later when you design dbs to use
something like "ProdDesc" instead. Also, "later,"
you will save some aggravation by not including
spaces in your field names so they do not have to
be bracketed, i.e.,
[Selling Price] ---> SellingPrice

Good luck,

Gary Walter


.

 

[Guest]

Ok, that worked great, but in SQL but how do I make it do
that happen from design view (since that is what is
required for the assignment)?...Hope that makes sense. I
hope I'm not posting trivial questions here, the
textbook's website gave us a link to this site...good
idea going back to school this time huh...lol, no regrets
though.

Thanks.

-----Original Message-----

and this is only an
assignment for school!

I think I see the problem.
Some of the best minds on this group
are providing a solution to a *real-world
problem.*

Is this what you mean?

ID Description Selling Price
1 A $4.00
2 B $6.00
3 C $8.00
4 D $10.00

and you want

A $4.00
D $10.00

If so, here be one way:

SELECT "MinPrice" As MinMax,
Inventory.Description, Inventory.[Selling Price]
FROM Inventory
WHERE ((Inventory.[Selling Price])=
(SELECT MIN([Selling Price]) FROM Inventory t1))
UNION
SELECT "MaxPrice",
Inventory.Description, Inventory.[Selling Price]
FROM Inventory
WHERE ((Inventory.[Selling Price])=
(SELECT MAX([Selling Price]) FROM Inventory t2));

giving the following from our example data:

MinMax Description Selling Price
MaxPrice D $10.00
MinPrice A $4.00


BTW, "Description" is a reserved word in Access
so remember later when you design dbs to use
something like "ProdDesc" instead. Also, "later,"
you will save some aggravation by not including
spaces in your field names so they do not have to
be bracketed, i.e.,
[Selling Price] ---> SellingPrice

Good luck,

Gary Walter


.

 

[Tom Ellison]

Dear Confused:

Are all your descriptions different? If you are grouping on
Description, then you should not be seeing the same description twice.
If your descriptions are all different, then what is it you want to
define as a Group? You are going to get a separate group for each
distinct set of values for the field(s) on which you group.

Perhaps you should send a bit of the raw data, the results you're
getting, and the results you expect. Maybe we can figure out what you
mean from that.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Gary Walter]

I guess I flunk.....

In design view....
-Add table Inventory to a new query
-Bring Description and Selling Price down to grid
-Under [Selling Price] in "Criteria" row, type
(SELECT MIN([Selling Price]) FROM Inventory t1)
-Under [Selling Price] in "or" row, type
(SELECT MAX([Selling Price]) FROM Inventory t2)

SELECT Inventory.Description,
Inventory.[Selling Price]
FROM Inventory
WHERE (((Inventory.[Selling Price])=
(SELECT MIN([Selling Price]) FROM Inventory t1)))
OR
(((Inventory.[Selling Price])=
(SELECT MAX([Selling Price]) FROM Inventory t2)));

giving (from our ex data):

Description Selling Price
A $4.00
D $10.00

Ok, that worked great, but in SQL but how do I make it do
that happen from design view (since that is what is
required for the assignment)?...Hope that makes sense. I
hope I'm not posting trivial questions here, the
textbook's website gave us a link to this site...good
idea going back to school this time huh...lol, no regrets
though.

Thanks.

-----Original Message-----

and this is only an
assignment for school!

I think I see the problem.
Some of the best minds on this group
are providing a solution to a *real-world
problem.*

Is this what you mean?

ID Description Selling Price
1 A $4.00
2 B $6.00
3 C $8.00
4 D $10.00

and you want

A $4.00
D $10.00

If so, here be one way:

SELECT "MinPrice" As MinMax,
Inventory.Description, Inventory.[Selling Price]
FROM Inventory
WHERE ((Inventory.[Selling Price])=
(SELECT MIN([Selling Price]) FROM Inventory t1))
UNION
SELECT "MaxPrice",
Inventory.Description, Inventory.[Selling Price]
FROM Inventory
WHERE ((Inventory.[Selling Price])=
(SELECT MAX([Selling Price]) FROM Inventory t2));

giving the following from our example data:

MinMax Description Selling Price
MaxPrice D $10.00
MinPrice A $4.00


BTW, "Description" is a reserved word in Access
so remember later when you design dbs to use
something like "ProdDesc" instead. Also, "later,"
you will save some aggravation by not including
spaces in your field names so they do not have to
be bracketed, i.e.,
[Selling Price] ---> SellingPrice

Good luck,

Gary Walter


.

 

[Gary Walter]

Of course, you could add the "MinMax"
field by typing following in a field row
(all as one line..watch word wrap):
MinMax: IIf([Selling Price]=DMin("[Selling
Price]","Inventory"),"MinPrice","MaxPrice")

so SQL looks like:

SELECT
IIf([Selling Price]=
DMin("[Selling Price]","Inventory"),"MinPrice","MaxPrice") AS MinMax,
Inventory.Description,
Inventory.[Selling Price]
FROM Inventory
WHERE
(((Inventory.[Selling Price])=
(SELECT MIN([Selling Price]) FROM Inventory t1)
Or
(Inventory.[Selling Price])=
(SELECT MAX([Selling Price]) FROM Inventory t2)));

producing..

MinMax Description Selling Price
MinPrice A $4.00
MaxPrice D $10.00

Good luck with school,

Gary Walter

I guess I flunk.....

In design view....
-Add table Inventory to a new query
-Bring Description and Selling Price down to grid
-Under [Selling Price] in "Criteria" row, type
(SELECT MIN([Selling Price]) FROM Inventory t1)
-Under [Selling Price] in "or" row, type
(SELECT MAX([Selling Price]) FROM Inventory t2)

SELECT Inventory.Description,
Inventory.[Selling Price]
FROM Inventory
WHERE (((Inventory.[Selling Price])=
(SELECT MIN([Selling Price]) FROM Inventory t1)))
OR
(((Inventory.[Selling Price])=
(SELECT MAX([Selling Price]) FROM Inventory t2)));

giving (from our ex data):

Description Selling Price
A $4.00
D $10.00

Ok, that worked great, but in SQL but how do I make it do
that happen from design view (since that is what is
required for the assignment)?...Hope that makes sense. I
hope I'm not posting trivial questions here, the
textbook's website gave us a link to this site...good
idea going back to school this time huh...lol, no regrets
though.

Thanks.

-----Original Message-----


and this is only an
assignment for school!

I think I see the problem.
Some of the best minds on this group
are providing a solution to a *real-world
problem.*

Is this what you mean?

ID Description Selling Price
1 A $4.00
2 B $6.00
3 C $8.00
4 D $10.00

and you want

A $4.00
D $10.00

If so, here be one way:

SELECT "MinPrice" As MinMax,
Inventory.Description, Inventory.[Selling Price]
FROM Inventory
WHERE ((Inventory.[Selling Price])=
(SELECT MIN([Selling Price]) FROM Inventory t1))
UNION
SELECT "MaxPrice",
Inventory.Description, Inventory.[Selling Price]
FROM Inventory
WHERE ((Inventory.[Selling Price])=
(SELECT MAX([Selling Price]) FROM Inventory t2));

giving the following from our example data:

MinMax Description Selling Price
MaxPrice D $10.00
MinPrice A $4.00


BTW, "Description" is a reserved word in Access
so remember later when you design dbs to use
something like "ProdDesc" instead. Also, "later,"
you will save some aggravation by not including
spaces in your field names so they do not have to
be bracketed, i.e.,
[Selling Price] ---> SellingPrice

Good luck,

Gary Walter


.

 

[Guest]

You don't flunk! I'll need that answer probably in a few
weeks...lol.

Thanks again for your help...I'm going to give this a
shot.

Thanks.

-----Original Message-----
I guess I flunk.....

In design view....
-Add table Inventory to a new query
-Bring Description and Selling Price down to grid
-Under [Selling Price] in "Criteria" row, type
(SELECT MIN([Selling Price]) FROM Inventory t1)
-Under [Selling Price] in "or" row, type
(SELECT MAX([Selling Price]) FROM Inventory t2)

SELECT Inventory.Description,
Inventory.[Selling Price]
FROM Inventory
WHERE (((Inventory.[Selling Price])=
(SELECT MIN([Selling Price]) FROM Inventory t1)))
OR
(((Inventory.[Selling Price])=
(SELECT MAX([Selling Price]) FROM Inventory t2)));

giving (from our ex data):

Description Selling Price
A $4.00
D $10.00

Ok, that worked great, but in SQL but how do I make it do
that happen from design view (since that is what is
required for the assignment)?...Hope that makes sense. I
hope I'm not posting trivial questions here, the
textbook's website gave us a link to this site...good
idea going back to school this time huh...lol, no regrets
though.

Thanks.

-----Original Message-----


and this is only an
assignment for school!

I think I see the problem.
Some of the best minds on this group
are providing a solution to a *real-world
problem.*

Is this what you mean?

ID Description Selling Price
1 A $4.00
2 B $6.00
3 C $8.00
4 D $10.00

and you want

A $4.00
D $10.00

If so, here be one way:

SELECT "MinPrice" As MinMax,
Inventory.Description, Inventory.[Selling Price]
FROM Inventory
WHERE ((Inventory.[Selling Price])=
(SELECT MIN([Selling Price]) FROM Inventory t1))
UNION
SELECT "MaxPrice",
Inventory.Description, Inventory.[Selling Price]
FROM Inventory
WHERE ((Inventory.[Selling Price])=
(SELECT MAX([Selling Price]) FROM Inventory t2));

giving the following from our example data:

MinMax Description Selling Price
MaxPrice D $10.00
MinPrice A $4.00


BTW, "Description" is a reserved word in Access
so remember later when you design dbs to use
something like "ProdDesc" instead. Also, "later,"
you will save some aggravation by not including
spaces in your field names so they do not have to
be bracketed, i.e.,
[Selling Price] ---> SellingPrice

Good luck,

Gary Walter


.

.

 

[Not confused anymore]

Oh gosh! Thank you so much, it finally worked. My
problem is that I was not typing enough info for the
criteria!

Thank you thank you thank you! )

-----Original Message-----
I guess I flunk.....

In design view....
-Add table Inventory to a new query
-Bring Description and Selling Price down to grid
-Under [Selling Price] in "Criteria" row, type
(SELECT MIN([Selling Price]) FROM Inventory t1)
-Under [Selling Price] in "or" row, type
(SELECT MAX([Selling Price]) FROM Inventory t2)

SELECT Inventory.Description,
Inventory.[Selling Price]
FROM Inventory
WHERE (((Inventory.[Selling Price])=
(SELECT MIN([Selling Price]) FROM Inventory t1)))
OR
(((Inventory.[Selling Price])=
(SELECT MAX([Selling Price]) FROM Inventory t2)));

giving (from our ex data):

Description Selling Price
A $4.00
D $10.00

Ok, that worked great, but in SQL but how do I make it do
that happen from design view (since that is what is
required for the assignment)?...Hope that makes sense. I
hope I'm not posting trivial questions here, the
textbook's website gave us a link to this site...good
idea going back to school this time huh...lol, no regrets
though.

Thanks.

-----Original Message-----


and this is only an
assignment for school!

I think I see the problem.
Some of the best minds on this group
are providing a solution to a *real-world
problem.*

Is this what you mean?

ID Description Selling Price
1 A $4.00
2 B $6.00
3 C $8.00
4 D $10.00

and you want

A $4.00
D $10.00

If so, here be one way:

SELECT "MinPrice" As MinMax,
Inventory.Description, Inventory.[Selling Price]
FROM Inventory
WHERE ((Inventory.[Selling Price])=
(SELECT MIN([Selling Price]) FROM Inventory t1))
UNION
SELECT "MaxPrice",
Inventory.Description, Inventory.[Selling Price]
FROM Inventory
WHERE ((Inventory.[Selling Price])=
(SELECT MAX([Selling Price]) FROM Inventory t2));

giving the following from our example data:

MinMax Description Selling Price
MaxPrice D $10.00
MinPrice A $4.00


BTW, "Description" is a reserved word in Access
so remember later when you design dbs to use
something like "ProdDesc" instead. Also, "later,"
you will save some aggravation by not including
spaces in your field names so they do not have to
be bracketed, i.e.,
[Selling Price] ---> SellingPrice

Good luck,

Gary Walter


.

.

 

[Not confused anymore]

Hi,

Yes, all my descriptions are different. Thanks for the
suggestions. I was just able to produce the correct info
with the help of another post.

Thank you all for helping, the info I am sure will come
in handy again!!!

Not confused anymore )