Can you make a query showing the selected product and related products?

[Nano]

Hello I have created a database to keep track of chemicals for my company. Each bottle is labeled with a unique barcode that the database uses to search records. Even chemicals that are the same have different barcodes to keep track of quantities and location of each bottle. Also because of the way chemistry is done chemical names may vary from bottle to bottle even if they are essentially the same thing. The only thing that is constant between chemicals that are the same is the “CAS Registry Numbers” (CAS) which is a set of numbers that have no inherent meaning. (I normally have to look these up)

So right now I have two ways to search the database, either using the “find/replace box” on a form to search the barcode number (Name/barcode/CAS) which only brings up one record at a time or I have a query to search by CAS number.

Is there a way to make a query that will search by barcode and display the record with the matching barcode first, then using the Chemical name and CAS number from the first search display any other records that match? I would prefer to do this in a table not a form.

 

[John Big Booty]

You could do this with a set of Cascading Combo Boxes.

As for doing this at Table level, Don't.

Tables are for storing your data and your User should have no direct access to data at table level. All user interaction with that data should occur through the "filter" of a Form (either directly or via a Query), in this way you are able to control how the user interacts with and sees that data.

If your user has access to the data at table level you can not control what your users sees or what they do to that data, a recipe for disaster.

 

[Nano]

You could do this with a set of Cascading Combo Boxes.

As for doing this at Table level, Don't.

Tables are for storing your data and your User should have no direct access to data at table level. All user interaction with that data should occur through the "filter" of a Form (either directly or via a Query), in this way you are able to control how the user interacts with and sees that data.

If your user has access to the data at table level you can not control what your users sees or what they do to that data, a recipe for disaster.

Thanks for the advice, the users in this case are the staff scientist and myself so I am not all that worried about what they see. This database is completely internal. I also lock the database and make it read only for them.
As for the Cascading Combo Boxes I will look into it, but I am not sure how to use them in this case.

Thank you though

 

[Nano]

Yeah I don't think a cascading combo box is going to work for this case. There are literally thousands of different names, Barcodes and CAS numbers. There is far too much to scroll though on a list box, even if I did a cascading list by having the user sort through the different categories of a chemical I would still get to over 100 different options at its most sorted point.