Any way to use logic & procedural decisions in database design?
Any way to use logic & procedural decisions in database design?
(OP)
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
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
RE: Any way to use logic & procedural decisions in database design?
RE: Any way to use logic & procedural decisions in database design?
Duane
Hook'D on Access
MS Access MVP
RE: Any way to use logic & procedural decisions in database design?
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
RE: Any way to use logic & procedural decisions in database design?
RE: Any way to use logic & procedural decisions in database design?
Duane
Hook'D on Access
MS Access MVP
RE: Any way to use logic & procedural decisions in database design?
My guess is simply have combos for each digit and the field would concatenate the combos.
RE: Any way to use logic & procedural decisions in database design?
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.
RE: Any way to use logic & procedural decisions in database design?
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
RE: Any way to use logic & procedural decisions in database design?
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.
RE: Any way to use logic & procedural decisions in database design?
Duane
Hook'D on Access
MS Access MVP
RE: Any way to use logic & procedural decisions in database design?
RE: Any way to use logic & procedural decisions in database design?
tblProductCodes
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
....
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.
RE: Any way to use logic & procedural decisions in database design?
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.
RE: Any way to use logic & procedural decisions in database design?
http://www.acc-technology.com/namconv.htm
http://en.wikipedia.org/wiki/Leszynski_naming_conv...
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?)
RE: Any way to use logic & procedural decisions in database design?
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.
RE: Any way to use logic & procedural decisions in database design?
Duane
Hook'D on Access
MS Access MVP