Query - count summary while excluding specific text

[pandorah]

Hi there, I'm trying to do a query on a table to count the number of fields with data while excluding one specific type of data. I have attached an example in excel to illustrate what I'm trying to do. In this example, I want to combine each unique 'Item' into one line and count the number of 'Pieces' on each individual 'day'. However, I want to exclude any cell that says "Red" from that count. The bottom two rows show the output I'm looking for. Can you help me figure out the best way to do this? Thank you.

[CJ_London]

if this is supposed to represent an Access table then I'm afraid you have a major task ahead - one I don't want to get involved with.

If on the other hand you normalise your data then the problem is trivial

Normalised your data would look more like the

Item....Piece...Day........colour
shirt.....1........sunday...red
shirt.....1........monday...purple
shirt.....1........tuesday...purple
shirt.....1........wedensday...purple
shirt.....1........thursday...purple
shirt.....1........friday...purple
shirt.....1........saturday...red
shirt.....3........sunday...red
shirt.....3........monday...red
shirt.....3........tueday...green
etc

then you query would be something like

SELECT item, Day, count(*)
FROM myTable
WHERE colour<>"Red"

which you can then present horizontally by converting the query to a crosstab

[pandorah]

Unfortunately the current table is imported from another system and the data is horizontal as represented in my attachment. I can't change how the data arrives. Is there is a way to normalize it the way you suggest via access once imported? If not, then thank you anyway and I will wait and see if someone else is willing to help.

[CJ_London]

Is there is a way to normalize it the way you suggest via access once imported?

create your table then use an append query to append each column in turn to the table. You can do this in one hit using a union query - which if this is just an occasional requirement you can use instead of the table - something like this union query can be turned into an append query

SELECT item, Piece, Sunday as Day, colour FROM xlTable
UNION ALL SELECT item, Piece, Monday, colour FROM xlTable
UNION ALL SELECT item, Piece, Tuesday, colour FROM xlTable
etc

[pandorah]

Thank you for the help

[alansidman]

You can normalize your data by bringing it into Power Query and then unpivoting the data

Here is the Mcode to make that happen

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Item", "Day:"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

If you want to take it to the next step and do the calculation in Power query then

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Item", "Day:"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "Red")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Item", "Attribute"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"