Selecting Records by Part Size

[ColbyDog]

Hello, I'm looking for help with an issue selecting parts by their dimensions/size. I have a very large quantity of parts that I need to condense to a manageable report. I want to show only the smallest size of each product that we offer and thought that something like this may be the way to go:



Tile Type Size Price AutoNumber Show
Black Granite 6" 6.00 1 X
Black Granite 12" 12.00 2
Black Granite 18" 18.00 3
Green Granite 12" 12.00 1 X
White Granite 6" 6.00 1 X
White Granite 12" 12.00 2
White Granite 18" 18.00 3
Black Marble 6" 12.00 1 X
Black Marble 12" 24.00 2
Black Marble 18" 36.00 3
Pink Marble 18" 36.00 1 X

Filtering on the "X"s would leave a much smaller report that contains only the "starting prices" like this:

Tile Type Size Price AutoNumber Show
Black Granite 6" 6.00 1 X
Green Granite 12" 12.00 1 X
White Granite 6" 6.00 1 X
Black Marble 6" 12.00 1 X
Pink Marble 18" 36.00 1 X

The audience would know exactly how to use this report.

Does anyone know if it is possible to populate an autonumber within a category? and to then flag or filter on the smallest autonumber value, which would be "1"?

As always, I appreciate your taking the time to read this. Cheerss!

[ColbyDog]

Sorry, I had it spaced to mimic a table. I didn't realize the spaces would be removed. If you can read delimited text, each row is assigned a number within the category (right before the "X").

[June7]

Assigning a unique identifier sequence for each group would require VBA code or possibly an UPDATE query using DCount() function. However, it might not be necessary to have this field in table to return the appropriate record.

Because the Size field is text, not number, this is a bit trickier.

SELECT *, Val([Size]) AS Expr1
FROM Tablename
WHERE (((Val([Size]))=DMin("Val(Size)","Tablename","Tile='" & [Tile] & "' AND Type='" & [Type] & "'")));