Solved Query Help (1 Viewer)

K

KINGOFCHAOS17

Member
Local time
Today, 16:24
Joined
Mar 20, 2020
Messages
45
Need some assistance please, trying to write a query to filter data against several fields.
Field 1 is called 'SLC' and I want to find all records that aren't '0'.
Field 2 is called 'Location' and I want it to only look for the above records outside of 5A002A, 5A002B, 5A002C, 5A002D, 5A002E, 5A002F, 5A002G.
Field 3 is called 'LOCATDetail' and I want it to only look for the above outside of positions 07 thru 21.

I know the above isn't very clear but I'm struggling to put into words what I've trying to achieve. Basically, I'm looking for all non-shelf life records (SLC that don't equal 0) that are stocked outside designated areas. I'm attaching a screen shot of my attempt of a query and a small sample of data, unfortunately I can't provide a copy of the database.
 

Attachments

  • Query.png
    Query.png
    72.9 KB · Views: 133
Code: 
"SLC <> 0 AND Location NOT IN ('5A002A', '5A002B', '5A002C', '5A002D', '5A002E', '5A002F', '5A002G') AND LOCATDetail  < 7 AND LOCATDetail > 21"
 
How is your query not working? What results is it producing that it shouldn't? What results is t it producing that it should?

With that said, you need to fix fix StockLocations.Stock. Every discrete piece of data needs to be in its own field. Based off your query you've got at least 2 pieces of data in there---the first 5 character s are important by themselves and so are the 9th and 10th characters. Because of that, they should go into their own field s. That will definitely make this query simpler.
 
plog said:
How is your query not working? What results is it producing that it shouldn't? What results is t it producing that it should?

With that said, you need to fix fix StockLocations.Stock. Every discrete piece of data needs to be in its own field. Based off your query you've got at least 2 pieces of data in there---the first 5 character s are important by themselves and so are the 9th and 10th characters. Because of that, they should go into their own field s. That will definitely make this query simpler.
Click to expand...
At the moment its pulling anything that is falling between 07 and 21 in the LOCATDetail field. I want it to just show results other than 0 that are outside 5A002A thru 5A002G with LOCATDetails 07 and 21.

I'm thinking you meant Stock_Locations.Locations whilst I agree with you, the field is free text and was designed that way (not by me) to increase flexibility. Overtime we've come up with standardised locations to ensure compliancy with regulations. I wish I could change the layout, but its fixed and in use by many different organisations. The location field is made up of the following for 5A002A0001A:
Position 1 = Building (5)
Position 2 = Zone (A)
Position 3-5 = Rack (002)
Position 6 = Shelf (A)
Position 7-10 = Shelf Position (0001)
Position 11 (optional) =Bin Detail (A)
 
Gasman said:
Code: 
"SLC <> 0 AND Location NOT IN ('5A002A', '5A002B', '5A002C', '5A002D', '5A002E', '5A002F', '5A002G') AND LOCATDetail  < 7 AND LOCATDetail > 21"
Click to expand...
Thank you but honestly I don't know how to implement the above, here's the current SQL:

Code: 
SELECT part_Catalog.PartNbr, part_ShelfLifeCodes.Code AS SLC, stock_Locations.Location, part_CatalogStatusCodes.Description AS Status, Mid([stock_locations].[Location],9,2) AS LOCATDetail, part_Catalog.Description, part_Catalog.NSN, part_UICodes.Code AS UI, part_Catalog.QtyAuthorized AS TAQ, stock_StockDetail.Qty AS [POS Qty], part_ERRCCodes.Code AS ERRC
FROM part_CatalogStatusCodes INNER JOIN (part_ShelfLifeCodes INNER JOIN (part_CatalogDetails INNER JOIN ((((stock_StockMasters INNER JOIN ((stock_StockDetail INNER JOIN part_Catalog ON stock_StockDetail.CatalogId = part_Catalog.Id) INNER JOIN stock_Locations ON stock_StockDetail.LocationId = stock_Locations.Id) ON stock_StockMasters.Id = stock_StockDetail.StockMasterId) INNER JOIN stock_StockOwners ON stock_StockMasters.StockOwnerId = stock_StockOwners.Id) INNER JOIN part_ERRCCodes ON part_Catalog.ERRCCodeId = part_ERRCCodes.Id) INNER JOIN part_UICodes ON part_Catalog.UIId = part_UICodes.Id) ON part_CatalogDetails.CatalogId = part_Catalog.Id) ON part_ShelfLifeCodes.Id = part_CatalogDetails.ShelfLifeCodeId) ON part_CatalogStatusCodes.Id = part_Catalog.CatalogStatusCodeId
WHERE (((part_ShelfLifeCodes.Code) Not Like "0") AND ((stock_Locations.Location) Not Like "5A002a*" Or (stock_Locations.Location) Not Like "5A002b*" Or (stock_Locations.Location) Not Like "5A002c*" Or (stock_Locations.Location) Not Like "5A002d*" Or (stock_Locations.Location) Not Like "5A002e*" Or (stock_Locations.Location) Not Like "5A002f*" Or (stock_Locations.Location) Not Like "5A002g*") AND ((part_CatalogStatusCodes.Description) Not Like "ARCHIVED") AND ((Mid([stock_locations].[Location],9,2)) Between "07" And "21") AND ((stock_StockOwners.Name)="POS"))
ORDER BY stock_Locations.Location;
 
Use my criteria for the WHERE (without the quotes)
FYI 5a002a is not like 5a002b
 
Obviously untested, but you can try:
SQL: 
SELECT
  part_Catalog.PartNbr,
  part_ShelfLifeCodes.Code AS SLC,
  stock_Locations.Location,
  part_CatalogStatusCodes.Description AS Status,
  Mid([stock_locations].[Location],9,2) AS LOCATDetail,
  part_Catalog.Description,
  part_Catalog.NSN,
  part_UICodes.Code AS UI,
  part_Catalog.QtyAuthorized AS TAQ,
  stock_StockDetail.Qty AS [POS Qty],
  part_ERRCCodes.Code AS ERRC
FROM part_CatalogStatusCodes
INNER JOIN (
  part_ShelfLifeCodes
  INNER JOIN (
    part_CatalogDetails
    INNER JOIN (
      (
        (
          (
            stock_StockMasters
            INNER JOIN (
              (
                stock_StockDetail
                INNER JOIN part_Catalog
                        ON stock_StockDetail.CatalogId = part_Catalog.Id
              )
              INNER JOIN stock_Locations
                      ON stock_StockDetail.LocationId = stock_Locations.Id
            )
                    ON stock_StockMasters.Id = stock_StockDetail.StockMasterId
          )
          INNER JOIN stock_StockOwners
                  ON stock_StockMasters.StockOwnerId = stock_StockOwners.Id
        )
        INNER JOIN part_ERRCCodes
                ON part_Catalog.ERRCCodeId = part_ERRCCodes.Id
      )
      INNER JOIN part_UICodes
              ON part_Catalog.UIId = part_UICodes.Id
    )
            ON part_CatalogDetails.CatalogId = part_Catalog.Id
  )
          ON part_ShelfLifeCodes.Id = part_CatalogDetails.ShelfLifeCodeId
)
        ON part_CatalogStatusCodes.Id = part_Catalog.CatalogStatusCodeId
WHERE part_ShelfLifeCodes.Code <> 0
  AND NOT (
    stock_Locations.Location LIKE "5A002*"
    AND
    Mid(stock_Locations.Location, 6, 1) IN ('a', 'b', 'c', 'd', 'e', 'f', 'g')
  )
  AND part_CatalogStatusCodes.Description <> "ARCHIVED"
  AND CInt((Mid(stock_locations.Location,9,2))) < 7
  AND CInt((Mid(stock_locations.Location,9,2))) > 21
  AND stock_StockOwners.Name = "POS"
ORDER BY
  stock_Locations.Location;
 
cheekybuddha said:
Obviously untested, but you can try:
SQL: 
SELECT
  part_Catalog.PartNbr,
  part_ShelfLifeCodes.Code AS SLC,
  stock_Locations.Location,
  part_CatalogStatusCodes.Description AS Status,
  Mid([stock_locations].[Location],9,2) AS LOCATDetail,
  part_Catalog.Description,
  part_Catalog.NSN,
  part_UICodes.Code AS UI,
  part_Catalog.QtyAuthorized AS TAQ,
  stock_StockDetail.Qty AS [POS Qty],
  part_ERRCCodes.Code AS ERRC
FROM part_CatalogStatusCodes
INNER JOIN (
  part_ShelfLifeCodes
  INNER JOIN (
    part_CatalogDetails
    INNER JOIN (
      (
        (
          (
            stock_StockMasters
            INNER JOIN (
              (
                stock_StockDetail
                INNER JOIN part_Catalog
                        ON stock_StockDetail.CatalogId = part_Catalog.Id
              )
              INNER JOIN stock_Locations
                      ON stock_StockDetail.LocationId = stock_Locations.Id
            )
                    ON stock_StockMasters.Id = stock_StockDetail.StockMasterId
          )
          INNER JOIN stock_StockOwners
                  ON stock_StockMasters.StockOwnerId = stock_StockOwners.Id
        )
        INNER JOIN part_ERRCCodes
                ON part_Catalog.ERRCCodeId = part_ERRCCodes.Id
      )
      INNER JOIN part_UICodes
              ON part_Catalog.UIId = part_UICodes.Id
    )
            ON part_CatalogDetails.CatalogId = part_Catalog.Id
  )
          ON part_ShelfLifeCodes.Id = part_CatalogDetails.ShelfLifeCodeId
)
        ON part_CatalogStatusCodes.Id = part_Catalog.CatalogStatusCodeId
WHERE part_ShelfLifeCodes.Code <> 0
  AND NOT (
    stock_Locations.Location LIKE "5A002*"
    AND
    Mid(stock_Locations.Location, 6, 1) IN ('a', 'b', 'c', 'd', 'e', 'f', 'g')
  )
  AND part_CatalogStatusCodes.Description <> "ARCHIVED"
  AND CInt((Mid(stock_locations.Location,9,2))) < 7
  AND CInt((Mid(stock_locations.Location,9,2))) > 21
  AND stock_StockOwners.Name = "POS"
ORDER BY
  stock_Locations.Location;
Click to expand...
I'm getting this pesky error message:

1736877499071.png
 
What happens if you change this line:
SQL: 
WHERE part_ShelfLifeCodes.Code <> 0
to:
SQL: 
WHERE part_ShelfLifeCodes.Code <> '0'
?
 
KINGOFCHAOS17 said:
I'm getting this pesky error message:
Click to expand...
It's may also possible that LOCATDetail is not always numeric - if so, some more creative thinking will be required.

First try changing:
SQL: 
  AND CInt((Mid(stock_locations.Location,9,2))) < 7
  AND CInt((Mid(stock_locations.Location,9,2))) > 21
to:
SQL: 
  AND (Mid(stock_locations.Location,9,2)) < 7
  AND (Mid(stock_locations.Location,9,2)) > 21
 
cheekybuddha said:
What happens if you change this line:
SQL: 
WHERE part_ShelfLifeCodes.Code <> 0
to:
SQL: 
WHERE part_ShelfLifeCodes.Code <> '0'
?
Click to expand...
No longer getting the error message, but its returning no results which I know to be incorrect. I've been looking at the data and it appears part_ShelfLifeCodes.Code is padded out with a bunch of spaces, not sure if that makes a difference.
 
cheekybuddha said:
It's may also possible that LOCATDetail is not always numeric - if so, some more creative thinking will be required.

First try changing:
SQL: 
  AND CInt((Mid(stock_locations.Location,9,2))) < 7
  AND CInt((Mid(stock_locations.Location,9,2))) > 21
to:
SQL: 
  AND (Mid(stock_locations.Location,9,2)) < 7
  AND (Mid(stock_locations.Location,9,2)) > 21
Click to expand...
The error message is back, I tried with and without the '' around 0 in part_ShelfLifeCodes.Code
 
What datatype is part_ShelfLifeCodes.Code?

Can you give some examples of different codes?
 
cheekybuddha said:
What datatype is part_ShelfLifeCodes.Code?

Can you give some examples of different codes?
Click to expand...
Appears to be text with a field size of 10. Not sure why they made it 10 when its always a one digit character. The other codes run from 0 thru 9 and A thru Z.
 
So, I've found a workaround, its not elegant but it works (for now).

SQL: 
SELECT part_Catalog.PartNbr, part_Catalog.Description, part_Catalog.NSN, part_UICodes.Code AS UI, part_ShelfLifeCodes.Code AS SLC, stock_Locations.Location, part_Catalog.QtyAuthorized AS TAQ, stock_StockDetail.Qty AS [POS Qty], part_ERRCCodes.Code AS ERRC, part_CatalogStatusCodes.Description AS Status, part_Catalog.Cage
FROM part_CatalogStatusCodes INNER JOIN (part_ShelfLifeCodes INNER JOIN (part_CatalogDetails INNER JOIN ((((stock_StockMasters INNER JOIN ((stock_StockDetail INNER JOIN part_Catalog ON stock_StockDetail.CatalogId = part_Catalog.Id) INNER JOIN stock_Locations ON stock_StockDetail.LocationId = stock_Locations.Id) ON stock_StockMasters.Id = stock_StockDetail.StockMasterId) INNER JOIN stock_StockOwners ON stock_StockMasters.StockOwnerId = stock_StockOwners.Id) INNER JOIN part_ERRCCodes ON part_Catalog.ERRCCodeId = part_ERRCCodes.Id) INNER JOIN part_UICodes ON part_Catalog.UIId = part_UICodes.Id) ON part_CatalogDetails.CatalogId = part_Catalog.Id) ON part_ShelfLifeCodes.Id = part_CatalogDetails.ShelfLifeCodeId) ON part_CatalogStatusCodes.Id = part_Catalog.CatalogStatusCodeId
WHERE (((part_ShelfLifeCodes.Code) Not Like "0*") AND ((stock_Locations.Location) Not Like "5A002?0007" And (stock_Locations.Location) Not Like "5A002?0008" And (stock_Locations.Location) Not Like "5A002?0009" And (stock_Locations.Location) Not Like "5A002?0010" And (stock_Locations.Location) Not Like "5A002?0011" And (stock_Locations.Location) Not Like "5A002?0012" And (stock_Locations.Location) Not Like "5A002?0013" And (stock_Locations.Location) Not Like "5A002?0014" And (stock_Locations.Location) Not Like "5A002?0015" And (stock_Locations.Location) Not Like "5A002?0016" And (stock_Locations.Location) Not Like "5A002?0017" And (stock_Locations.Location) Not Like "5A002?0018" And (stock_Locations.Location) Not Like "5A002?0019" And (stock_Locations.Location) Not Like "5A002?0020" And (stock_Locations.Location) Not Like "5A002?0021" And (stock_Locations.Location) Not Like "5A004?0001" And (stock_Locations.Location) Not Like "5A004?0002" And (stock_Locations.Location) Not Like "5A004?0003" And (stock_Locations.Location) Not Like "5A004?0004" And (stock_Locations.Location) Not Like "5A004?0005" And (stock_Locations.Location) Not Like "5A004?0006" And (stock_Locations.Location) Not Like "5A004?0007" And (stock_Locations.Location) Not Like "5A004?0008" And (stock_Locations.Location) Not Like "5D001*" And (stock_Locations.Location) Not Like "5F*" And (stock_Locations.Location) Not Like "5H*" And (stock_Locations.Location) Not Like "5C*" And (stock_Locations.Location) Not Like "NL" And (stock_Locations.Location) Not Like "*dutch*") AND ((part_CatalogStatusCodes.Description) Not Like "ARCHIVED") AND ((stock_StockOwners.Name)="POS") AND ((part_Catalog.Cage) Not Like "RJKIT"))
ORDER BY stock_Locations.Location;

Here's some sample return data for info.
1736884268857.png
 
Last edited:
Thank you for everyone's input in getting me to the end result, if you have suggestions on how to clean up or improve the query I'm happy to hear your ideas.

Thanks again :)
 
Yes, not elegant and going to get worse if you need to ignore other categories. That is why as IN() is easier to read.
You could even consider another field that would group what you want at any time, and can be easily amended if that changes a lot.?
 
You must log in or register to reply here.

Similar threads

L
Exclude sime recirds from query result
Replies
5
Views
591
LjushaMisha
L
AOB
UPDATE with LEFT-JOIN (conditional update query)
Replies
9
Views
766
isladogs
isladogs
D
Solved Query if Between lower and upper to return lower, returning all records
Replies
9
Views
332
dullster
D
E
Help query Count
Replies
5
Views
682
cheekybuddha
cheekybuddha
K
Strange query behavior
Replies
10
Views
160
KACJR
K

Users who are viewing this thread

Total: 2 (members: 0, guests: 2)
Back
Top Bottom