Filtering data from combo list (1 Viewer)

I haven't made an access database for the best part of 20 years and I'm struggling, I'm hoping you guys can help me! The information I'm working with has been gradually migrated from paper to excel and now I'm wanting to make it a bit more joined up so that we can do searches, create reports etc. Basically automate tasks which people currently go and look stuff up to do then type it.

So I have a table with a list of moulds that the company owns, some we only have 1off, some we have multiple off, so lets for example say that table might be;

A1 - Mould number 1 to make part A
A2 - Mould number 2 to make part A
A3 - Mould number 3 to make part A
B1 - Mould number 1 to make part B
B2 - Mould number 2 to make part B

etc etc. The table has other data relating to the moulds but that's not relevant


Then I have a table of products, this has the products code, description, drawing number etc etc then a drop down box with tick boxes so when you add a product you can select which moulds can make that part. So;

X123 - Part X 123 - A1, A2, A3
Y123 - Part Y 123 - B1, B2


Then I have a third table where operators (will?!?) record the daily quality control tests they do on a product. So there is a drop down box where they can select the product code which is pulled from the product table, the fields to record the results of the tests and a field for the mould which was used to make the part. So;

X123 - test 1 - test 2 - test 3 - mould used
Y123 - test 1 - test 2 - test 3 - mould used


Now here is where I am struggling. I want the mould used field to be a drop down options box. We know from the product table which moulds can be used to make that part so that is what I want the drop down options to be. How can I make access populate that drop down box with the ticked items from the product table based on the product code which they have selected?

I'm trying to do it in the table but if there is no easy way maybe I will have to do it in a form? I want to use forms for data entry when I get that far to make it easier for people to use but I was hoping this could be done in the table?

Table A has moulds.
Table B has Products.

You really need a table C that links Table A to Table B. This would have the primary key for Table A and the primary key for table B at least. May have other data, but this is what drives the link you are talking about. This also assumes that a mould can be used for more than one product.

If a mould is unique to a product then the table for moulds would simply have the ID for the product it is for as a "Foreign Key".

Let me know which of the two is more of a match to your business and I'll help with how to do that.

Yes your right, one mould can make several different products.

The primary key for the product is it's stock code.

The primary key for the mould is the mould number.

But one stock code could have the option of 5 different moulds. These 5 mould numbers are in a drop down check list as a field on the products table.

When they fill in the quality table I want them to be able to specify which mould was used. I can populate a drop down list from the mould table but that gives a list of every mould, at this point we know what product is being tested so I want to be able to filter this list down using the information in the product table so that the user only has the option to pick one of the moulds that can make that product.

Does that make sense?

Well, before going any further, add an autonumber primary key to each table. You can keep the mould and product in there, but don't use them to connect things together.

The primary key that binds things should be one that users don't interact with. If the user can't change it, they can't break things.

Please take a look at the attached database and let me know if this sample is similar to what you are trying to do.

In your particular case I think it will get a bit more extensive. I am guessing that one product can use more than one mould. When you go to manufacture you have more than one example of a given mould, thus allowing for batch production. When an item is made you will be tracking what product it is (and get the moulds) then will need to track which physical moulds of that type are used, along with other data such as when and by whom.

This SHOULD get you going using a unique primary key that normally can't be seen/changed as well as one way to present this kind of data. For my example I use a list of all "Vaccinations" (basic check list) and you enter when it was given to make the "Link". You would be doing something similar then using the primary key from the linked moulds to select which physical mould is used for a given production run.

I think I'm going to have to sit and do some access tutorials, there's so much I can't remember, just finding the button or option I want is taking a while :banghead:

I have attached an example of what I'm trying to do.

- tblMoulds lists the moulds that are available
- tblSteps lists the products that are made and the moulds which they can be be made in as a multiple choice

These 2 would be reasonably static and only updated by myself

- tblPalletTester is the one that the operators would complete (via a more friendly form obviously), so they can pick the product code from a drop down based on tblSteps, enter the various test data, and pick the individual mould that that product came from. In the example this is a drop down list from tblMoulds, however it doesn't make sense to give the option of all the moulds as there are only a limited number of moulds that could be used, which has already been defined in tblSteps. So how can I make the drop down give only the mould numbers as an option as defined in tblSteps and have them select the one that applies.

Obviously this doesn't have to be done at table level it could be done at form level as the table only needs to store a number.

Thanks for taking the time to look at this, I appreciate it!

cynic-al said:

I think I'm going to have to sit and do some access tutorials---

Click to expand...

There are some tutorial Videos on my website here:-

www.niftyaccess.com

Sent from my SM-G925F using Tapatalk