Format cells of duplicate values to make them invisible

user_14266931 0
Viewed 47

I have a query in MS Access that looks like the table on the left. For nicer readability, I would prefer it like the table on the right:

Item         Buyer            Item         Buyer
-------------------           -------------------
Hamburger    Tim              Hamburger    Tim
Hamburger    Alice                         Alice
Hamburger    Frank                         Frank
Hamburger    Greg                          Greg
Hot dog      Frank            Hot dog      Frank
Hot dog      Mary                          Mary
Hot dog      Susan                         Susan
Apple        Tim              Apple        Tim
Banana       Susan            Banana       Susan
Banana       Alice                         Alice

This is purely for visual aid, so I don't think it's necessary to change anything in the actual query. Rather, I would simply use some sort of conditional format, so that duplicated cells (except the first ones) in the Item column have an "invisible" font. I tried using "Expr1: Format([Item],[white])", but I couldn't make it work, much less work only for duplicates.

Any help will be appreciated

ms-access formatting
1 Answers

Method in query (not as complicated as I thought), however, could perform slowly in large dataset:

SELECT IIf(DCount("*", "TableName", "Item='" & [Item] & "' AND Buyer<'" & [Buyer] & "'") = 0, [Item], Null) AS I, Buyer AS B
FROM TableName
ORDER BY Item, Buyer;

More stable approach would be to build a report and set textbox HideDuplicates property.

user_76071900
Related