Access query to find missing alphanumeric data

[Soupy8728]

Our company has a lot of warehouses with a lot of inventory. An example of a location would be A01A1, A01A2, A01A3, A01A4 where A01 would be a floor space and inventory would be stacked 4 high on top of each other. My question is what if A01A3 gets left out during inventory count? How can I query to find these missing locations? Hope this makes sense. Thank you.

[June7]

Sorry, accidentally duplicated post.

[June7]

Explore Find Unmatched query. Access has a build wizard for that.

Or maybe:

SELECT * FROM Locations WHERE LocationID NOT IN(SELECT LocationID FROM Inventory WHERE InventoryID=someinput)

[Bulzie]

You are you saying in your table they might omit entering A01A3 but since you have an A01A4, you know there should be an A3? So you want the program to tell you A3 is missing?

[Soupy8728]

Yes exactly and we call this a “floater” because if something is in A4, we know something should be in A3 but the data didn’t get captured. This is just a tiny example. We have over 9000 locations that this could be affecting. Thank you.

[Bulzie]

What is your table structure, do you have a table for Floors and a table for Stacks? Are there any finite conditions like you will never have more then 5 stacks for one floor, etc? You could also add in logic to prevent someone missing a stack on entry maybe.

[Bob Fitz]

Yes exactly and we call this a “floater” because if something is in A4, we know something should be in A3 but the data didn’t get captured. This is just a tiny example. We have over 9000 locations that this could be affecting. Thank you.

I'm not sure that I really understand the data here but have a look at the attached db. The query named "Query1 Without Matching T2" would list the "Floaters"

[Bob Fitz]

If we have provided a solution, please mark this thread as solved. (top of the thread under "Thread Tools"

[Soupy8728]

You provided a solution to part of my problem. Thank you.

[mike60smart]

It would help if you upload a screenshot of your Relationship Window.