 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / May 2008Dmin AND Dmax within expression builder
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?  Signaturehttp://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 [quoted text clipped - 8 lines] > >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). SignatureJohn W. Vinson [MVP] 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! Signaturehttp://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 [quoted text clipped - 19 lines] > and >=DMin then you would see all the records (including the values at the > boundaries). >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. SignatureJohn W. Vinson [MVP] 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. Signaturehttp://njgin.aclink.org > >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 [quoted text clipped - 9 lines] > table might contain 300.00000000024 which will display as 300 but will satisfy > the criterion. 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. Signaturehttp://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 [quoted text clipped - 19 lines] > and >=DMin then you would see all the records (including the values at the > boundaries). >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 [quoted text clipped - 8 lines] > >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! SignatureJohn W. Vinson [MVP] 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. Signaturehttp://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 [quoted text clipped - 11 lines] > 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! >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 [quoted text clipped - 25 lines] >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! SignatureJohn W. Vinson [MVP] Signaturehttp://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 [quoted text clipped - 40 lines] > a date range out of tblItems when (so far as I can see) tblItems does not have > a date field! 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? Signaturehttp://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 [quoted text clipped - 40 lines] > a date range out of tblItems when (so far as I can see) tblItems does not have > a date field! >You are correct. >tblProjects and tblItems are related by the common field "ProjectID". [quoted text clipped - 9 lines] >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? SignatureJohn W. Vinson [MVP] 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. Signaturehttp://njgin.aclink.org > >You are correct. > >tblProjects and tblItems are related by the common field "ProjectID". [quoted text clipped - 20 lines] > Does it apply to an Item? Do you have multiple prices over time, and if so how > do you store them? >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? SignatureJohn W. Vinson [MVP] 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. Signaturehttp://njgin.aclink.org > >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 [quoted text clipped - 29 lines] > 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? >Thank you for your responses John. It sounds like I need to redesign the >table structure. [quoted text clipped - 23 lines] >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. SignatureJohn W. Vinson [MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.