Retrieve minimum price of each item in multiple sets of options

[jase7678]

I have a database containing an ItemTypesColors table which has a 1:Many relationship with an ItemDescription table which has a 1:Many relationship with an ItemVendor table.

• The ItemTypesColors table comprises Item Type & Color, eg: Shirts|White, Shirts|Black, Jeans|Blue etc.
• The ItemDescription table contains the number of items in a package, a link to the ItemVendor table, price etc.
• The ItemVendor table contains the Vendor name, a discount %, Sales Tax % & Shipping Fee.

Multiple Vendors can supply White Shirts but will do so in varying Quantities, at different Prices and differ in whether they offer a discount, charge sales tax or shipping etc.

I have a form allowing user to enter the Item Type, Color & Quantity they want. I then have a query that calculates the total price of each Vendor’s offering. This is working nicely but another query that utilizes this in order to return the minimum price for each Item Type & Color combination is returning duplicate records for those Item & Color combinations supplied by multiple Vendors.

If I return just ItemTypesColors ID & MinOfPrice then I get exactly what I want, 1 price for each ItemTypesColors ID:

[ItemTypesColorsID][MinOfPrice]
132                  $63.18
136                  $40.00
138                  $288.98
139                  $110.49
144                  $27.99

The problem with this is that ItemTypesColors ID doesn’t get me to the ItemVendor that’s cheapest. For that I need to add the ItemDescription ID but when I do that I get duplicated records, eg:

[ItemTypesColorsID][MinOfPrice][ItemDescriptionID]
132                    $63.18     73
136                    $40.00     272
138                   $288.98     286
139                   $110.49     18
144                    $37.98     41
144                    $27.99     42
144                    $46.98     43

At one point in time I made a change that resulted in $27.99 being returned for 144|41, 144|42 & 144|43 but I’m not sure how I did that.

Could anybody advise what I need to change in my query such that it retrieves all 3 fields as above but just one record for each instance of ItemTypesColors ID?

This is the code currently in my query:

SELECT qry_1_TotalPricesOfItemsByEventRevisionID.tblItemTypesColorsJunction_ID
   , Min(qry_1_TotalPricesOfItemsByEventRevisionID.Price)       AS MinOfPrice
   , qry_1_TotalPricesOfItemsByEventRevisionID.ItemDescriptionID
FROM qry_1_TotalPricesOfItemsByEventRevisionID
GROUP BY qry_1_TotalPricesOfItemsByEventRevisionID.tblItemTypeItemColorJunction_ID
   , qry_1_TotalPricesOfItemsByEventRevisionID.ItemDescriptionID;

Thank you for any assistance.

Maybe an image will help. The following is currently being returned by a query. I don't necessarily want to modify this query because I might want access to all this information at same other time. How do I write a query to just return the lowest iTotalPrice along with ItemDescription ID for each tblItemTypesIte mColorJunction_ ID, ie: eliminate the two records with red lines through them?

[IMGnothumb]http://i819.photobucke t.com/albums/zz117/jase7678/Access%20Query% 20Discussion%20 Forum/Capture.png[/IMGnothumb]

Thank you.

[zmbd]

The problem is that you cannot group on multiple fields in the manner in which you are attempting in an aggregate query.
Use your query
Feed that to a second query grouped on colour and min value of total price
feed this query to another that returns only matching between the first two.
SQL:
Query2:

SELECT [Table2 Query].colour
   , Min([Table2 Query].ttlp) 
      AS MinOfttlp
FROM [Table2 Query]
GROUP BY [Table2 Query].colour;

Query3:

SELECT [Table2 Query].pk
   , [Table2 Query].colour, [Table2 Query].ttlp
   , [Table2 Query].dscp
FROM [Table2 Query] 
   INNER JOIN Query2 
      ON ([Table2 Query].ttlp = Query2.MinOfttlp) 
         AND ([Table2 Query].colour = Query2.colour);

Where [Table2 Query] is your current query
Where [Query2] is the query that returns the minimum from your current query

You can of course combine these into one massive SQL script; however, I've not time to do that this morning.... Father's day, Church, and a Baseball Game to go to!

Using your data and description I've ran this in my test database and returned only the information you desire.

[jase7678]

zmdb - thank you for taking the time to correctly format my post (embedded image (I jumped to the conclusion that the post would have to point to a hosted image), restructure code & presented current query results in tables), present a solution & confirm it works in your own DB all while having many personal items to attend to. I implemented your suggestion and it works exactly as required.

I'm surprised there isn't a way of doing this in a more streamlined way, ie: facilitated by SQL or MS Access, but being aware of such limitations is half the battle and will reduce the amount of time I spend trying to figure out ways to achieve something.

Thank you again - your clear and detailed response was greatly appreciated.

Jason.

[NeoPa]

I echo your congratulations Jason. Great response from ZMBD.

I would also point to your original post and subsequent response as examples of an excellent way to deal with such experts and volunteers as you may find on a forum site such as this. Your clear explanation and attention to detail made it a pleasure to deal with I suspect. Certainly an easier and more pleasant experience than many we come across.

Cheers -Ade.

[jase7678]

Thank you, Ade. I try to be as detailed and clear as possible but sometimes feel I go into too much detail so it's pleasing to hear that I may have hit the right balance in this instance.

Jason.

[zmbd]

Yes, there is definitely a balancing act in the amount of information needed in the initial post and you did just fine.

The posts that are hard to work with are:
"It doesn't do what I want..."
followed by a script containing 100's of lines and no explanation of what it is supposed to do and what it's doing.

:)

BOL W/ your app.

[NeoPa]

I agree with both of you.

Knowing what the target to aim for is half the battle though, and you clearly do.