I am trying to setup an autocomplete that will show only the parts for which the logged in user has no inventory.  This is my table setup: User_Master, Parts, and Inventory.  I know in Microsoft Access you can achieve this by first creating a query to determine the inventory for the User, then using that query inside the query to determine the Parts that have a null Part Id in the first query.  How can I accomplish this?  I know how to do it for all parts with no inventory but thats not what I want.  I want to show the parts for the specific user with no inventory.

Hi Chris,

I believe you can accomplish that by creating a query with USER_MASTER, INVENTORY and PARTS. In the query you'll be suggested to have joins between USER_MASTER and Inventory and between Inventory and Parts.

You can then right-click the query condition that joins Inventory with Parts (should be something like Inventory.PartId = Parts.Partnumber) and choose "Parts with or without Inventory". That way, for now, your query will include all parts, either associated with an Inventory (and a USER_MASTER) or not.

You can then add another query condition with Inventory.PartId = NullIdentifier() so you'll be filtering all parts that didn't fit any Inventory.

No need for a query of a query for that particular case! Try it out and tell us if it fits what you want!

Best regards,


I have previously built this query, which provides all of the parts without inventory but not for a specific user.  I want the parts without inventory for the parameter UserMasterId.  Let me know if you are having trouble understanding what I am looking for.

For example....

Inventory records are link to a corresponding master user.  I want all the parts that do not have inventory for a specific user.  If user "Chris" has inventory in part 1, part 2, and part 5; I want it to return Parts 3 and 4 as the results in the query.  Whereas user "Miguel" has inventory in Part 3, Part 4, and Part 5, therefore, I want it to return Part 1 and Part 2 when queried for :Miguel".


Oh, and when I set it up with a UserMasterId parameter it shows up with no records found.  I am assuming this is because on parts without inventory would not have a UserMasterId related to it until it becomes an inventory.  I really hope to get past this to continue on with my project, I appreciate any and all of your help.


Anybody out there? LOL
Hi Chris,

Can you please execute a Test Query on your query (with the appropriate parameters) and then paste here the results of the SQL Tab? This tab shows inside the Query Editor after you execute a Test Query operation. 

This way we can better understand where the problem might be. 

Best regards,
Here is the data results from the query I have.  The point of the query is to only show the parts without inventory for a specific user. Say for instance there are 6 parts; Part1, Part2, Part3, etc.  Then assume for userid 4 like in the picture he has inventory for Part4, Part5, and Part6.  I want the query to show he does not have inventory for Part1, Part2, and Part3.  However, that should be for "userid 4" only, because say "userid 3" has inventory for Part1, Part2, and Part3.  I want the query to show Part4, Part5, and Part6 as the parts that "userid 3" does not have inventory for.  This query is to populate a combo box with only the parts the user does not have inventory for so they do not have to be validated and searched for when the user wants to add the part to inventory bu selecting it in the combo box.  I hope I explained it well enough.  Again thank you so much for any help, I really do appreciate it.


Thanks for posting, but what I really need is the contents of the SQL Tab after you run the query. Just press "Close" on the dialog that says no results, and then click the SQL Tab, copy the generated SQL and paste it here.

I suspect what you are trying to do can only be done on an advanced query.

Here you go.

Other Half

Haven't been able to try this myself, but it should be good to get you started. You'll need an Advanced Query with an output of type Parts (looks like this is what you are using right?).

Also, make sure you add any conditions to check if the inventory relation exists BUT no inventory is present (maybe ReorderQuantity = 0)

SELECT {Parts}.*
FROM {Parts}
WHERE {Parts}.[PartNumber] NOT IN
    (SELECT {Inventory}.[PartId]
    FROM {Inventory}
    WHERE {Inventory}.{UserMasterId} = @UserId
    /*add here any condition to check if inventory level = 0*/
ORDER BY {Parts}.[Description]
FROM ({Parts} ENParts Left JOIN {Inventory} ENInventory ON (ENInventory.[PARTID] = ENParts.[PARTNUMBER]))
WHERE (ENInventory.[PARTID] IS NULL) AND ( (ENInventory.[USERMASTERID]= @UserId) and (ENInventory.[USERMASTERID] is not null) )
Thank You So Much!

Just what I was looking for!

You rock
Happy to help!

Have fun coding in the Agile Platform.

Best regards,