Search/Organization Recommendation

[nkasaba]

Since I'm unsure how to explain my situation in words, I took some screenshots to explain what I'm trying to do next. The purpose of this database is to store information about parts we will benchmark.


This is the switchboard I have created thus far. None of the buttons do anything except the 'input part', which takes the user here...


From here, the user will begin inputting vehicle/part information. Based on the combo box "Component" found on this form, it will select the appropriate sub-form for the user on the "Part Overview" tab. I found the easiest way to do this was to put 3 sub-forms on top of each other on the "Part Overview" tab and make the one necessary 'visible.' Here are the 2 of the sub-forms to give you an idea.




And finally, this is what I'm hoping to accomplish (I can draw it out but don't know how to do it!).



After using the Look-up by OEM or component, the user would get a list of the parts that have been tested and a link to information about that part. From there, I'd like to bring up an overview of the part that also encompasses links to our network where pictures and final reports are stored.

If I am thinking about this the wrong way in terms of how to do something like this in Access, let me know and I will try to find a different approach. This was just the first thing that came to mind as I was writing ideas down.

Thanks,

Nick

[patjones]

Hi,

Thanks for the thumbnails; they help explain your situation. I have a couple questions, the answers to which will hopefully enable us to help.

In the data entry part which you have already mostly put in place, it seems...I'm confused about the part where you say you have three sub-forms on top of each other. Is it because you have multiple kinds of parts (e.g. condenser, fan motor, radiator, etc.) and you will need a different sub-form for each type of part?

Now, for the lookup functionality, suppose you click on "Honda". It sounds like what you want is to display a list box with all the parts you have for "Honda"...a nd then be able to select a part from the list box to display the overview for that part (including picture(s), etc.)?

Just want to get our discussion going and talk things out a little bit to see if we can get the best design for your situation.

Pat

[nkasaba]

Hi,

Thanks for the thumbnails; they help explain your situation. I have a couple questions, the answers to which will hopefully enable us to help.

In the data entry part which you have already mostly put in place, it seems...I'm confused about the part where you say you have three sub-forms on top of each other. Is it because you have multiple kinds of parts (e.g. condenser, fan motor, radiator, etc.) and you will need a different sub-form for each type of part?

Now, for the lookup functionality, suppose you click on "Honda". It sounds like what you want is to display a list box with all the parts you have for "Honda"...a nd then be able to select a part from the list box to display the overview for that part (including picture(s), etc.)?

Just want to get our discussion going and talk things out a little bit to see if we can get the best design for your situation.

Pat

Pat,

Thanks for the quick reply. You hit the nail right on the head in terms of different sub-forms. A fan motor is obviously going to require a different set of information than a radiator will.

As for your second question, you are dead on again. The way I had imagined it (and tried to draw it) is basically a small table where when I click on Honda, I will see a list with every Honda part that has been tested, including the vehicle its from, model year, engine size - whatever my management deems important when searching. From there, a simple click to an overview page would round it out. This would go the opposite for say if I wanted to search for all radiators that have been tested - I would be presented with every radiator, the vehicle they are associated with, etc.

[NeoPa]

Nick,

This is impressively laid out, but it's unclear exactly what the question is.

We can't do the whole thing for you. Do you have a specific question?

** Edit **
Forget this. Pat has picked up the gauntlett so I'll leave it to you guys and just jump in if I can with anything specific.

[nkasaba]

Nick,

This is impressively laid out, but it's unclear exactly what the question is.

We can't do the whole thing for you. Do you have a specific question?

** Edit **
Forget this. Pat has picked up the gauntlett so I'll leave it to you guys and just jump in if I can with anything specific.

Neo,

This is why I did not just attach the database for you guys to dig through - I did not want to come off as trying to pawn off my work. I just need some idea as to where to begin. I have absolutely no idea where to start outside of some drawings I put down on paper.

[NeoPa]

I wasn't gonna hit you with a warning or anything as you were clearly not trying to palm off your work. You put some decent effort into doing it right and that always counts.

That said, not everyone is happy answering such questions as experience has shown they can be a poisoned chalice. It looks like Pat is prepared to lead you forward some way though, so good luck. I'll monitor too, and if there's something I can help with I will.

[patjones]

So eventually you will end up with lots more sub-forms for the part overviews...I think if this works for you, it is fine. Having never been in a situation quite like this, I can't say there is necessarily a better way of doing it.

For the lookup functionality, what I would do is combine all your buttons into drop-down lists (a.k.a. combo boxes). So you'll have a combo box that lists the car models on one side, and another combo box that lists part type on the other side - very similar to what you have now except much cleaner looking. Just below your combos, you'll have a list box.

With this in mind, suppose you pick Volkswagon from the car model combo; the list box will display all the VW parts in the list box. Double-clicking on a part in the list box can then take you to the parts overview sub-form which you have already setup. Similarly, picking a part from the parts combo would produce a list of instances of just that part...

Notice that I'm suggesting you use your parts overview tab for the lookup as well as the data entry. It shouldn't present many problems for you as long as you realize the distinction between data entry and lookup. For example, when doing a lookup, you might want to place limitations on what can/cannot be edited and enable/disable certain boxes on the sub-form appropriately, whereas when using the same form for data entry everything will be enabled.

The other thing I am interested in is your table structure. With so many parts, and so many pieces of different information for different parts, I'm curious as to how you have your tables put together.

Pat

[nkasaba]

So eventually you will end up with lots more sub-forms for the part overviews...I think if this works for you, it is fine. Having never been in a situation quite like this, I can't say there is necessarily a better way of doing it.

For the lookup functionality, what I would do is combine all your buttons into drop-down lists (a.k.a. combo boxes). So you'll have a combo box that lists the car models on one side, and another combo box that lists part type on the other side - very similar to what you have now except much cleaner looking. Just below your combos, you'll have a list box.

With this in mind, suppose you pick Volkswagon from the car model combo; the list box will display all the VW parts in the list box. Double-clicking on a part in the list box can then take you to the parts overview sub-form which you have already setup. Similarly, picking a part from the parts combo would produce a list of instances of just that part...

Notice that I'm suggesting you use your parts overview tab for the lookup as well as the data entry. It shouldn't present many problems for you as long as you realize the distinction between data entry and lookup. For example, when doing a lookup, you might want to place limitations on what can/cannot be edited and enable/disable certain boxes on the sub-form appropriately, whereas when using the same form for data entry everything will be enabled.

The other thing I am interested in is your table structure. With so many parts, and so many pieces of different information for different parts, I'm curious as to how you have your tables put together.

Pat

Pat,

That's a great idea! If I have anytime to work on this at home this weekend, I will try and let you know how it goes.

As for tables, I made a TON. I learned on my first failed attempt at making this database that tables are awesome - as long as you don't use the damn lookup wizard!. I basically have 4 main tables - one for each form and sub-form all linked by 'Benchmark ID.' After that, I have a bunch of tables which hold vehicle make/model info, part type info, etc. I made a table everywhere I could!

[patjones]

OK. I asked because it is obvious that with all the different types of parts, you'll have lots of data that needs to be organized in a sensible manner. I suggest taking a look at one of the most popular "how-to" articles here in the Access forum:

Database Normalization and Table Structures

These are general database design principles applicable to any platform you work on, not just Access.

Pat