Creating Hierarchy

[Just_Some_Guy]

Hello everyone, I'm quite new to access, been teaching myself for a little over a month now. I haven't gotten to any VBA yet unfortunately and I think that's what my task at hand will involve. I was assigned to make a program in access that has a hierarchy so a user can go in and select a few options and generate an excel sheet with the info they need. Sounds simple right? If anyone could give me insight on how to go about this, PLEASE let me know. I've attempted but I keep running into hurdles that could just be bad ideas on my part or my lack of access knowledge.



This is the IDEAL flow of the program:

There are 4 levels (tentatively), level 1 being the highest. The user should be able to select what level they want to start at and see all options available at that level. So if the user starts at level 1, then one option from level 1 determines multiple level 2 options. Pick the options you want from level 2 and that will determine multiple level 3 options. Pick the options you want from the level 3 options and that will determine multiple level 4 options, and then all those options selected in level 4 will be output to excel. There should be a "Select/Deselect All" button somewhere. The user should be able to add data via a form too. Hopefully this can be done with some help.

Any help would be GREATLY APPRECIATED. Thank you

[rpeare]

can you give a more applied example of your data?

Is it something like, say an employee roster, you want to be able to pick a specific person, then, for instance be able to look at their time off for the last month, that sort of thing?

From your description it sounds like all you'd have to do is have your criteria in a query set up correctly to pull the information you want then simply export the query but without something a bit more graphic that's just a guess.

[Just_Some_Guy]

Sure! We're trying to make a database that has a bunch of tests that are considered the best practices and methods in doing them. When people make a plan to characterize a part, we'd like them to be able to look through this database and pick and choose whatever tests they would need to use based on the elements that are in their new part. The lowest level (level 4) would be tests that need to be done for a part. Level 3 is what we'd call a Flex IP block, which is basically a collection of tests from level 4. Level 2 is the AMS IP Block, and that is a collection of Flex IP blocks from level 3, and level 1 would be the part that is on the market itself, and is comprised of a few AMS IP Blocks.

So an example would be:

Product A (level 1) could have multiple AMS IP blocks (level 2), and within each AMS IP block there could be multiple Flex IP blocks (Level 3). Then within each Flex IP block, there would be multiple tests (level 4).

Essentially the user is just picking tests they need to perform, but with the hierarchy they know where each test is instead of looking through hundreds of tests. That way they look at their part and see they have a few of the same AMS IP blocks as what's in the database, they click on that, and based on the relations, it will give you all the tests that are usually done within that certain block. Let me know if I need to elaborate more.

Thanks!

[rpeare]

ok now let me ask the opposite question

Can each one of the LOWER order be a member of more than one HIGHER order?

For instance

Let's say you have tests 1.

Can test 1 belong only to Flex IP Block 1 or can it belong to Flex IP block 1 OR Flex IP block 2?

similarly can a flex IP block belong to more than one AMS IP block?

You actually only have 3 levels relating to the testing. The products themselves are not part of the level of testing structure.

From my understanding of your process you want to:

1. Create a new product
2. Select which AMS IP blocks you want to do testing in.
3. From within each AMS IP block you want to select which Flex IP blocks to do testing in.
4. From within each Flex IP block you want to choose specific tests to run.

So for instance if you have AMS IP blocks 1 through 3
Each AMS IP block has 3 Flex IP's associated with it (assuming a flex IP can only belong to one AMS IP)
and each Flex IP block has 3 tests associated with it (assuming, again, a test can only be associated with 1 flex IP block)

You'd have a total of 27 tests (3x3x3)

But you may only want to do tests from flex IP's 1 and 3 for a particular item rather than running every single test (27 of them) for each flex and ams IP group.
is that correct?

[Just_Some_Guy]

Test 1 can belong to Flex IP Block 1 OR Flex IP block 2. And a Flex IP block could belong to more than one AMS IP block. Also, an AMS IP block could belong to multiple "parts" (level 1). So say a new part comes out that is similar to one we've done in the past but has a few modifications; the user could select that part and see everything that was done for it, and add new or existing elements to that part. Maybe in the new part, AMS Block 1 has everything as before, but an added Flex IP block. The user will just add the existing (or create a new Flex IP Block) to be added to the AMS IP block of the new part.
Process:
1. Create a new product.
2. If new product is similar to an old one, select the old product, if not select AMS IP blocks that are in new product.
3. Flex IP blocks will be populated based on what AMS IP blocks were selected, and the user will select Flex IP blocks. User may need to add new or existing Flex IP Blocks based on product.
4. Tests will be populated based on what Flex IP Blocks were chosen. Maybe new or existing tests need to be added to this new product too.

It's difficult because the idea is that we have this structure where it populates everything that was associated with the previous higher up block, but also have the ability to add and remove options based on the product. So the user should be able to add or remove blocks or tests at any level except level 1.

Basically a part is a mixture of AMS IP blocks which one AMS IP Block could be a mixture of Flex IP Blocks and Flex IP blocks will generally be the same, but there should always be a way to add/remove tests if it seems fit. Hopefully this doesn't confuse you, it's a lot of blah blah. Let me know if I have to explain it differently.

Thanks

[Just_Some_Guy]

To answer the last question, yes. There should be quite a bit of flexibility, so you select select whatever AMS blocks you have/need, same with Flex IP and Tests. You may only need Test 1 and Test 2 from Flex IP 2, so the user should be able to select each individual test when it gets down to that level.

[rpeare]

This sounds a little dangerous, for instance if you have a product with multiple (let's say 3 as per my original example) AMS blocks, each one of those AMS blocks has mutiple (again 3 as per my original example) FLEX blocks. Now let's say for the sake of argument that each FLEX block has the same test (1 of the 3 tests) in it, you'd have to have a way to reconcile ONE test to 9 different 'required' tests because you have 3 levels of many to many relationships. I'm not saying it's not possible just a little dangerous, particularly when you allow anyone to make their own AMS and FLEX blocks with any amount of tests they want in them.

Theoretically you can end up with every AMS block containing every FLEX block and every FLEX block containing every possible TEST which would be a nightmare and would duplicate/complicate a lot of things. For instance let's take the example where every FLEX group has the same test in it but you want to remove that test as part of the testing for a new part, you'd have to modify the testing requirements for that part on 9 different FLEX ID's

Typically in this sort of set up there are certain tests that are directly related to the AMS group, tests that are directly related to FLEX group and tests that are independent of AMS or FLEX group and the three groups are mutually exclusive.

Is there no way you can group your tests that they are mutually exclusive?

For instance tests for Hardness of metal
Test 1 through 3
or electrical resistance
Test 4 through 6
or chemical composition
Test 7 through 9
AMS tests
Test 10 - 12
FLEX tests
Test 13 - 15

That way you could select specific electrical resistance (for example) tests rather than saying there are 20 different flexID's that have the same electrical resistance test associated with them. In terms of data entry and data lookup it would be a lot less confusing and still give you what you want.

[Just_Some_Guy]

Just reviewed what we really want to get out of this, and it was decided that once an AMS IP or Flex IP block is input into the database (has to be approved by a panel or team of people), it will not change. If a new part has everything the same as Flex 1 but one extra test, it will be considered a new Flex, maybe Flex 1.1 or something. Would that make things easier?

[rpeare]

Not if you can still have the same test in multiple flex IP's and there can be mutliple flex IP's with the same test on the same part.

Your best results are going to be if you you have NO overlapping tests on the same part number.

So for instance if part 1 has FLEX IP 1 and FLEX IP 2 but the two flex IP's have NO overlapping tests you have no problem. it's when you have the same test repeated multiple times per part because it appears in multiple FLEX IP's where the problem are going to start occurring. Like I said it's not impossible it just makes your job of programming a hell of a lot more complex. Being able to assure that there are NO duplicate tests, regardless of how many FLEX IPs are chosen really dictates your way forward.

[Just_Some_Guy]

Okay that makes sense to me. In the case that we have 3 tests that get done within 3 different Flex IP's, each test could be called Test 1 (Flex IP 1), Test 1 (Flex IP 2), Test 1(Flex IP 3), with each Test 1 having its own entry. I'm guessing the same problem would arise if the same Flex IP block was in multiple AMS IP blocks?

[rpeare]

That would solve it but it would be a rather heavy handed approach. let's say every FLEX ID you had all used TEST 1, your TEST table you'd have to have TEST 1 for repeated x times where x represented the number of FLEX ID's you had. In essence in your TEST table you do not want any duplicated tests

In your FLEX IP table you want to arrange your tests so that when you chose FLEX IP related to a part there were no overlapping tests, and this would backtrack to your AMS IP's.

What I'd really love to see if you have it is an example of what your company is currently using to show which tests need to be performed on which products.

That might make things a little clearer in terms of giving you direction but what it comes down to is that you want to store as little information as possible and everything you're indicating thus far makes that seem like a terribly lofty goal

[Just_Some_Guy]

I don't think we have an example right now, this is a really new project working towards standardizing a lot of things, but we'll work on getting something to you to help

[Just_Some_Guy]

We decided to go ahead and use multiple records for the same test, so our database has increased a bit, but it's still quite small in the grand scheme of things. Our goal now is to create a form to filter the query based on what the user wants, and have that output to excel where they can change how it looks or maybe add/remove minor details that shouldn't be changed in the database etc. The problem is making a query that will show everything. We added a section that is on the same level as Flex IP, but not considered Flex IP, so the levels are now IF > AMS > Flex and/or Bundle > Tests.

Our issue is that some Products may not contain something from each level, so we'd like to show that in the query.
E.g.
IF block has some AMS IP's, and one of those certain AMS IP's doesn't have a Flex IP at all, but it has a Bundle and Tests. We'd like to be able to show all the AMS IP's (even if no Flex IP is associated with it), and continue to show the Bundle and Tests. This should hold true for any level.

Hopefully this makes sense.

Sorry to take so long and we had no luck finding a document that shows what tests need to be performed on which products. Not sure one really exists, so we may have to make a sample from a product that's been used and get clearance to post it.

Thanks for the help.

[Just_Some_Guy]

I realized I worded the first sentence wrong. We still have the same number of tests, but since we have a lot of many-many relationships, the junction tables have a lot more records. So Test 1 might show up 3 or 4 times in the junction table, but it will be associated with a different Flex every time. (Dual primary keys for the junction tables). Hope that clears it up.

[rpeare]

I don't see a question here but what you're saying is that not all ams IP's will have flex IP's this means you don't truly have a tiered structure. I would really love to know what the discussion was .

It sounds to me like you have tests that are related to AMS IP's and Tests related to FLEX IP's. All the trouble you're facing in defining the problem from there relate to wanting to assign multiple tests to multiple flex IP's but without knowing the ins and outs of your business my guess is that you should have a one to many relationship between FLEX IPs to tests. For instance if you have groups of tests depending on the material you could disable or enable tests for that particular item and not have the overlap. What you're proposing with the many to many relationship is going to make choosing a FLEX IP for a particular part very trick.

Let's say you have three flex IP's

flex IP 1 has tests 1 through 5
flex IP 2 has tests 5 and 6
flex IP 3 has tests 6 through 10

now let's say you have a new part and you really only want to perform test 5 and 6 you could end up with someone choosing flex IP 1 and flex IP 3 and disabling tests 1 through 4 and 7 through 10 instead of just choosing flex IP 2 which would meet their need.

On top of that you're talking about getting excel involved which you don't need to do, access is capable of doing whatever you need and I would strongly suggest you stay away from exporting information if your goal is to then import that information back into access. That's an extra step in complexity that you do not need.