Order by Price, using three different fields

[Dodsey77]

Hi there,

I have a Retro Systems database showing old computers / consoles, and all
games and accessories associated with them.

The details on each game and accessory include 3 different prices: low
range, high range, and top price. (low range and high range being the general
sale prices for the game / accessory).

i.e. A game could have a general price of £8 - £15, but with a top price of
£30 for it in especially good condition.

I want to output a report with games / accessories in decreasing order of
price, so the highest is at the top. However, I would like it to take into
account all of the prices. For instance, something with a top price of £30
would come higher than something with a high range price of £20, but come
lower than something with a low range price of £40.

Can anyone help me write the query, so that it will check all three fields?

Thanks in advance,

 

[ChrisM]

Hi,

I don't fully understand your sorting logic, but if you have a calculated
field which (for example) takes the highest of the three prices. You can
then sort on that calculated field....
You can vary the way the prices are sorted by varying the way the calculated
field is worked out.

Chris.

 

[Allen Browne]

Do all items have a value in the Top Price field that is higher than the
other prices?

If so, you can just choose the Top Price field in the Sorting And Grouping
dialog in report design view, and sort Descending.

If some fields are blank, or the highest price could be in another field,
you could include this statement in your query:
MaxPrice: MaxOfList([low range], [high range], [top price])
and copy the MaxOfList() function from this link:
http://allenbrowne.com/func-09.html

This assumes that the 3 fields are numeric or currency fields. If they are
text to represent the range, break them into 2 currency fields each, such as
LowMin and LowMax, HighMin and HighMax, TopMin and TopMax.

 

[Dodsey77]

Hi there,

No... that's the problem: Some games only have the price range; some also
have a Top Price and others don't.

The majority of games have the range only.

Thanks,

Do all items have a value in the Top Price field that is higher than the
other prices?

If so, you can just choose the Top Price field in the Sorting And Grouping
dialog in report design view, and sort Descending.

If some fields are blank, or the highest price could be in another field,
you could include this statement in your query:
MaxPrice: MaxOfList([low range], [high range], [top price])
and copy the MaxOfList() function from this link:
http://allenbrowne.com/func-09.html

This assumes that the 3 fields are numeric or currency fields. If they are
text to represent the range, break them into 2 currency fields each, such as
LowMin and LowMax, HighMin and HighMax, TopMin and TopMax.

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

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

I have a Retro Systems database showing old computers / consoles, and all
games and accessories associated with them.

The details on each game and accessory include 3 different prices: low
range, high range, and top price. (low range and high range being the
general
sale prices for the game / accessory).

i.e. A game could have a general price of £8 - £15, but with a top price
of
£30 for it in especially good condition.

I want to output a report with games / accessories in decreasing order of
price, so the highest is at the top. However, I would like it to take into
account all of the prices. For instance, something with a top price of £30
would come higher than something with a high range price of £20, but come
lower than something with a low range price of £40.

Can anyone help me write the query, so that it will check all three
fields?

Thanks in advance

 

[Dodsey77]

I see.... So, if I create a new field which looks at the other three to
determine which value is the highest, and then sort on that, it should be
possible?

I don't suppose you have an example of how to compare the values of the
fields?

Thanks,

Helen

 

[Allen Browne]

So you will split the field into 2, and then use the supplied function to
get the highest value?

Storing 2 different things (a high value and a low value) in one field is
the main source of your problem here. It breaks one of the fundamental rules
of data design: making the data atomic. Once you correct this design flaw,
the rest is easy.

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

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

Hi there,

No... that's the problem: Some games only have the price range; some also
have a Top Price and others don't.

The majority of games have the range only.

Thanks,

Do all items have a value in the Top Price field that is higher than the
other prices?

If so, you can just choose the Top Price field in the Sorting And
Grouping
dialog in report design view, and sort Descending.

If some fields are blank, or the highest price could be in another field,
you could include this statement in your query:
MaxPrice: MaxOfList([low range], [high range], [top price])
and copy the MaxOfList() function from this link:
http://allenbrowne.com/func-09.html

This assumes that the 3 fields are numeric or currency fields. If they
are
text to represent the range, break them into 2 currency fields each, such
as
LowMin and LowMax, HighMin and HighMax, TopMin and TopMax.

I have a Retro Systems database showing old computers / consoles, and
all
games and accessories associated with them.

The details on each game and accessory include 3 different prices: low
range, high range, and top price. (low range and high range being the
general
sale prices for the game / accessory).

i.e. A game could have a general price of £8 - £15, but with a top
price
of
£30 for it in especially good condition.

I want to output a report with games / accessories in decreasing order
of
price, so the highest is at the top. However, I would like it to take
into
account all of the prices. For instance, something with a top price of
£30
would come higher than something with a high range price of £20, but
come
lower than something with a low range price of £40.

Can anyone help me write the query, so that it will check all three
fields?

 

[Dodsey77]

Hi,

I think you've misunderstood - there are three different fields. One is
called 'Low range', another is 'high range' and the last is 'top price' - I
don't have more than one thing stored in each field.

Thanks,

So you will split the field into 2, and then use the supplied function to
get the highest value?

Storing 2 different things (a high value and a low value) in one field is
the main source of your problem here. It breaks one of the fundamental rules
of data design: making the data atomic. Once you correct this design flaw,
the rest is easy.

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

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

Hi there,

No... that's the problem: Some games only have the price range; some also
have a Top Price and others don't.

The majority of games have the range only.

Thanks,

Do all items have a value in the Top Price field that is higher than the
other prices?

If so, you can just choose the Top Price field in the Sorting And
Grouping
dialog in report design view, and sort Descending.

If some fields are blank, or the highest price could be in another field,
you could include this statement in your query:
MaxPrice: MaxOfList([low range], [high range], [top price])
and copy the MaxOfList() function from this link:
http://allenbrowne.com/func-09.html

This assumes that the 3 fields are numeric or currency fields. If they
are
text to represent the range, break them into 2 currency fields each, such
as
LowMin and LowMax, HighMin and HighMax, TopMin and TopMax.


I have a Retro Systems database showing old computers / consoles, and
all
games and accessories associated with them.

The details on each game and accessory include 3 different prices: low
range, high range, and top price. (low range and high range being the
general
sale prices for the game / accessory).

i.e. A game could have a general price of £8 - £15, but with a top
price
of
£30 for it in especially good condition.

I want to output a report with games / accessories in decreasing order
of
price, so the highest is at the top. However, I would like it to take
into
account all of the prices. For instance, something with a top price of
£30
would come higher than something with a high range price of £20, but
come
lower than something with a low range price of £40.

Can anyone help me write the query, so that it will check all three
fields?

 

[Allen Browne]

Having 3 fields (each with 2 values) is the root of your issue.

You need to split it into 6 fields to solve the problem.

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

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

Hi,

I think you've misunderstood - there are three different fields. One is
called 'Low range', another is 'high range' and the last is 'top price' -
I
don't have more than one thing stored in each field.

Thanks,

So you will split the field into 2, and then use the supplied function to
get the highest value?

Storing 2 different things (a high value and a low value) in one field is
the main source of your problem here. It breaks one of the fundamental
rules
of data design: making the data atomic. Once you correct this design
flaw,
the rest is easy.

Hi there,

No... that's the problem: Some games only have the price range; some
also
have a Top Price and others don't.

The majority of games have the range only.

Thanks,



:

Do all items have a value in the Top Price field that is higher than
the
other prices?

If so, you can just choose the Top Price field in the Sorting And
Grouping
dialog in report design view, and sort Descending.

If some fields are blank, or the highest price could be in another
field,
you could include this statement in your query:
MaxPrice: MaxOfList([low range], [high range], [top price])
and copy the MaxOfList() function from this link:
http://allenbrowne.com/func-09.html

This assumes that the 3 fields are numeric or currency fields. If they
are
text to represent the range, break them into 2 currency fields each,
such
as
LowMin and LowMax, HighMin and HighMax, TopMin and TopMax.


I have a Retro Systems database showing old computers / consoles,
and
all
games and accessories associated with them.

The details on each game and accessory include 3 different prices:
low
range, high range, and top price. (low range and high range being
the
general
sale prices for the game / accessory).

i.e. A game could have a general price of £8 - £15, but with a top
price
of
£30 for it in especially good condition.

I want to output a report with games / accessories in decreasing
order
of
price, so the highest is at the top. However, I would like it to
take
into
account all of the prices. For instance, something with a top price
of
£30
would come higher than something with a high range price of £20, but
come
lower than something with a low range price of £40.

Can anyone help me write the query, so that it will check all three
fields?

 

[ChrisM]

I beg to differ...

He has 3 fields:

LowRangePrice
HighRangePrice
TopPrice

LowRangePrice and HighRangePrice are the upper and lower limits of the
NORMAL price range for this item
TopPrice is the highest possible price for the item for an exceptional
example of the item in question.

What are the 6 fields that you believe he should have?
Or have I got it wrong...?

Having 3 fields (each with 2 values) is the root of your issue.

You need to split it into 6 fields to solve the problem.

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

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

Hi,

I think you've misunderstood - there are three different fields. One is
called 'Low range', another is 'high range' and the last is 'top price' -
I
don't have more than one thing stored in each field.

Thanks,

So you will split the field into 2, and then use the supplied function
to
get the highest value?

Storing 2 different things (a high value and a low value) in one field
is
the main source of your problem here. It breaks one of the fundamental
rules
of data design: making the data atomic. Once you correct this design
flaw,
the rest is easy.

Hi there,

No... that's the problem: Some games only have the price range; some
also
have a Top Price and others don't.

The majority of games have the range only.

Thanks,



:

Do all items have a value in the Top Price field that is higher than
the
other prices?

If so, you can just choose the Top Price field in the Sorting And
Grouping
dialog in report design view, and sort Descending.

If some fields are blank, or the highest price could be in another
field,
you could include this statement in your query:
MaxPrice: MaxOfList([low range], [high range], [top price])
and copy the MaxOfList() function from this link:
http://allenbrowne.com/func-09.html

This assumes that the 3 fields are numeric or currency fields. If
they
are
text to represent the range, break them into 2 currency fields each,
such
as
LowMin and LowMax, HighMin and HighMax, TopMin and TopMax.


I have a Retro Systems database showing old computers / consoles,
and
all
games and accessories associated with them.

The details on each game and accessory include 3 different prices:
low
range, high range, and top price. (low range and high range being
the
general
sale prices for the game / accessory).

i.e. A game could have a general price of £8 - £15, but with a top
price
of
£30 for it in especially good condition.

I want to output a report with games / accessories in decreasing
order
of
price, so the highest is at the top. However, I would like it to
take
into
account all of the prices. For instance, something with a top price
of
£30
would come higher than something with a high range price of £20,
but
come
lower than something with a low range price of £40.

Can anyone help me write the query, so that it will check all three
fields?

 

[Allen Browne]

Dodsey77, if Chris has understood you correctly and you have 3 number fields
(not 3 text fields that contain ranges such as "£8 - £15"), the solution is
very easy.

As explained in my earlier reply, just create a query and type this into the
Field row in query design:
MaxPrice: MaxOfList([low range], [high range], [top price])
copying the MaxOfList() function from the link.

That's it. You can now sort your report by MaxPrice.

The function takes ignores any Null, and returns the highest value from any
of the 3 fields that contain a value.

You can also do it with nested IIf() statements if you don't want to use the
function.

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

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

I beg to differ...

He has 3 fields:

LowRangePrice
HighRangePrice
TopPrice

LowRangePrice and HighRangePrice are the upper and lower limits of the
NORMAL price range for this item
TopPrice is the highest possible price for the item for an exceptional
example of the item in question.

What are the 6 fields that you believe he should have?
Or have I got it wrong...?

Having 3 fields (each with 2 values) is the root of your issue.

You need to split it into 6 fields to solve the problem.

Hi,

I think you've misunderstood - there are three different fields. One is
called 'Low range', another is 'high range' and the last is 'top
price' - I
don't have more than one thing stored in each field.

Thanks,

:

So you will split the field into 2, and then use the supplied function
to
get the highest value?

Storing 2 different things (a high value and a low value) in one field
is
the main source of your problem here. It breaks one of the fundamental
rules
of data design: making the data atomic. Once you correct this design
flaw,
the rest is easy.

Hi there,

No... that's the problem: Some games only have the price range; some
also
have a Top Price and others don't.

The majority of games have the range only.

Thanks,



:

Do all items have a value in the Top Price field that is higher than
the
other prices?

If so, you can just choose the Top Price field in the Sorting And
Grouping
dialog in report design view, and sort Descending.

If some fields are blank, or the highest price could be in another
field,
you could include this statement in your query:
MaxPrice: MaxOfList([low range], [high range], [top price])
and copy the MaxOfList() function from this link:
http://allenbrowne.com/func-09.html

This assumes that the 3 fields are numeric or currency fields. If
they
are
text to represent the range, break them into 2 currency fields each,
such
as
LowMin and LowMax, HighMin and HighMax, TopMin and TopMax.


I have a Retro Systems database showing old computers / consoles,
and
all
games and accessories associated with them.

The details on each game and accessory include 3 different prices:
low
range, high range, and top price. (low range and high range being
the
general
sale prices for the game / accessory).

i.e. A game could have a general price of #8 - #15, but with a top
price
of
#30 for it in especially good condition.

I want to output a report with games / accessories in decreasing
order
of
price, so the highest is at the top. However, I would like it to
take
into
account all of the prices. For instance, something with a top
price of
#30
would come higher than something with a high range price of #20,
but
come
lower than something with a low range price of #40.

Can anyone help me write the query, so that it will check all
three
fields?

 

[Dodsey77]

That's great - I'll give it a try.

Chris had understood correctly. There are three fields, and in your example,
the value of 8 would be in the LowPriceRange field, 15 would be in
HighPriceRange and if an amount of £30 can be achieved if the game is in Mint
condition, 30 would be stored in the TopPrice field.

What do you mean by "copying the MaxOfList() function from the link"?

Dodsey77, if Chris has understood you correctly and you have 3 number fields
(not 3 text fields that contain ranges such as "£8 - £15"), the solution is
very easy.

As explained in my earlier reply, just create a query and type this into the
Field row in query design:
MaxPrice: MaxOfList([low range], [high range], [top price])
copying the MaxOfList() function from the link.

That's it. You can now sort your report by MaxPrice.

The function takes ignores any Null, and returns the highest value from any
of the 3 fields that contain a value.

You can also do it with nested IIf() statements if you don't want to use the
function.

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

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

I beg to differ...

He has 3 fields:

LowRangePrice
HighRangePrice
TopPrice

LowRangePrice and HighRangePrice are the upper and lower limits of the
NORMAL price range for this item
TopPrice is the highest possible price for the item for an exceptional
example of the item in question.

What are the 6 fields that you believe he should have?
Or have I got it wrong...?

Having 3 fields (each with 2 values) is the root of your issue.

You need to split it into 6 fields to solve the problem.

Hi,

I think you've misunderstood - there are three different fields. One is
called 'Low range', another is 'high range' and the last is 'top
price' - I
don't have more than one thing stored in each field.

Thanks,

:

So you will split the field into 2, and then use the supplied function
to
get the highest value?

Storing 2 different things (a high value and a low value) in one field
is
the main source of your problem here. It breaks one of the fundamental
rules
of data design: making the data atomic. Once you correct this design
flaw,
the rest is easy.

Hi there,

No... that's the problem: Some games only have the price range; some
also
have a Top Price and others don't.

The majority of games have the range only.

Thanks,



:

Do all items have a value in the Top Price field that is higher than
the
other prices?

If so, you can just choose the Top Price field in the Sorting And
Grouping
dialog in report design view, and sort Descending.

If some fields are blank, or the highest price could be in another
field,
you could include this statement in your query:
MaxPrice: MaxOfList([low range], [high range], [top price])
and copy the MaxOfList() function from this link:
http://allenbrowne.com/func-09.html

This assumes that the 3 fields are numeric or currency fields. If
they
are
text to represent the range, break them into 2 currency fields each,
such
as
LowMin and LowMax, HighMin and HighMax, TopMin and TopMax.


I have a Retro Systems database showing old computers / consoles,
and
all
games and accessories associated with them.

The details on each game and accessory include 3 different prices:
low
range, high range, and top price. (low range and high range being
the
general
sale prices for the game / accessory).

i.e. A game could have a general price of #8 - #15, but with a top
price
of
#30 for it in especially good condition.

I want to output a report with games / accessories in decreasing
order
of
price, so the highest is at the top. However, I would like it to
take
into
account all of the prices. For instance, something with a top
price of
#30
would come higher than something with a high range price of #20,
but
come
lower than something with a low range price of #40.

Can anyone help me write the query, so that it will check all
three
fields?

 

[Dodsey77]

I searched the site and found the link to your Function, but I'm having
difficulty using it.

I get the message "Undefined function "MaxOfList" in Expression".

I have created a new module and pasted it in, but it doesn't seem to be
using it correctly.

Thanks for all your help so far.

Helen

That's great - I'll give it a try.

Chris had understood correctly. There are three fields, and in your example,
the value of 8 would be in the LowPriceRange field, 15 would be in
HighPriceRange and if an amount of £30 can be achieved if the game is in Mint
condition, 30 would be stored in the TopPrice field.

What do you mean by "copying the MaxOfList() function from the link"?

Dodsey77, if Chris has understood you correctly and you have 3 number fields
(not 3 text fields that contain ranges such as "£8 - £15"), the solution is
very easy.

As explained in my earlier reply, just create a query and type this into the
Field row in query design:
MaxPrice: MaxOfList([low range], [high range], [top price])
copying the MaxOfList() function from the link.

That's it. You can now sort your report by MaxPrice.

The function takes ignores any Null, and returns the highest value from any
of the 3 fields that contain a value.

You can also do it with nested IIf() statements if you don't want to use the
function.

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

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

I beg to differ...

He has 3 fields:

LowRangePrice
HighRangePrice
TopPrice

LowRangePrice and HighRangePrice are the upper and lower limits of the
NORMAL price range for this item
TopPrice is the highest possible price for the item for an exceptional
example of the item in question.

What are the 6 fields that you believe he should have?
Or have I got it wrong...?

Having 3 fields (each with 2 values) is the root of your issue.

You need to split it into 6 fields to solve the problem.

Hi,

I think you've misunderstood - there are three different fields. One is
called 'Low range', another is 'high range' and the last is 'top
price' - I
don't have more than one thing stored in each field.

Thanks,

:

So you will split the field into 2, and then use the supplied function
to
get the highest value?

Storing 2 different things (a high value and a low value) in one field
is
the main source of your problem here. It breaks one of the fundamental
rules
of data design: making the data atomic. Once you correct this design
flaw,
the rest is easy.

Hi there,

No... that's the problem: Some games only have the price range; some
also
have a Top Price and others don't.

The majority of games have the range only.

Thanks,



:

Do all items have a value in the Top Price field that is higher than
the
other prices?

If so, you can just choose the Top Price field in the Sorting And
Grouping
dialog in report design view, and sort Descending.

If some fields are blank, or the highest price could be in another
field,
you could include this statement in your query:
MaxPrice: MaxOfList([low range], [high range], [top price])
and copy the MaxOfList() function from this link:
http://allenbrowne.com/func-09.html

This assumes that the 3 fields are numeric or currency fields. If
they
are
text to represent the range, break them into 2 currency fields each,
such
as
LowMin and LowMax, HighMin and HighMax, TopMin and TopMax.


I have a Retro Systems database showing old computers / consoles,
and
all
games and accessories associated with them.

The details on each game and accessory include 3 different prices:
low
range, high range, and top price. (low range and high range being
the
general
sale prices for the game / accessory).

i.e. A game could have a general price of #8 - #15, but with a top
price
of
#30 for it in especially good condition.

I want to output a report with games / accessories in decreasing
order
of
price, so the highest is at the top. However, I would like it to
take
into
account all of the prices. For instance, something with a top
price of
#30
would come higher than something with a high range price of #20,
but
come
lower than something with a low range price of #40.

Can anyone help me write the query, so that it will check all
three
fields?

 

[Dodsey77]

Oh I've got it!

I needed to rename the module to have the brackets after it

It works perfectly!

Thanks for all your help!!

Helen

That's great - I'll give it a try.

Chris had understood correctly. There are three fields, and in your example,
the value of 8 would be in the LowPriceRange field, 15 would be in
HighPriceRange and if an amount of £30 can be achieved if the game is in Mint
condition, 30 would be stored in the TopPrice field.

What do you mean by "copying the MaxOfList() function from the link"?

Dodsey77, if Chris has understood you correctly and you have 3 number fields
(not 3 text fields that contain ranges such as "£8 - £15"), the solution is
very easy.

As explained in my earlier reply, just create a query and type this into the
Field row in query design:
MaxPrice: MaxOfList([low range], [high range], [top price])
copying the MaxOfList() function from the link.

That's it. You can now sort your report by MaxPrice.

The function takes ignores any Null, and returns the highest value from any
of the 3 fields that contain a value.

You can also do it with nested IIf() statements if you don't want to use the
function.

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

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

I beg to differ...

He has 3 fields:

LowRangePrice
HighRangePrice
TopPrice

LowRangePrice and HighRangePrice are the upper and lower limits of the
NORMAL price range for this item
TopPrice is the highest possible price for the item for an exceptional
example of the item in question.

What are the 6 fields that you believe he should have?
Or have I got it wrong...?

Having 3 fields (each with 2 values) is the root of your issue.

You need to split it into 6 fields to solve the problem.

Hi,

I think you've misunderstood - there are three different fields. One is
called 'Low range', another is 'high range' and the last is 'top
price' - I
don't have more than one thing stored in each field.

Thanks,

:

So you will split the field into 2, and then use the supplied function
to
get the highest value?

Storing 2 different things (a high value and a low value) in one field
is
the main source of your problem here. It breaks one of the fundamental
rules
of data design: making the data atomic. Once you correct this design
flaw,
the rest is easy.

Hi there,

No... that's the problem: Some games only have the price range; some
also
have a Top Price and others don't.

The majority of games have the range only.

Thanks,



:

Do all items have a value in the Top Price field that is higher than
the
other prices?

If so, you can just choose the Top Price field in the Sorting And
Grouping
dialog in report design view, and sort Descending.

If some fields are blank, or the highest price could be in another
field,
you could include this statement in your query:
MaxPrice: MaxOfList([low range], [high range], [top price])
and copy the MaxOfList() function from this link:
http://allenbrowne.com/func-09.html

This assumes that the 3 fields are numeric or currency fields. If
they
are
text to represent the range, break them into 2 currency fields each,
such
as
LowMin and LowMax, HighMin and HighMax, TopMin and TopMax.


I have a Retro Systems database showing old computers / consoles,
and
all
games and accessories associated with them.

The details on each game and accessory include 3 different prices:
low
range, high range, and top price. (low range and high range being
the
general
sale prices for the game / accessory).

i.e. A game could have a general price of #8 - #15, but with a top
price
of
#30 for it in especially good condition.

I want to output a report with games / accessories in decreasing
order
of
price, so the highest is at the top. However, I would like it to
take
into
account all of the prices. For instance, something with a top
price of
#30
would come higher than something with a high range price of #20,
but
come
lower than something with a low range price of #40.

Can anyone help me write the query, so that it will check all
three
fields?

 

[Allen Browne]

Good news!