Any way to use logic & procedural decisions in database design? 2

[Griphus]

Hello and thanks for any help.

I am trying to build / fix a database of projects for a manufacturing company. Each project is for one or more parts with model numbers. Those model numbers are set up such that the acceptable characters later in the number are based on the choices earlier in the number.

For example, say we have options 1, 2, & 3. If someone choose option 1, later they may be able to choose A & C, but not B. However, if they choose 2 or 3, they could then choose C.

Graphically, the logic can be drawn pretty easily. However, I'm struggling with finding a way to implement that in a database. Any advice would be very much appreciated!

Thanks,
Timothy

 

[MajP]

The answer is yes, but you need to provide real detail to this with a real example. Some possibilities are cascading comboboxes (choices in subsequent combos are based on previous combo), data validation on fields, data validation on character input. Please describe in real detail how you would like this to work.

 

[dhookom]

I would store the information in multiple fields and use some logic in cascading combo boxes. You can always concatenate the separate fields to derive a model number.

Duane
Hook'D on Access
MS Access MVP

 

[Griphus]

Awesome - thank you.

I shied away from the real detail because it's a little gory and didn't want to overwhelm people.

Let me think about the best way to communicate it on here... this might be a little goofy, but I'll attach the model index with the proprietary information redacted.

I was basically just handed this and am still trying to sort it all out myself.

I take that back. MediaFire is blocked at work. I will email it to myself and upload it tonight in a new post when I get home from work.

Thanks again for your help & interest.

-Timothy

 

[Griphus]

Here we go, I've uploaded it to my Google Drive.

 

[dhookom]

All file storage is blocked at my work.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I looked at it, but not sure what it says. It appears as if a product number is composed of 4 digits. Product Line, Type, and then 2 for representing the Description. If that is the case I do not see any logic, can you explain the logic? It appears for the 1st digit the choices are D,E,G,I,M,T. For the second A,B,D,E,K,P,R,S,V. For the Third C,E. And for the 4th R,T,G,M,9.

My guess is simply have combos for each digit and the field would concatenate the combos.

 

[Griphus]

Hi there - thanks for looking at it.

An example of what I was talking about using the "product line," which can be D, E, G, I, M, or T and the "Type" which has a bunch of other letters:

You can have a "GA__," but not a "DA__." Similarly, you can have an "ID__," but not a "DD__." So, I'd be looking to disable certain selections in later combo boxes based on selections in earlier combo boxes? I can probably look up how to do that.

Let me know if you have any follow-up questions or anything - if not, thanks so much for your help.

 

[dhookom]

Computers don't understand:

You can have a "GA__," but not a "DA__." Similarly, you can have an "ID__," but not a "DD__."

PCs best understand data in tables that store the appropriate "can haves". Do you have tables that store your business rules? Keep in mind that data belongs in your tables and not in your code.

Duane
Hook'D on Access
MS Access MVP

 

[Griphus]

Do you have tables that store your business rules? Keep in mind that data belongs in your tables and not in your code.

I agree with you regarding data storage philosophy, but no, essentially I'm dealing with a giant flat-file that someone put in Access and I'm trying to turn it into a proper relational database.

 

[dhookom]

Is there anything keeping you from creating the required tables?

Duane
Hook'D on Access
MS Access MVP

 

[Griphus]

No sir... I'm essentially building it from scratch as we "speak." Right now I'm still working on cleaning up all sorts of other inconsistencies in the data set.

 

[MajP]

I think I see it now. On the far right side is the list of all possible values. Some of the values have an underscore in the 3rd digit and I think that means it can be an E or a C. So I made a record for each value (e or c). If this is the case it becomes very simple. You simply make the data table that Duane is talking about that would look like:

tblProductCodes
[TT]
ItemDescription ItemCode
Magnetic Drive DB
Magnetic Drive DV
Magnetic Drive DVT
Synchronous Generator GAE
Synchronous Generator GAC
Synchronous Generator GBE
Synchronous Generator GBC
Synchronous Generator GBER
Synchronous Generator GBCR
Synchronous Generator GEE
Synchronous Generator GEC
....
[/TT]
the rowsource for the first combobox (cmboOne) is
"select Distinct itemDescription from tblProductCodes"
the rowsource for the second combobox is something like
"Select itemCode from tblProductCodes where itemDescription = '" & cmboOne & "'"

So if I pick Magnetic Drive, the only choices in the second combo are DB,DV,DVT.

 

[Griphus]

Hi MajP,

Regarding the first part of your post, that is exactly correct. The left column is a list of all the possible selections (51 of them). The blank can be filled in with a C or an E (don't know why he created it that way...).

I will work on creating the table you mention in the second portion of your post. It may take me a day or two to work on & figure it out - I am not an Access Pro. Thanks so much for your help with this.

 

[MajP]

I would hope creating the table (assuming my assumptions are correct) would take you only a few minutes with 10 or fifteen minutes to populate the choices. How you use it in the application may take a while. Once you make the table then come back with more questions. look on the internet for proper Access naming conventions it pays off in the long run. Some information

http://www.acc-technology.com/namconv.htm

http://en.wikipedia.org/wiki/Leszynski_naming_convention

You do not have to go overboard and you can tailor as you need. But be descriptive and do not use traps (special characters, spaces, reserved words)

Good
tblProductCodes (table of product codes)
tableProductCodes
tbl_Product_Codes
Bad
Product Codes
(Do not know if it a query, table, form etc?)
(Spaces bomb in code)

Good
cmboItemDescription (combo box for the item description)
combo_Item_Description
Bad
combo22 (no idea what it does)
combo 22 (bombs in code)
ItemDescription (what is it?)

 

[Griphus]

PCs best understand data in tables that store the appropriate "can haves". Do you have tables that store your business rules? Keep in mind that data belongs in your tables and not in your code.

I have a question about this statement. I understand what you mean philosophically, but not how to implement it.

As an easy example... say we have a motor, and the customer has an option to buy a cooler or not buy a cooler. Once they've bought a cooler, they can choose 0, 1, or 2 liquid level switches in it.

So, when filling out a form we wouldn't want someone to be able to select a liquid level switch if there is no cooler. Now, I understand you can do this within the form using VB, but is there some way to do it within the tables & relationships of the database? If so, that would be really cool.

 

[dhookom]

You can't do this in a table. You need to do this in a form where the Row Source of one combo box might be dependent on the value of a previous combo box.

Duane
Hook'D on Access
MS Access MVP