Dmin AND Dmax within expression builder

[scratchtrax]

If I had the following values in the 'UnitPrice' column: 300, 300, 300 & 640;
and these were found within the 'tblItems' table for a single
'ITEMNAMEDESCRIPTION' and I used this expression:

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

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

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

within the expression builder,

what would the results of this selection query be?

 

[John W. Vinson]

If I had the following values in the 'UnitPrice' column: 300, 300, 300 & 640;
and these were found within the 'tblItems' table for a single
'ITEMNAMEDESCRIPTION' and I used this expression:

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

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

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

within the expression builder,

what would the results of this selection query be?

Ummm...

Why not try it and see?

I'd expect you would get no results at all, because there are no records which
have UnitPrice simultaneously less than 640 and also greater than 300. If you
had a record with 400 in the UnitPrice you would see that single record (since
it is between the two boundaries). If you change the expression to use <=DMax
and >=DMin then you would see all the records (including the values at the
boundaries).

 

[scratchtrax]

Thank you John, this is what I want, no results at all. So, it must be
something else that is causing it to return three 300 values... maybe some
other part of the expression is incorrect. I'll keep looking but if you
think it should return nothing and I've been expecting nothing, then
something else is wrong. THANK YOU!

--
http://njgin.aclink.org

If I had the following values in the 'UnitPrice' column: 300, 300, 300 & 640;
and these were found within the 'tblItems' table for a single
'ITEMNAMEDESCRIPTION' and I used this expression:

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

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

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

within the expression builder,

what would the results of this selection query be?

Ummm...

Why not try it and see?

I'd expect you would get no results at all, because there are no records which
have UnitPrice simultaneously less than 640 and also greater than 300. If you
had a record with 400 in the UnitPrice you would see that single record (since
it is between the two boundaries). If you change the expression to use <=DMax
and >=DMin then you would see all the records (including the values at the
boundaries).

 

[John W. Vinson]

Thank you John, this is what I want, no results at all. So, it must be
something else that is causing it to return three 300 values... maybe some
other part of the expression is incorrect. I'll keep looking but if you
think it should return nothing and I've been expecting nothing, then
something else is wrong. THANK YOU!

What's the datatype of the UnitPrice field - currency? Is it possible that
there is a value in the decimal portion (Currency fields have four decimal
places) that is concealed from view by a format?

Or, if you're using Single or Double, you might be having rounoff error: the
table might contain 300.00000000024 which will display as 300 but will satisfy
the criterion.

 

[scratchtrax]

It is currency, thank you for the tip.

I've looked and it doesn't apply here, unfortunately. So, I continue to
search. I think there may be something wrong with the Dmin part of it,
because when I attempt each part of the expression I end up with three 300
values. Even when I remove the Dmax part of the criterion I get the three
300 values. Weird...

Anyway, thank you very much for replying to my insanity. I am grateful for
any considerate suggestion, thank you.

 

[scratchtrax]

I found this page
http://msdn.microsoft.com/en-us/library/aa159048(office.10).aspx
and it says "...If you use the DMin or DMax function, values are evaluated
before the data is grouped. If you use the Min or Max function, the data is
grouped before values in the field expression are evaluated." I wonder if
this isn't the problem.

--
http://njgin.aclink.org

If I had the following values in the 'UnitPrice' column: 300, 300, 300 & 640;
and these were found within the 'tblItems' table for a single
'ITEMNAMEDESCRIPTION' and I used this expression:

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

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

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

within the expression builder,

what would the results of this selection query be?

Ummm...

Why not try it and see?

I'd expect you would get no results at all, because there are no records which
have UnitPrice simultaneously less than 640 and also greater than 300. If you
had a record with 400 in the UnitPrice you would see that single record (since
it is between the two boundaries). If you change the expression to use <=DMax
and >=DMin then you would see all the records (including the values at the
boundaries).

 

[John W. Vinson]

If I had the following values in the 'UnitPrice' column: 300, 300, 300 & 640;
and these were found within the 'tblItems' table for a single
'ITEMNAMEDESCRIPTION' and I used this expression:

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

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

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

within the expression builder,

what would the results of this selection query be?

Please post the complete SQL of the query, and some indication of what the
data looks like (other than these four values). I'm perplexed!

 

[scratchtrax]

Or maybe the Domain specified for both the Dmin and Dmax should be something
different-this would make things (or at least my limited understanding of
things), more difficult.
There are other ways I could do this, I know there are...this just doesn't
make sense to me, so I sort of feel like I'm obliged to understand why this
doesn't work. Sorry for the hassle. When I switch the query to sql, it
looks 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]));

Unit Price is currency, ITEMNAMEDESCRIPTION is text, WORKTYPE is text. I'm
trying to structure a query so that the prices that are returned are: within
the last 13 months, are within a specified work type and are the middle
prices that exclude the highest and lowest values. It seems like I get the
values I want except that the lowest value does not take into consideration
the date restriction. This query it seems, removes the lowest value from the
whole table and not from just within the specified projects.

Thanks for helping me, just being able to talk (well, write) about it is
helping, thanks.
--
http://njgin.aclink.org

If I had the following values in the 'UnitPrice' column: 300, 300, 300 & 640;
and these were found within the 'tblItems' table for a single
'ITEMNAMEDESCRIPTION' and I used this expression:

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

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

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

within the expression builder,

what would the results of this selection query be?

Please post the complete SQL of the query, and some indication of what the
data looks like (other than these four values). I'm perplexed!

 

[John W. Vinson]

Or maybe the Domain specified for both the Dmin and Dmax should be something
different-this would make things (or at least my limited understanding of
things), more difficult.
There are other ways I could do this, I know there are...this just doesn't
make sense to me, so I sort of feel like I'm obliged to understand why this
doesn't work. Sorry for the hassle. When I switch the query to sql, it
looks 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]));

Unit Price is currency, ITEMNAMEDESCRIPTION is text, WORKTYPE is text. I'm
trying to structure a query so that the prices that are returned are: within
the last 13 months, are within a specified work type and are the middle
prices that exclude the highest and lowest values. It seems like I get the
values I want except that the lowest value does not take into consideration
the date restriction. This query it seems, removes the lowest value from the
whole table and not from just within the specified projects.

Thanks for helping me, just being able to talk (well, write) about it is
helping, thanks.

I expect that it's the confusing parenthesis nesting. That can be a problem
whenever you combine multiple criteria.

Your DMax and DMin are searching (very inefficiently, given the nested
function calls!) on the basis of the item named description. Is there no
ItemID which can be used?

There is nothing in your DMax or DMin to restrict *those function calls* to a
date range. The only restriction on BIDDATE is the one in the outer query. If
the unit price varies over time, how can you determine which is the
appropriate price from tblItems? I guess I don't understand how tblItems and
tblProjects are related, and what you can do to get the appropriate price for
a date range out of tblItems when (so far as I can see) tblItems does not have
a date field!

 

[scratchtrax]

--
http://njgin.aclink.org

Or maybe the Domain specified for both the Dmin and Dmax should be something
different-this would make things (or at least my limited understanding of
things), more difficult.
There are other ways I could do this, I know there are...this just doesn't
make sense to me, so I sort of feel like I'm obliged to understand why this
doesn't work. Sorry for the hassle. When I switch the query to sql, it
looks 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]));

Unit Price is currency, ITEMNAMEDESCRIPTION is text, WORKTYPE is text. I'm
trying to structure a query so that the prices that are returned are: within
the last 13 months, are within a specified work type and are the middle
prices that exclude the highest and lowest values. It seems like I get the
values I want except that the lowest value does not take into consideration
the date restriction. This query it seems, removes the lowest value from the
whole table and not from just within the specified projects.

Thanks for helping me, just being able to talk (well, write) about it is
helping, thanks.

I expect that it's the confusing parenthesis nesting. That can be a problem
whenever you combine multiple criteria.

Your DMax and DMin are searching (very inefficiently, given the nested
function calls!) on the basis of the item named description. Is there no
ItemID which can be used?

There is nothing in your DMax or DMin to restrict *those function calls* to a
date range. The only restriction on BIDDATE is the one in the outer query. If
the unit price varies over time, how can you determine which is the
appropriate price from tblItems? I guess I don't understand how tblItems and
tblProjects are related, and what you can do to get the appropriate price for
a date range out of tblItems when (so far as I can see) tblItems does not have
a date field!

 

[scratchtrax]

You are correct.
tblProjects and tblItems are related by the common field "ProjectID".
It is text and contains a unique number for each project.
I thought I was restricting the date via this field. However, as you've
written and as I look at it now, there isn't a date restriction inside the
Dmin or the Dmax sections. I thought there was a limitation based on the
outer query, or; as it appears in the expression builider.
You are also correct in that, there isn't a date field in tblItems. I
thought I would restrict it with the ProjectID relationship, but that isn't
happening. Part of my thoughts on attempting this were so that there wasn't
a redundant date field. Do you think I need one? Should I store the date in
the Projects and also in the Items table? Can I get a date restriction
inside the Dmin and Dmax sections?

--
http://njgin.aclink.org

Or maybe the Domain specified for both the Dmin and Dmax should be something
different-this would make things (or at least my limited understanding of
things), more difficult.
There are other ways I could do this, I know there are...this just doesn't
make sense to me, so I sort of feel like I'm obliged to understand why this
doesn't work. Sorry for the hassle. When I switch the query to sql, it
looks 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]));

Unit Price is currency, ITEMNAMEDESCRIPTION is text, WORKTYPE is text. I'm
trying to structure a query so that the prices that are returned are: within
the last 13 months, are within a specified work type and are the middle
prices that exclude the highest and lowest values. It seems like I get the
values I want except that the lowest value does not take into consideration
the date restriction. This query it seems, removes the lowest value from the
whole table and not from just within the specified projects.

Thanks for helping me, just being able to talk (well, write) about it is
helping, thanks.

I expect that it's the confusing parenthesis nesting. That can be a problem
whenever you combine multiple criteria.

Your DMax and DMin are searching (very inefficiently, given the nested
function calls!) on the basis of the item named description. Is there no
ItemID which can be used?

There is nothing in your DMax or DMin to restrict *those function calls* to a
date range. The only restriction on BIDDATE is the one in the outer query. If
the unit price varies over time, how can you determine which is the
appropriate price from tblItems? I guess I don't understand how tblItems and
tblProjects are related, and what you can do to get the appropriate price for
a date range out of tblItems when (so far as I can see) tblItems does not have
a date field!

 

[John W. Vinson]

You are correct.
tblProjects and tblItems are related by the common field "ProjectID".
It is text and contains a unique number for each project.
I thought I was restricting the date via this field. However, as you've
written and as I look at it now, there isn't a date restriction inside the
Dmin or the Dmax sections. I thought there was a limitation based on the
outer query, or; as it appears in the expression builider.
You are also correct in that, there isn't a date field in tblItems. I
thought I would restrict it with the ProjectID relationship, but that isn't
happening. Part of my thoughts on attempting this were so that there wasn't
a redundant date field. Do you think I need one? Should I store the date in
the Projects and also in the Items table? Can I get a date restriction
inside the Dmin and Dmax sections?

Well, you can see your tables and you know your business rules better than I
do. Of course a date restriction on the Projects table will have no effect on
a DMax function pulling data from a different table! If a given Item is
involved in many Projects, what is the real-life meaning on a date constraint
on an item? I can't see how it could apply, unless you were to have a price
history table.

What is the real-life meaning of the date field? Does it apply to a Project?
Does it apply to an Item? Do you have multiple prices over time, and if so how
do you store them?

 

[scratchtrax]

Well, lets see if I can't explain a bit better while answering some of your
questions. The date field, I think, applies to both projects and items. As
a project comes in, several contractors submit bids giving prices for each of
the items in the bid spec. The date, it is hoped, will restrict the returned
price values to include only those prices in the last 13 months. This will
be used as a price estimation tool to attempt to closely estimate the cost of
each item by way of a normalized average that only includes the middle price
values for each item. Items over 13 months are too old to be used to
generate a cost estimate. Therefore, I do have multiple prices over time and
these prices are stored in the tblItems table. I was attempting to limit the
items based on the projects date via the projects table. The items table
would be a repository for these prices and would relate to the projects table
by the projectid. The only prices that occurred for projects within in the
last 13 months would be selected.

"Of course a date restriction on the Projects table will have no effect on
a DMax function pulling data from a different table" - Can you put a date
restriction on the Items table based on dates from within the Projects table,
if the two tables are joined by a ProjectID?

Again, thank you for all of your replies, I remain grateful.

 

[John W. Vinson]

Well, lets see if I can't explain a bit better while answering some of your
questions. The date field, I think, applies to both projects and items. As
a project comes in, several contractors submit bids giving prices for each of
the items in the bid spec. The date, it is hoped, will restrict the returned
price values to include only those prices in the last 13 months.

Only if you can uniquely determine the price from the ItemID.

This will
be used as a price estimation tool to attempt to closely estimate the cost of
each item by way of a normalized average that only includes the middle price
values for each item. Items over 13 months are too old to be used to
generate a cost estimate. Therefore, I do have multiple prices over time and
these prices are stored in the tblItems table.

Your query referenced a bid date in the Projects table - not a date in the
Items table. How is the items table structured? You cannot (or should not,
A2007 multivalue fields to the contrary notwithstanding "have multiple prices
over time" stored in a single Price field. Does the ItemID change every time
the price changes?

I was attempting to limit the
items based on the projects date via the projects table. The items table
would be a repository for these prices and would relate to the projects table
by the projectid.

How are the two tables related? I was assuming (probably incorrectly) that one
Item could be involved in many Projects. What real-life entity is modeled by
the Items table? How are Items and Projects related?

Again: given the fact that there is a date in the Projects table, and (again,
TO MY KNOWLEDGE since I cannot see your tables) none in the Items table, how
can you ascretain what prices were in effect for the last 13 months?

 

[scratchtrax]

Thank you for your responses John. It sounds like I need to redesign the
table structure.

I thought that if the projects table and items table were related by project
ID and I restricted the projects that were considered for selecting related
items, that would return the group of items limited to only the observed
projects. I don't believe I can uniquely determine the price from the ItemID.

My query references a bid date in the projects table becuase I thought that
if the projects were limited by date and the projects and items were related
by project, that would limit the selected items from the Items table. So, I
guess I will attempt this by putting a date field in the Items table. I
thought I could do this by relationships through another table, but its
obviously not working. So, I guess I will have the date repeated throughout
the various tables.

No, you were correct. One items can be involved in many projects. One
item, per contractor, per project, stored in the Items table. I'm not sure
how to answer; what real-life entity is modeled by the Items table? I think
it would be to say that each contractors items for each project are held
within the Items table. Items and projects are related by a project-id and
each table has a primary key in the form of an auto-number that I allowed MS
Access to add at development.

Again, thank you for your communications. I will begin the redesign and add
dates to the items table and see if that doesn't help.
Thanks again.

 

[John W. Vinson]

Thank you for your responses John. It sounds like I need to redesign the
table structure.

I thought that if the projects table and items table were related by project
ID and I restricted the projects that were considered for selecting related
items, that would return the group of items limited to only the observed
projects. I don't believe I can uniquely determine the price from the ItemID.

My query references a bid date in the projects table becuase I thought that
if the projects were limited by date and the projects and items were related
by project, that would limit the selected items from the Items table. So, I
guess I will attempt this by putting a date field in the Items table. I
thought I could do this by relationships through another table, but its
obviously not working. So, I guess I will have the date repeated throughout
the various tables.

No, you were correct. One items can be involved in many projects. One
item, per contractor, per project, stored in the Items table. I'm not sure
how to answer; what real-life entity is modeled by the Items table? I think
it would be to say that each contractors items for each project are held
within the Items table. Items and projects are related by a project-id and
each table has a primary key in the form of an auto-number that I allowed MS
Access to add at development.

Again, thank you for your communications. I will begin the redesign and add
dates to the items table and see if that doesn't help.
Thanks again.

Well... *you* know your business structure and data model. I don't!

If knowing the ItemID in fact lets you select a unique project, always the
same project for that item (which it may; I don't know though!) then yes, you
can get a specific date for an item by joining the Items and Projects table.

If you have posted the relationships between the tables anywhere, I've missed
it.