Raju_Diddi Raju_Diddi - 1 month ago 5x
SQL Question

MS-Access get the list of products having a special indicator for all the multiple records of the product

I had a stock data report which consists of various columns

  • P.Num

  • S.Loc

  • Batch

  • Del @ S.Loc

  • Qty

  • Price

  • Value

report consists of multiple records for each product with a combination of "S.Loc" & "Batch".
Each record is having its own "qty", "price" & "value".

Here another column "Del @ S.loc" which having an entry "X" or empty.

"X" indicates that the product is deleted from the system records at the particular "S.Loc" & "Batch", for further transactions.

Now i need to identify/exclude the products having this "X" indicator items as completely deleted item, only when the same product is in another S.Loc also marked as "X". otherwise the product is available for any transaction in other Storage Locations.

I appreciate if some one help me in writing the query.

enter image description here


You can do this with conditional aggregation:

select [P.Num]
from t
group by [P.Num]
having min([Del @ S.Loc]) = 'X' and max([Del @ S.Loc]) = 'X';

If you want the original records, you can join this back to the original data.