There are 156 items in Alum_List. It appears 141 are used in Alum_Tab.


Aggregating on ItemName/Type/Colour/Size/Thickness for the max Price returns 324 records. Excluding records where Rate is null returns 103 records. INNER JOIN of that query to Alum_List on just the ItemName fields returns 102 records - item "60mm Round Pipe" is not in Alum_List. Also, record 1240 does not have price.

A self-inner-join of the 103 record query results in only 36 records. That is because some of the fields are null and cannot join on nulls. So revised queries:

Query1: AlumADJ
SELECT Alum_Tab.ItemName, Alum_Tab.VendorName, Alum_Tab.DatePurchased, Alum_Tab.Rate, Alum_Tab.Details, Nz([Type],"none") AS Tpe, Nz([Size],"none") AS Sz, Nz([Colour],"none") AS Clr, Nz([Thickness],0) AS Thick, Alum_Tab.Price
FROM Alum_Tab;

Query2
SELECT AlumADJ.ItemName, AlumADJ.VendorName, AlumADJ.DatePurchased, AlumADJ.Rate, AlumADJ.Details, AlumADJ.Tpe, AlumADJ.Sz, AlumADJ.Clr, AlumADJ.Thick, AlumADJ.Price
FROM AlumADJ INNER JOIN (SELECT ItemName, Tpe, Clr, Sz, Thick, Max(Price) AS MaxPrice FROM AlumADJ WHERE NOT Rate Is Null GROUP BY ItemName, Tpe, Clr, Sz, Thick) AS Dupe ON (AlumADJ.Price = Dupe.MaxPrice) AND (AlumADJ.Thick = Dupe.Thick) AND (AlumADJ.Sz = Dupe.Sz) AND (AlumADJ.Clr = Dupe.Clr) AND (AlumADJ.Tpe = Dupe.Tpe) AND (AlumADJ.ItemName = Dupe.ItemName)
WHERE ((Not (AlumADJ.Rate) Is Null))
ORDER BY AlumADJ.ItemName, AlumADJ.Tpe, AlumADJ.Sz, AlumADJ.Clr, AlumADJ.Thick;

The output is 148 records. There are duplicate combinations of ItemName/Type/Size/Color/Thickness/Price. Think that's all I can accomplish with this data.

1. So Colour is not supposed to be grouping factor? It should not contribute to the unique identifier? Why do you show Colour in GROUP BY in your first post?

2. That is what the query should do but your data does not allow it.