SELECT IIf((Count([PO]))>1,1,Count([PO])) AS PO_Num, IIf((Count([PO]))>1,'Multiple',[Category2]) AS Cat
FROM [Final]
GROUP BY [Final].Category2, [Final].[PO];

k so stage 1 seems to work, all the PO's that are multiples for the one category have been grouped and their value set to 1... but there are PO's with the same number pointing to another category.

I need to "simply" merge those PO's to be just one line_item.

1001 cat1
1001 cat1
1001 cat1
1001 cat2
1001 cat2
1001 cat2
1002 cat3
1002 cat3


should just combine down to
1 multiple
1 cat3


It would make more sense on a bigger scale but hopefully i'm clear here. But the ones that are not multiple do need to show the category name.

Can anyone spot the issue here?

I'm finding that there are many disimilarities between access and sql.

Anyone know how I can finish off this query so I can move on :-)