Grouping in base report based on multiple fields with common entries

[RobinDawes]

Suppose I have a table with fields “Information”, “Colour1”, “Colour2”, “Colour3”

The colour fields all contain string values or no value - I don’t know in advance what the strings might be.

So possible records might look like

                      "Picture Frame","blue","yellow",no value
                      "Sailboat","yellow","red","green"
                      "Book","green",no value,no value
                      "Garden","red","yellow","orange"

I’d like to produce a report that looks like this:

                       blue
                                Picture Frame

                       green
                                Sailboat
                                Book

                       orange
                                Garden

                       red
                                Sailboat
                                Garden

                       yellow
                                Picture Frame
                                Sailboat
                                Garden

Is this possible?

[Ratslinger]

Yes. The key is the query. Using:

SELECT COLOR1 AS COLOR, ITEM FROM ITEMS WHERE COLOR1 <> ''  UNION SELECT COLOR2 AS COLOR, ITEM FROM ITEMS WHERE COLOR2 <> '' UNION SELECT COLOR3 AS COLOR, ITEM FROM ITEMS WHERE COLOR3 <> '' ORDER BY COLOR

for the query. Where the input table is:

The query result is:

Now you can use the query to create the report grouping by COLOR. The query eliminates any item where the color is NULL(empty).

Edit - Corrected SQL to work in Report Builder:

SELECT * FROM (SELECT COLOR1 AS COLOR, ITEM FROM ITEMS WHERE COLOR1 <> ''  UNION SELECT COLOR2 AS COLOR, ITEM FROM ITEMS WHERE COLOR2 <> '' UNION SELECT COLOR3 AS COLOR, ITEM FROM ITEMS WHERE COLOR3 <> '')

[RobinDawes]

Thanks! The query does just what I wanted, which is great. Unfortunately the report won’t generate - it gives an error message “An error occurred while creating the report” - as far as I can tell, it seems to think that “order” is an unexpected token and it thinks there should be a “)” nearby. The same thing happens if I use the wizard to create the report, but the error message in that case is less informative.

I also tried removing the “order by” part of the query but it doesn’t help.

[RobinDawes]

fiddled around with the SQL - it works now - thanks!

[Ratslinger]

Sorry for the problem. The SQL didn’t need any changes. Just turn Run SQL command directly on (it’s a toggle button on the toolbar - SQL label). Some things just can’t be set up using wizard. It has its’ limits.

[Ratslinger]

Just saw the error in report creation. Glad you were able to get it to work.