Hi
I have a report that has a booking number, I am comparing where customer codes do not match. Issue is I have one column where there could be 30 customer codes I am comparing against another column, I am using data item to say match or no.
I would like to only show booking numbers where all of the data items say NO ... eg. Booking number duplicated in 10 rows only one row has match all the others have no so I do not want to see this booking number.... if all 10 lines had no then I want to see this booking number. I tried to write a case statement but not sure how?
Can you attach a representation of what you are trying to achieve?
That way, we can visualize what you currently have.
Here it is... Thank you.
You posted your company's data online ?? You should provide only a few rows to explain the situation and post sample data.
1> First create a query with only one column 'Booking Number' and apply a filter to the query - [Data Item1] <> 'NO'.
2> Create 2nd query with only one column 'Booking Number'.
3> Create 3rd query by using 'except' so that the result set is all Booking Numbers except the ones you want excluded.
4> Create 4th query with all columns
5> Join 4th query with 3rd on Booking Number and produce 5th query.
The 5th query will give you only those Booking Numbers where all rows are 'NO'.
thank you ... that makes so much sense
this is dummy data not real