Advanced Search...Trouble..

[mission2java_78]

I posted this topic a while back and Im still stumped as to how to approach this. Basically..one of the departmental bosses in our business development groups wants this so called "advanced search" to help our reps prepare proposals a lot simplier for assembly lines. See we basically sell to the big 3 assembly lines...and our business personal quote and price out the actually machinery etc to the customer. Basically a proposal has any number of transports, stations and pallets (four tables here: Proposals -> Transports (many side), Proposals -> Stations (many side), Proposals -> Pallets (many side). Fine and dandy...
But each transport, station, or pallet can have any number of Components. So 3 more tables (Transports->TComponents (many side linked by a transportID), Stations->SComponents (many side linked by stationID), and finally (Pallets -> PComponents (many side linked by palletID). The structure so far is fine and dandy...now comes the hard part...

See when an estimator create say a transport he has to select components that are needed to build this transport. I will have a table holding EVERY component...and there are a lot of them. Each component Im guessing I should categorize and give a specific type (Type of Component could be: Pallet, Station, Transport, Other) (Category could be: Conveyors, Drives, Stops, Shafts etc).

Now lets say the estimator is creating this transport...so he wants to add a conveyor...so he needs to search for the right conveyor...
So first he has to select the category conveyor..then we have to choose an item type (KSR, FR, VFR), then after that select its item type (CZ, BZ), then select a width (312, 412, 512), and finally select a Pitch (100, 166, etc). The problem is..not all components have these commonalities...these could be specific to say Conveyors and Drives...but totally different to stops. I dont know or have any idea to set this up so that users can base a search on ANY component.

The whole intent of this is that estimators are wasting way too much time using excel and word and we needed an application..so far the app is fine...but the problem I am on is holding the users hand in the tool and helping them select the right component. I mean its almost like the application is interviewing them to find out which component they need. I imagined just creating one table with all these components and leaving some fields as null for those components that did not apply...but im not sure??!?!?! Basically I want a bunch of combo boxes saying first what do you want a component type (Pallet / Station / Transport / Other), then a category...and from there the category will determine what other fields will be needed for the search. Should I keep this idea of one large table for all components? Also I will definately need tables to hold the category, pitch, item width, and item, along with all the lookup tables for these right?


Below was my original idea for say a conveyor in a word document
-------------------------------------------------------------------------------------------------------
Break Down into the following: Type, Categories, Item, SpecificItem, Width, Pitch.

Type
Key Type
1 Transport
2 Pallet
3 Station

Categories
Key Category
1 Conveyor
2 Drive
3 Display

CategoryByType
KeyForType KeyForCategory Type Category
1 1 Transport Conveyor
1 2 Transport Drive
…..
2 3 Station Display


This shows that a category is associated with a type.

Then the next table should associate a Category to an item. Item will have its own separate table. First we have the items table:

Items
Item Key Item
1 KSR
2 FR
3 VFR

Then this item is associated with a category with a table ItemByCategory

ItemByCategory
KeyForCategory KeyForItem Category Item
1 1 Conveyor KSR
1 2 Conveyor FR
1 3 Conveyor VFR

Then we associate an item with a specific item with a table of specific items and a table of SpecificItemsByItem

SpecificItems
Specific Item Key Specific Item
1 CZ
2 BZ

We associate the specific item with the parent item.

SpecificItemsByItem
Item Key Specific Item Key Item Specific Item
2 1 FR CZ
2 2 FR BZ

Now we want to associate a specific item to a width so we have a Width table followed by a SpecificItemWidth table.

Width
Width Key Width
1 312
2 542
3 702

We associate a width with a specific item.

SpecificItemWidth
Specific Item Key Width Key Specific Item Width
1 1 CZ 312
1 2 CZ 542
1 3 CZ 702

Then we associate a specificitemwidth with a pitch.

Pitch
Pitch Key Pitch
1 100
2 166

We associate a pitch with the specificitemwidth with a table

SpecificItemWidthPitch
SpecificItemWidth Key Pitch Key SpecificItemWidth Pitch
1 1 312 100
1 2 312 166

--------------------------------------------------------------------------------------------

I know this is a very long and confusing problem but if anyone has a suggestion for me...as Ross Perot would say, "I'm all ears!"

Thanks,
Jon

 

[mission2java_78]

To clarify a bit...

So my components table would start out like so:

ID DataString Type Category
1 T1000 Transport Conveyor
2 S5000 Station Stop
.....
....

But the problem is here...that each category has so many different characteristics..like with conveyor I have to worry about size, pitch, etc. With stop i have to worruy about manufacturer, etc. Different atributes...I dont know if I should just add all these fields to the one table and leave them as NULL values if they do not exist for the category.

Jon

 

[mission2java_78]

Hmm I guess I might just be SOL on this...

Calling Doc Calling Pat..do you read?

 

[The_Doc_Man]

M2J, I read you loud and clear but you have a very difficult problem. This issue is actually two or three issues at once. Not only that, it is, like, master's-thesis level complexity.

Technically, your problem isn't really best suited to Access (which failing never stopped me before in attacking something with Access, you understand.) What you have is a decision tree something like the old computer game I used to play on PDP-10s, "Guess the Animal." It was capable of learning, but maybe you don't want that much smarts. Just the ability to find stuff that is properly defined. The only REAL way I know to approach this problem is to first build (on paper) the actual, fully explored, black-and-white decision tree of ALL the parts you can specify and the list of questions you have to ask to get there.

Next, you have to examine that tree to see if there is a way to normalize it. Normalize = put all things with common attributes (regardless of other factors that would normally isolate the items) on the same branch, no matter how deep that makes the deepest possible branches. I.e. if you can get to "conveyor belt" questions from two different ways, you shouldn't care how you got there as long as you ask the right questions.

Now, to implement your decision tree, you need a structure that says something like this:

tblAnswers:
-- Answer_Group_Id: Long
-- Answer_ID: Long or Integer or Byte based on max possible number of answers to any single question.
-- Answer_Text: xxxx xxxx xxxx (Memo or fixed text, your call) the possible answer to the implied question.
-- Answer_Help_ID: Long (FK to tblAnswer_Help)
-- Answer_Part_ID: xxxxxx (text - probably don't need a memo field) This identifies the part number you want.
-- TerminalAnswer: (Boolean) Yes/No
-- Leads_To: Long (FK to tblDecideTree Question_ID)
.... and PK is compounded from Answer_Group_Id, Answer_ID.

tblAnswer_Help:
-- Answer_Help_ID: Long (PK)
-- Answer_Help_Text: xxxxx xxxxx xxxxx xxxx (Memo)

tblDecideTree
-- Question_ID: Long (PK)
-- Question: xxxxx xxxxx xxxxx ? (Memo)
-- TerminalQues: (Boolean) Yes/No
-- Answer_Group_ID: (FK to tblAnswers)

OK, here's the trick. This CANNOT be handled with a normal form. Has to be unbound.

You make a form that starts with the first question, whatever it is, that decides for you what general kind of part you want. You find the starting question in tblDecideTree, which is probably a constant.

Your form does a little bit of VBA behind its form-current event to load the question and find the possible answers to that question in your tblAnswers. (Basically, all possible answers to the question with the given Answer_Group_ID.) For instance, you might find that question 1 condones answers such as "Animal" "Vegetable" "Mineral" when it asks the question "What kind of thing did you want?"

So you display a little form as a popup that displays the question text and in a sub-form list, one row per answer, shows all possible answers to that question. Your user checks one of the answers. Perhaps you have a button on each answer row of the popup form where the user can ask for more data on that answer (as a modal message box or another pop-up form, your call), so in that case you can follow the Answer_Help_ID linkage to another table that has a longer description about whatever it is.

OK, so what next? You now have a question and, once one of the boxes is checked, you have an answer. For each check-box, the click routine has to do the same thing.

You check to see if the answer is enough (Answer_Terminal = TRUE) to specify what you really wanted to know. In which case the Answer_Part_ID tells you the part info. If the answer is not enough (Answer_Terminal=FALSE), the Leads_To field takes you to another question. So you repeat the whole process over again, asking another question and processing the user's selected answer. Probably involves more than a little bit of VBA, but really only two or three recordsets in the selection process.

Eventually you get to a terminal answer that identifies your part in the Answer_Part_ID. BUT if you get to a terminal question node first (Question_Terminal), the question isn't a question. It's a message to tell your user that s/he has gone down a garden path that leads nowhere.

Now, note that I did not use the Answer_Group_ID as equal to the Question_ID. This is because, as I mentioned earlier, you don't want to limit yourself. You might wish to "cross branches" at some point when you realize that what you are leading to is really on another branch.

If you have fully explored your tree, there will be NO terminal questions - only terminal answers. But over time, you might find yourself forced to build the occasional dead-end. In particular, if you implement an answer group that includes "none of the above" as a possibility, it might lead to a terminal question node that says "Call Mr. Smedley in Engineering for help on this part." Or something like that.

In closing, my friend, beware of asking for what you want. You might just get it.

 

[mission2java_78]

Yep...I knew something like this would happen. I completly understand where you are heading with this. However its really not a form of asking questions..its basically a selection (combo boxes or what not). My main problem exists in probably my second post. See I can label a component as being a transport, a station, a pallet, or other. Then I can provide a category for the component such as:

Transport Conveyor
Transport Belt
Station Stop
Station turntable

...

So basically I have the type (Transport / Station / Pallet / Other) and the Category (Conveyor / Belt / Stop / TurnTable etc)...now here is the main problem..a conveyor has many more different attributes than a belt. For instance a Conveyor could have a width, a pitch, a brand etc. All different attributes as opposed to a belt which might only have a length associated with it. This is where my problem comes into play..different categories have different attributes. I really want to keep all components in one table...I do NOT want to seperate the tables and Unionize them..this was not the ideal solution from the get go. As you have said this is not an access problem (in fact this is all vb and sql server..I came in here to pick the minds of the great). So my problem is breaking up the categories...for each category / component. I need to be able to provide searching though VERY specific to that category component..in the case of conveyor I will need to search for a SPECIFIC conveyor by allowing the user to select a pitch, a width, a brand..etc...but for instance to search for a belt I do NOT need to specify all of this information..I only need to specify say a height.

Thanks,
Jon

 

[mission2java_78]

What about a search form that has a bunch of combos, mostly hidden. As the user refines his criteria, different sets of combos become available. The combos can reference the contents of other combos (as in the cascading examples) to filter their contents. When the selection criteria is fininshed, create the where clause based on the combos that have criteria.

Pat...
I have no problem with the interface...this in fact was my original idea. The problem lies with the following. Please bare with me since I may go into some deep boring detail.

Lets look at the top level..we have a components table:
Components
[ID] [Component]
1 Transport
2 Station
3 Pallet
4 Other

We then have a simple table which lists the categories available:
Categories
[ID] [Category]
1 Conveyor
2 Stops
3 Belts
4 Robots
5 Divert
...........

We then have a table which distinguishes which categories go with which component For instance:

CategoryByComponent
Transport Conveyor
Transport Divert
Station Stops
Station Robots
Pallet Belts
................

Above for simplicity I have listed the text rather than the key values. So basically we have a component can have a many side for a category...

Now is where the problem starts, and it could be because im missing something. See lets take a conveyor for example..a conveyor has attributes which include: Model, Size, Width, Brand, Pitch, etc. While a divert may not have any of these but may have say just a manufacturer...so in my form when searching for a conveyor I should be given a way to select a model, a size, a width, etc...BUT for a divert I should only have a combo box to select a model. now again interface wise this is simple...BUT my problem is in the database tables...where do I break off from here? What table(s) can I create to go back to many different attributes for each thing? I dont know how to continue my tree since all these attributes may be different. I have attached a word document which explains how conveyor is broken down.

Hopefully it will help you guys see what level a conveyor goes BUT that it does not go on the same path for other categories.

Please ask questions if clarification is needed.

Thanks,

Jon

 

[mission2java_78]

Im guessing all of those in the word document and as stated in my previous reply are all small tables. But my problem is how do I get back to the right component after all the criteria has been selected? I mean Im guessing I need a table that lists EVERY component..and points back as foreign key to the item, size, width, height? All these as foreign keys? But what if the component is say a divert and only one of these foreign keys exist? Are the rest considered NULL? Im very lost here...and open to ideas.

Its kind of like a shopping cart at a best buy / office depot / office max. You select computers, then you select desktop, then you select pentium, then you select pentium 4...etc and so on.

But how do these web apps go back to THAT specific product...for instance how did they end up narrowing in on an HP computer after all of these selections?

It makes sense but more difficult than easy...especially since all of these things break up into smaller tables. My main concern is the main table with all the components and going back?

Jon

 

[chewy]

mission,
Sorry to be off topic but what do you do for a living? Are you a professional database designer?

 

[mission2java_78]

Nah,

Far from a database pro. I work with databases inline with what I do. Mainly I work with C++ and embedding java...very minimal web related apps but I've done a few PHP apps. YOu'll notice at times Ill be in the database forums for a good period of a week or two..then you wont see me for 3 weeks..etc etc. It basically means something hit the fan here...I work in the auto industry so things are on the up and down all the time. One week you work 70 hours the next you work 50...strange isnt it .

My title here is Service Software Engineer cause I work a lot with assembly line systems...but as I said I've always enjoyed the power of databases so I'm kissing boss butt and streamlining our processes by getting rid of word and excel docs and converting to full blown visual basic applications.

Other than that I'm a toilet "Jon".

Back to topic.

Jon

 

[The_Doc_Man]

But how do these web apps go back to THAT specific product...for instance how did they end up narrowing in on an HP computer after all of these selections?

They build a decision tree, fully explored, like I suggested, or by building custom pages. The way that the info is implemented is a matter of the page design code you use. Java, HTML, something else... all of them have to elaborate on a list of possibilities and get you to choose something to narrow the list. But they had to design the elimination.

We won't really be able to do a lot for you. The old programmer's rule is, "If you can't adequately describe the problem, how will you ever describe the solution?"

The drill-down process can either be tree-oriented or partial-key-matching oriented. You can do it recursively or by explicitly creating sub-forms for each layer. But in the final analysis, you have to define a key that gets you to a unique item some way.

So perhaps it is time to step back from the problem and look at it globally. What is the business method here? You are asking Access to support a model of how these persons generate their quotes. Do you know every step of the process they use? Please don't take that as a smart-a$$ question. If you cannot diagram, draw, or otherwise define this process, you will never be able to program it.

 

[mission2java_78]

Hello Doc,

Completly agree with what you have said...and I realize not knowing the business logic or the current method of creating a proposal would kill the whole purpose of creating the application. Its like chewing gum , walking, and closing your eyes all at the same time hoping to get from source A to some destination correctly.

However, I completly understand their process and have worked inline with these guys for weeks on creating a proposal using the old method of excel and word. The answer to this is a serious of questions which again is not the problem I am encountering..in fact my intention was basically some combo boxes, listboxes, etc which would narrow down the search. My main problem as I have stated before is whether or not all of these different categories should have their own tables SINCE each category has a different number of attributes. To add on to that a major concern is should there be a primary main table listing EVERY component with all of these "small tables" all as foreign keys? I mean how else will I be able to pull back the correct component?

Thanks again,
Jon

 

[The_Doc_Man]

My main problem as I have stated before is whether or not all of these different categories should have their own tables SINCE each category has a different number of attributes.

This question at least strongly implies the solution to your real problem. That's why I suggested a tree method. It only goes as deep as it needs to go in order to get the data that defines the exact product you need. If you have 2 attributes, you might get along with 2 questions. If you have 30 attributes, you might need 30 questions. A tree-structured method "doesn't care" how many questions are required. It stops when it reaches an end-point. Until then, it keeps on asking questions.

I'm hammering on the "tree" idea because in theory, when you have a variable number of questions to ask, whether you are consciously thinking of the problem this way or not, you are implementing a rudimentary tree. Whether it is a series of web pages that branch via hyperlink to other web pages that branch via hyperlink to other web pages etc.etc.etc. or whether it is a program that branches to another piece of code that asks a question and uses the answer to branch to another piece of code etc.etc.etc.

In other words, this is the same logic as the game I mentioned or a chess look-ahead algorithm or any other method that has to choose from among a large number of possibilities and doesn't give you all the choices at once.

Even if you don't implement the tree-question algorithm I suggested, your ANALYSIS phase might benefit from thinking along these lines. In particular, it might help you identify cross-links and jump-down-the-branch cases as such. In other words, short-cuts and approaches to minimize the questions you have to ask. Whether you implement the questions as drop-downs or pop-up forms is immaterial. Both offer you choices. Both take you to new options based on previous choices.

Now, I'll offer another thought. This might help you build what you want.

You have a list of items with different numbers of selection criteria. As you asked earlier, you probably DO need to have a mode of presentation where everything looks the same even if they come from different tables and have different types of keys.

What about a UNION query that presents all parts from all tables, where the tables with the most criteria have all criteria presented? And for tables where fewer criteria exist, the UNION query presents a constant for that selection criterion.

So present your combo boxes in the order of selection criterion appearance in the UNION query's virtual record. When you make a selection from the first criterion, build a virtual list of choices for the second criterion. (The topic in this forum is "cascading combo boxes". You should get several hits from Pat on that topic.) Keep on going until you have eliminated everything that doesn't match your sequence of criteria.

Now, on the combo-box by combo-box transition, you need to do two things. First, remember the current box's choice. Keep it somewhere. Now before you bring up the next combo, do a query based on a "SELECT UNIQUE" of the next criterion's choices where the value from the first box is part of the criterion for this "SELECT UNIQUE" query. If there is only one unique value for that next combo box with the current criterion, you have hit one of the "supplied constant" fields from one of the UNION members with fewer criteria than the maximum. In that case, you find that criterion and keep track of it, too. Then step to the next possible criterion and go again.

Do this until you reach the last criterion. When you are finished, you have either selected a part or it just flat don't exist.

 

[mission2java_78]

I dont mean to be a stone head here...


but again..I understand the process of asking questions and criteria and so on..but this still leaves the problem that I have. Does anyone agree that eventually I need a table with ALL the components in it? If so...then what are the foreign keys if each category has different attributes. I dont know who has looked at the word document but thats the structure I was looking at for say a conveyor..it splits into those tables. But how do I narrow in on the part .... surely I have to do a select stmnt at the end..but I need to base it on all the criteria..doesnt that imply all that criteria needs to be foregin keys in this huge table?

Component 
   +
    |       Category
    |        +
              |
    ----Stations  (Component Type)
    |         |------Stop
    |         |------Robot
    ----Pallets
    |         |------Divert            -------------> size, manufacturer
    |         |------Belt             -------------> has brand only
    ----Transports
    |         |------Conveyor       ------------> has width, pitch, item, brand
    |         |------Elevator         ------------> has height only
    ----Other
              |------Stop    
              |------Nutrunners

Its very difficult to explain...but each one of the categories has a set of different attributes...
and im still not understanding how to end up with the part the user wants.

Let me know if you have more questions...I know im not completly giving you good or clear info...its very hard to put it down in plain old words.

Jon

 

[The_Doc_Man]

I agree you need a presentation that gives you all possible selection keys. But read again that part I wrote about UNION queries as a way to equalize the records in a way that looks like the table with the greatest number of selector fields.

You use the first combo box to eliminate a bunch of things. From what is left, dynamically build a query to allow you to populate the second combo box only.

Then use the second combo box to eliminate more things. Use the selections from the first and second boxes to build a query based on the third selection criterion only.

For any situation where the subset of the table you are in has no more choices, you have to count the potential choices and skip that combo box if you have eliminated all but one choice within that box.

 

[mission2java_78]

Based on what you have said everything seems ok.

It always comes down to however ...though...I still do not see a possible solution to my table which has all the components listed. I understand how to create all these small tables used to do searching and lookup as criteria for my component. However I am still pondering on how my table will look that has all the components. Anyone have any clue on what fields are necessary..the reason I ask goes back to the different, and im always going back to this because i still am not sure why or how to implement this, attributes for each component.

Lets say I have a table of all my components

Components:
ComponentID
Component
EnglishDescription
GermanDescription
ComponentTypeID (Can be transport, station , pallet, other)
CategoryID ( based on the component type id this can be a category based on the component type...meaning there is a lookup table that holds a CategoryID with the ComponentID)
____________
_____________

the "__________" refers to my NOT understanding what foreign keys are needed because of the different attributes. Again this refers to that word document. Lets say I have a conveyor with all those attributes listed on the file I had uploaded on my previous thread. Would there need to be foreign keys to all those tables in this main table? If so...what if the component does not have ALL of those attributes...say for instance a divert...what would happen to all those foreign keys. I'd rather someone help me out with some table structure than the theory. I know the latter helps the former..but in my case it is not helping at all. I can understand what they want..I cannot put it into table or technology terms. the last thing on my mind is interface..right now I want to get the layout correct.

Someone can push me a little further on what Im looking at for a main table and that should get me going...its as if im missing something simple entirely.

Thanks,
Jon

 

[The_Doc_Man]

I'll try to spell this out.

Let's say you have your selection tables in categories: transport, station , pallet, other

Lets say that the tables really are called tblTransport, tblStation, tblPallet, tblOther

But in order to fully specify tblTransport you need 5 things. To do tblStation you only need 4. To do tblPallet you only need 2. To do tblOther, you need 3 things.

OK, build a union query on those four tables.

SELECT PrtNum as PartNumber, Description as Descr1, Selector1 as Sel1, Selector2 as Sel2, ...., Selector5 as Sel5, "Tnspt" as ItemType FROM tblTransport

UNION

SELECT Pnum as PartNumber, WhatItIs as Descr1, Ident1 as Sel1, CStr(Ident2) as Sel2, " " as Sel3, .. , "Sta" as ItemType from tblStation

UNION .... etc.

There are six keys in this table. They are Sel1, Sel2, Sel3, Sel4, Sel5 and ItemType.

Now build a form based this way.

First combo box is based on a fixed series of choices: Tnspt, Sta, Plt, Other

When someone selects one of those, you do a query on the union query to select unique choices for Sel1 from the union query of item-type as selected. Remember the item type. If the count of unique choices is 1, get the (only) unique choice and store that as Sel1. Remember it. Otherwise wait for (and remember) the selection from the list you have presented. Go to the next selector.

Now in that next selector based on ItemType and Sel1, show only the unique choices for Sel2. If THAT is 1, get the only choice and go on to Sel3. Otherwise, wait for and remember the selection.

Eventually you will build a selection query that looks like

ItemType = "xxxx" and SEL1 = "yyyy" and SEL2 = "zzzz" and SEL3 = "wwww" and ....

Doing this from the UNION query instead of directly from the table allows you to even change the format of something that might not be in the same format. (Note the CSTR function in the portion of the union query that pulls up station data.) This way, the format of the item is always knowable. You could even do a join to a lookup type table if you have a really wild-eyed option and wanted to simplify the item in the UNION query.

Is this ringing a bell yet? Did the light switch on? Do you see where this is going?

 

[mission2java_78]

I went against all odds and took a different approach.

Got it working now. I decided since I did not want to deal with UNION queries..and the entries were really related to one another I decided to use a self joining table.

Everything looks to be working very well.

Thanks all.

Jon