If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Attempting to design database around Excel datasheet design
Posting question as last resort - been using this forum for long time with
wonderful results - great job people. Bleery eyed search - finding no viable solution. Labor Time Guide - Problem: (I guess I would call this a ) 3 dimensional array in an Excel sheet, with the cell formula being "=if(A1 = "A", "number",if(A1 = "B", "number",if(A1 = "C", number, "number not found") "A1" being equal to a user selected list box result (A-Z), which results in a cell value equal to a labor time ("number"). "Machine" (ModelIndex) has many "Systems" / "Systems" can belongs to man different (ModelIndex) "Machines" "System" has many "Groups / "Groups has only one "System" "Groups has many "Components" / "Components" has only one "Groups (What you can DO to this component) "JobCode" is a predefined code list (A-Z) applicable to ALL machines (ModelIndex). Not every component uses ALL "JobCodes" (Hence the formula to calculate only the "A1" "JobCode" that pertains to the component, else "Not apllicable" message) Excel Array: Left column lists ALL possible components on ALL (ModelIndex), which is a combination of selected "System|Group|Component" (no "nulls"). "Header column" lists ALL possible Machines (MachineIndex). Not all Machine use ALL components (many "nulls"). What I visualize here is a table for every ""JobCode" (A-Z), with the fields being: Table "JobCodeA" - ComponentCode | MachineCode | LaborTime Table "JobCodeB" - ComponentCode | MachineCode | LaborTime etc, etc... Massive redundancy, massive "nulls", every table would inculde ALL components and All Machine Codes... Hmmm. (not very efficient) The only difference in the tables would be the time entered, based on the table name. Must to be a better way. The tables for selecting the model of machine, the system, the group, and the component went well, but populating records with LaborTime is based on JobCode, which is where I'm stuck. Excel allowed me to put a formula (above) in "LaborTime" cell, where I could display "LaborTime" based on "JobCode", using only the JobCode that pertained to THAT component on THAT machine. (No redundancy, some (acceptable) "nulls"). Labor Times may change in the future (Table Update). Requested expansion to many other machines. (Migrating to DBMS from Excel). LaborTime table design? Duh...dazed and confused. (Sorry this ended up being so long) "Subject should be a brief, meaningful summary of your question or comment" Thoughts? |
#2
|
|||
|
|||
Attempting to design database around Excel datasheet design
"Machine" (ModelIndex) has many "Systems" / "Systems" can belongs to
man different (ModelIndex) "Machines" solution: three tables, as tblMachines MachineID (primary key) MachineName other fields that describe a specific machine only tblSystems SystemID (pk) SystemName other fields that describe a specific system only tblMachineSystems MachSysID (pk) MachineID (foreign key from tblMachines) SystemID (fk from tblSystems) other fields that describe a specific machine and specific system combination relationships are tblMachines.MachineID 1:n tblMachineSystems.MachineID tblSystems.SystemID 1:n tblMachineSystems.SystemID "System" has many "Groups / "Groups has only one "System" solution: add another table, as tblSystemGroups GroupID (pk) SystemID (fk from tblSystems) other fields that describe the group for a specific system relationship is tblSystems.SystemID 1:n tblSystemGroups.SystemID "Groups has many "Components" / "Components" has only one "Groups solution: add another table, as tblComponents ComponentID (pk) GroupID (fk from tblGroups) relationship is tblGroups.GroupID 1:n tblComponents.GroupID (What you can DO to this component) "JobCode" is a predefined code list (A-Z) applicable to ALL machines (ModelIndex). solution: add another table, as tblJobCodes CodeID (pk) CodeName other fields that describe a job code only Not every component uses ALL "JobCodes" (Hence the formula to calculate only the "A1" "JobCode" that pertains to the component, else "Not apllicable" message) solution: add another table, as tblComponentJobCodes CompCodeID (pk) ComponentID (fk from tblComponents) JobCodeID (fk from tblJobCodes) other fields that describe a specific job code and specific component combination relationships are tblComponents.ComponentID 1:n tblComponentJobCodes.ComponentID tblJobCodes.JobCodeID 1:n tblComponentJobCodes.JobCodeID What I visualize here is a table for every ""JobCode" (A-Z), with the fields being: Table "JobCodeA" - ComponentCode | MachineCode | LaborTime Table "JobCodeB" - ComponentCode | MachineCode | LaborTime etc, etc... no, no, bad idea. Excel utilizes a flat file design. Access was specifically created to support a *relational* design. the two structures are very different, so if you try to impose a flat file structure on Access, you're going to lose much of the power of the software and have a horrendous time trying to develop an application that works right. Massive redundancy, massive "nulls", every table would inculde ALL components and All Machine Codes... Hmmm. (not very efficient) not at all efficient, and a nightmare to develop, maintain, and expand as needed. Excel allowed me to put a formula (above) in "LaborTime" cell, where I could display "LaborTime" based on "JobCode", using only the JobCode that pertained to THAT component on THAT machine. (No redundancy, some (acceptable) "nulls"). Labor Times may change in the future (Table Update). Requested expansion to many other machines. (Migrating to DBMS from Excel). LaborTime table design? Duh...dazed and confused. you haven't provided enough information about how LaborTime relates to the rest of the data - is each value related to a JobCode only? or does the value vary based on other variables as well as (or instead of) the JobCode? so i can't suggest anything here. also, you began by using the term "Machine" as seemingly interchangeable with the term "ModelIndex", but much later used the term "Machine" and "Machine Code" as seemingly interchangeable. so i'm lost; i don't know if you're using different terms to describe the same entity, or if you're describing distinct entities with each term. at any rate, i strongly recommend that you read up (or read more) on relational design principles. i think you're going to be the best person to model your process, once you understand how to do it correctly for a RDBMS. for more information, see http://home.att.net/~california.db/tips.html. suggest you read at least the first two tips, though you'll find the next seven helpful as well when you begin building the tables/relationships structure. hth "CBartman" wrote in message ... Posting question as last resort - been using this forum for long time with wonderful results - great job people. Bleery eyed search - finding no viable solution. Labor Time Guide - Problem: (I guess I would call this a ) 3 dimensional array in an Excel sheet, with the cell formula being "=if(A1 = "A", "number",if(A1 = "B", "number",if(A1 = "C", number, "number not found") "A1" being equal to a user selected list box result (A-Z), which results in a cell value equal to a labor time ("number"). "Machine" (ModelIndex) has many "Systems" / "Systems" can belongs to man different (ModelIndex) "Machines" "System" has many "Groups / "Groups has only one "System" "Groups has many "Components" / "Components" has only one "Groups (What you can DO to this component) "JobCode" is a predefined code list (A-Z) applicable to ALL machines (ModelIndex). Not every component uses ALL "JobCodes" (Hence the formula to calculate only the "A1" "JobCode" that pertains to the component, else "Not apllicable" message) Excel Array: Left column lists ALL possible components on ALL (ModelIndex), which is a combination of selected "System|Group|Component" (no "nulls"). "Header column" lists ALL possible Machines (MachineIndex). Not all Machine use ALL components (many "nulls"). What I visualize here is a table for every ""JobCode" (A-Z), with the fields being: Table "JobCodeA" - ComponentCode | MachineCode | LaborTime Table "JobCodeB" - ComponentCode | MachineCode | LaborTime etc, etc... Massive redundancy, massive "nulls", every table would inculde ALL components and All Machine Codes... Hmmm. (not very efficient) The only difference in the tables would be the time entered, based on the table name. Must to be a better way. The tables for selecting the model of machine, the system, the group, and the component went well, but populating records with LaborTime is based on JobCode, which is where I'm stuck. Excel allowed me to put a formula (above) in "LaborTime" cell, where I could display "LaborTime" based on "JobCode", using only the JobCode that pertained to THAT component on THAT machine. (No redundancy, some (acceptable) "nulls"). Labor Times may change in the future (Table Update). Requested expansion to many other machines. (Migrating to DBMS from Excel). LaborTime table design? Duh...dazed and confused. (Sorry this ended up being so long) "Subject should be a brief, meaningful summary of your question or comment" Thoughts? |
#3
|
|||
|
|||
Attempting to design database around Excel datasheet design
My Goodness, thanks for the timely response (figured I'd be waitin' till
after the holiday). Still piecing all the tables together per your suggestions. When mapping out all the tables in Visio, I noticed a lot of M:N relationships. Your suggestion seems to have addressed that issue. If I can master this application, it will be expended greatly (additional company divisions), so I appreciate your insight into expandability. End application will be quite simple: What machine did you work on, what area of the machine was worked on, what physical component part failure was addressed, and what did you DO to it to solve problem? Based on users selections, application returns published labor time stating how long this procedure should have taken. Thanks again. "tina" wrote: "Machine" (ModelIndex) has many "Systems" / "Systems" can belongs to man different (ModelIndex) "Machines" solution: three tables, as tblMachines MachineID (primary key) MachineName other fields that describe a specific machine only tblSystems SystemID (pk) SystemName other fields that describe a specific system only tblMachineSystems MachSysID (pk) MachineID (foreign key from tblMachines) SystemID (fk from tblSystems) other fields that describe a specific machine and specific system combination relationships are tblMachines.MachineID 1:n tblMachineSystems.MachineID tblSystems.SystemID 1:n tblMachineSystems.SystemID "System" has many "Groups / "Groups has only one "System" solution: add another table, as tblSystemGroups GroupID (pk) SystemID (fk from tblSystems) other fields that describe the group for a specific system relationship is tblSystems.SystemID 1:n tblSystemGroups.SystemID "Groups has many "Components" / "Components" has only one "Groups solution: add another table, as tblComponents ComponentID (pk) GroupID (fk from tblGroups) relationship is tblGroups.GroupID 1:n tblComponents.GroupID (What you can DO to this component) "JobCode" is a predefined code list (A-Z) applicable to ALL machines (ModelIndex). solution: add another table, as tblJobCodes CodeID (pk) CodeName other fields that describe a job code only Not every component uses ALL "JobCodes" (Hence the formula to calculate only the "A1" "JobCode" that pertains to the component, else "Not apllicable" message) solution: add another table, as tblComponentJobCodes CompCodeID (pk) ComponentID (fk from tblComponents) JobCodeID (fk from tblJobCodes) other fields that describe a specific job code and specific component combination relationships are tblComponents.ComponentID 1:n tblComponentJobCodes.ComponentID tblJobCodes.JobCodeID 1:n tblComponentJobCodes.JobCodeID What I visualize here is a table for every ""JobCode" (A-Z), with the fields being: Table "JobCodeA" - ComponentCode | MachineCode | LaborTime Table "JobCodeB" - ComponentCode | MachineCode | LaborTime etc, etc... no, no, bad idea. Excel utilizes a flat file design. Access was specifically created to support a *relational* design. the two structures are very different, so if you try to impose a flat file structure on Access, you're going to lose much of the power of the software and have a horrendous time trying to develop an application that works right. Massive redundancy, massive "nulls", every table would inculde ALL components and All Machine Codes... Hmmm. (not very efficient) not at all efficient, and a nightmare to develop, maintain, and expand as needed. Excel allowed me to put a formula (above) in "LaborTime" cell, where I could display "LaborTime" based on "JobCode", using only the JobCode that pertained to THAT component on THAT machine. (No redundancy, some (acceptable) "nulls"). Labor Times may change in the future (Table Update). Requested expansion to many other machines. (Migrating to DBMS from Excel). LaborTime table design? Duh...dazed and confused. you haven't provided enough information about how LaborTime relates to the rest of the data - is each value related to a JobCode only? or does the value vary based on other variables as well as (or instead of) the JobCode? so i can't suggest anything here. also, you began by using the term "Machine" as seemingly interchangeable with the term "ModelIndex", but much later used the term "Machine" and "Machine Code" as seemingly interchangeable. so i'm lost; i don't know if you're using different terms to describe the same entity, or if you're describing distinct entities with each term. at any rate, i strongly recommend that you read up (or read more) on relational design principles. i think you're going to be the best person to model your process, once you understand how to do it correctly for a RDBMS. for more information, see http://home.att.net/~california.db/tips.html. suggest you read at least the first two tips, though you'll find the next seven helpful as well when you begin building the tables/relationships structure. hth "CBartman" wrote in message ... Posting question as last resort - been using this forum for long time with wonderful results - great job people. Bleery eyed search - finding no viable solution. Labor Time Guide - Problem: (I guess I would call this a ) 3 dimensional array in an Excel sheet, with the cell formula being "=if(A1 = "A", "number",if(A1 = "B", "number",if(A1 = "C", number, "number not found") "A1" being equal to a user selected list box result (A-Z), which results in a cell value equal to a labor time ("number"). "Machine" (ModelIndex) has many "Systems" / "Systems" can belongs to man different (ModelIndex) "Machines" "System" has many "Groups / "Groups has only one "System" "Groups has many "Components" / "Components" has only one "Groups (What you can DO to this component) "JobCode" is a predefined code list (A-Z) applicable to ALL machines (ModelIndex). Not every component uses ALL "JobCodes" (Hence the formula to calculate only the "A1" "JobCode" that pertains to the component, else "Not apllicable" message) Excel Array: Left column lists ALL possible components on ALL (ModelIndex), which is a combination of selected "System|Group|Component" (no "nulls"). "Header column" lists ALL possible Machines (MachineIndex). Not all Machine use ALL components (many "nulls"). What I visualize here is a table for every ""JobCode" (A-Z), with the fields being: Table "JobCodeA" - ComponentCode | MachineCode | LaborTime Table "JobCodeB" - ComponentCode | MachineCode | LaborTime etc, etc... Massive redundancy, massive "nulls", every table would inculde ALL components and All Machine Codes... Hmmm. (not very efficient) The only difference in the tables would be the time entered, based on the table name. Must to be a better way. The tables for selecting the model of machine, the system, the group, and the component went well, but populating records with LaborTime is based on JobCode, which is where I'm stuck. Excel allowed me to put a formula (above) in "LaborTime" cell, where I could display "LaborTime" based on "JobCode", using only the JobCode that pertained to THAT component on THAT machine. (No redundancy, some (acceptable) "nulls"). Labor Times may change in the future (Table Update). Requested expansion to many other machines. (Migrating to DBMS from Excel). LaborTime table design? Duh...dazed and confused. (Sorry this ended up being so long) "Subject should be a brief, meaningful summary of your question or comment" Thoughts? |
#4
|
|||
|
|||
Attempting to design database around Excel datasheet design
well, you're welcome - though i hope you're taking my design suggestions
with a grain of salt, and reading up on relational design so you can be sure to do it right (rather than just doing it the way i suggested). good luck with it, and happy holidays! "CBartman" wrote in message news My Goodness, thanks for the timely response (figured I'd be waitin' till after the holiday). Still piecing all the tables together per your suggestions. When mapping out all the tables in Visio, I noticed a lot of M:N relationships. Your suggestion seems to have addressed that issue. If I can master this application, it will be expended greatly (additional company divisions), so I appreciate your insight into expandability. End application will be quite simple: What machine did you work on, what area of the machine was worked on, what physical component part failure was addressed, and what did you DO to it to solve problem? Based on users selections, application returns published labor time stating how long this procedure should have taken. Thanks again. "tina" wrote: "Machine" (ModelIndex) has many "Systems" / "Systems" can belongs to man different (ModelIndex) "Machines" solution: three tables, as tblMachines MachineID (primary key) MachineName other fields that describe a specific machine only tblSystems SystemID (pk) SystemName other fields that describe a specific system only tblMachineSystems MachSysID (pk) MachineID (foreign key from tblMachines) SystemID (fk from tblSystems) other fields that describe a specific machine and specific system combination relationships are tblMachines.MachineID 1:n tblMachineSystems.MachineID tblSystems.SystemID 1:n tblMachineSystems.SystemID "System" has many "Groups / "Groups has only one "System" solution: add another table, as tblSystemGroups GroupID (pk) SystemID (fk from tblSystems) other fields that describe the group for a specific system relationship is tblSystems.SystemID 1:n tblSystemGroups.SystemID "Groups has many "Components" / "Components" has only one "Groups solution: add another table, as tblComponents ComponentID (pk) GroupID (fk from tblGroups) relationship is tblGroups.GroupID 1:n tblComponents.GroupID (What you can DO to this component) "JobCode" is a predefined code list (A-Z) applicable to ALL machines (ModelIndex). solution: add another table, as tblJobCodes CodeID (pk) CodeName other fields that describe a job code only Not every component uses ALL "JobCodes" (Hence the formula to calculate only the "A1" "JobCode" that pertains to the component, else "Not apllicable" message) solution: add another table, as tblComponentJobCodes CompCodeID (pk) ComponentID (fk from tblComponents) JobCodeID (fk from tblJobCodes) other fields that describe a specific job code and specific component combination relationships are tblComponents.ComponentID 1:n tblComponentJobCodes.ComponentID tblJobCodes.JobCodeID 1:n tblComponentJobCodes.JobCodeID What I visualize here is a table for every ""JobCode" (A-Z), with the fields being: Table "JobCodeA" - ComponentCode | MachineCode | LaborTime Table "JobCodeB" - ComponentCode | MachineCode | LaborTime etc, etc... no, no, bad idea. Excel utilizes a flat file design. Access was specifically created to support a *relational* design. the two structures are very different, so if you try to impose a flat file structure on Access, you're going to lose much of the power of the software and have a horrendous time trying to develop an application that works right. Massive redundancy, massive "nulls", every table would inculde ALL components and All Machine Codes... Hmmm. (not very efficient) not at all efficient, and a nightmare to develop, maintain, and expand as needed. Excel allowed me to put a formula (above) in "LaborTime" cell, where I could display "LaborTime" based on "JobCode", using only the JobCode that pertained to THAT component on THAT machine. (No redundancy, some (acceptable) "nulls"). Labor Times may change in the future (Table Update). Requested expansion to many other machines. (Migrating to DBMS from Excel). LaborTime table design? Duh...dazed and confused. you haven't provided enough information about how LaborTime relates to the rest of the data - is each value related to a JobCode only? or does the value vary based on other variables as well as (or instead of) the JobCode? so i can't suggest anything here. also, you began by using the term "Machine" as seemingly interchangeable with the term "ModelIndex", but much later used the term "Machine" and "Machine Code" as seemingly interchangeable. so i'm lost; i don't know if you're using different terms to describe the same entity, or if you're describing distinct entities with each term. at any rate, i strongly recommend that you read up (or read more) on relational design principles. i think you're going to be the best person to model your process, once you understand how to do it correctly for a RDBMS. for more information, see http://home.att.net/~california.db/tips.html. suggest you read at least the first two tips, though you'll find the next seven helpful as well when you begin building the tables/relationships structure. hth "CBartman" wrote in message ... Posting question as last resort - been using this forum for long time with wonderful results - great job people. Bleery eyed search - finding no viable solution. Labor Time Guide - Problem: (I guess I would call this a ) 3 dimensional array in an Excel sheet, with the cell formula being "=if(A1 = "A", "number",if(A1 = "B", "number",if(A1 = "C", number, "number not found") "A1" being equal to a user selected list box result (A-Z), which results in a cell value equal to a labor time ("number"). "Machine" (ModelIndex) has many "Systems" / "Systems" can belongs to man different (ModelIndex) "Machines" "System" has many "Groups / "Groups has only one "System" "Groups has many "Components" / "Components" has only one "Groups (What you can DO to this component) "JobCode" is a predefined code list (A-Z) applicable to ALL machines (ModelIndex). Not every component uses ALL "JobCodes" (Hence the formula to calculate only the "A1" "JobCode" that pertains to the component, else "Not apllicable" message) Excel Array: Left column lists ALL possible components on ALL (ModelIndex), which is a combination of selected "System|Group|Component" (no "nulls"). "Header column" lists ALL possible Machines (MachineIndex). Not all Machine use ALL components (many "nulls"). What I visualize here is a table for every ""JobCode" (A-Z), with the fields being: Table "JobCodeA" - ComponentCode | MachineCode | LaborTime Table "JobCodeB" - ComponentCode | MachineCode | LaborTime etc, etc... Massive redundancy, massive "nulls", every table would inculde ALL components and All Machine Codes... Hmmm. (not very efficient) The only difference in the tables would be the time entered, based on the table name. Must to be a better way. The tables for selecting the model of machine, the system, the group, and the component went well, but populating records with LaborTime is based on JobCode, which is where I'm stuck. Excel allowed me to put a formula (above) in "LaborTime" cell, where I could display "LaborTime" based on "JobCode", using only the JobCode that pertained to THAT component on THAT machine. (No redundancy, some (acceptable) "nulls"). Labor Times may change in the future (Table Update). Requested expansion to many other machines. (Migrating to DBMS from Excel). LaborTime table design? Duh...dazed and confused. (Sorry this ended up being so long) "Subject should be a brief, meaningful summary of your question or comment" Thoughts? |
#5
|
|||
|
|||
Attempting to design database around Excel datasheet design
On Dec 22, 8:51 pm, "tina" wrote:
Access was specifically created to support a *relational* design. no, no, bad idea. Jet is founded on SQL DBMS. Relational theory and SQL DBMS are as different (perhaps more so) than Access vs Excel e.g. some (acceptable) "nulls" nulls are not acceptable to relational theorists. Good luck trying to impose a pure relational design in dirty old SQL g. Jamie. -- |
#6
|
|||
|
|||
Attempting to design database around Excel datasheet design
Jamie Collins wrote:
some (acceptable) "nulls" nulls are not acceptable to relational theorists. Then I suggest theorists get out of the way of those who have to implement real world solutions. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ |
#7
|
|||
|
|||
Attempting to design database around Excel datasheet design
Hi there,
[you haven't provided enough information about how LaborTime relates to the rest of the data - is each value related to a JobCode only? or does the value vary based on other variables as well as (or instead of) the JobCode? so i can't suggest anything here.] This particular area is the snag I'm having. Sorry if I didn't "splain" very well. The rest of the table designs are very similar to what I was piecing together, other than the M:N relationships. (I finally have my brain wrapper around how to utilize the "composite" tables, thank you). The final product will return a "LaborTime" based on a specific code derived from "tblMAIN, tblGROUP, & tblCOMPONENT tables, user selected. Once we know WHAT part (component) was addressed, now I need to return a "LaborTime" based on the "JobCode" (what did you DO to the compnent, codes A-Z). So far so good. Problem I can't quite wrap my brain around: LaborTime might be different for each MACHINE, even with matching (combined) component code and matching JobCode. i.e. (code B) = "Replace" , (code A03B [A+03+B] "alternator belt", on MACHINE "A" might be 2.5 hrs, Same Codes ("Replace", A03B) on MACHINE "B" might take only 1.5 hrs. (Code D) = "Adjust" might have posted times, again, different for each machine. I say "might" because; 1- not all components are on ALL machines (electric truck doesn't HAVE an engine, therefore any "engine component codes will not apply), and 2 - not ALL JobCodes apply to ALL components (you may have an engine, but you CAN'T "adjust" it). LaborTime table - must be able to match "ComponentCode", "JobCode", AND "Machine" code to arrive at / return a published LaborTime. I was successful at creating this scheme in Excel, being able to include "JobCode" in a formula directly in the cell. (each component has only approx 5 "JobCodes that actually apply - nested "IF" statements) I'm not taking credit for the code scheme, nor labor times we're using (although it seems to be working out relatively well). I would like to (was requested to) design a more suitable data access method. VBA in Excel is working well at this time, but I do anticipate this "ComponentCode" / "LaborTime" schema to be migrating to other divisions in the company. Potentially thousands of components, machines, labor times. I need help understanding what the "tblLABORTIME" layout would look like. Yes, I'm still reading: "Database Systems - Design, Implementation, & Management, 5th edition" (Rob Coronel - 2002) Was a good course, although I don't claim to be fluent. "tina" wrote: "Machine" (ModelIndex) has many "Systems" / "Systems" can belongs to man different (ModelIndex) "Machines" solution: three tables, as tblMachines MachineID (primary key) MachineName other fields that describe a specific machine only tblSystems SystemID (pk) SystemName other fields that describe a specific system only tblMachineSystems MachSysID (pk) MachineID (foreign key from tblMachines) SystemID (fk from tblSystems) other fields that describe a specific machine and specific system combination relationships are tblMachines.MachineID 1:n tblMachineSystems.MachineID tblSystems.SystemID 1:n tblMachineSystems.SystemID "System" has many "Groups / "Groups has only one "System" solution: add another table, as tblSystemGroups GroupID (pk) SystemID (fk from tblSystems) other fields that describe the group for a specific system relationship is tblSystems.SystemID 1:n tblSystemGroups.SystemID "Groups has many "Components" / "Components" has only one "Groups solution: add another table, as tblComponents ComponentID (pk) GroupID (fk from tblGroups) relationship is tblGroups.GroupID 1:n tblComponents.GroupID (What you can DO to this component) "JobCode" is a predefined code list (A-Z) applicable to ALL machines (ModelIndex). solution: add another table, as tblJobCodes CodeID (pk) CodeName other fields that describe a job code only Not every component uses ALL "JobCodes" (Hence the formula to calculate only the "A1" "JobCode" that pertains to the component, else "Not apllicable" message) solution: add another table, as tblComponentJobCodes CompCodeID (pk) ComponentID (fk from tblComponents) JobCodeID (fk from tblJobCodes) other fields that describe a specific job code and specific component combination relationships are tblComponents.ComponentID 1:n tblComponentJobCodes.ComponentID tblJobCodes.JobCodeID 1:n tblComponentJobCodes.JobCodeID What I visualize here is a table for every ""JobCode" (A-Z), with the fields being: Table "JobCodeA" - ComponentCode | MachineCode | LaborTime Table "JobCodeB" - ComponentCode | MachineCode | LaborTime etc, etc... no, no, bad idea. Excel utilizes a flat file design. Access was specifically created to support a *relational* design. the two structures are very different, so if you try to impose a flat file structure on Access, you're going to lose much of the power of the software and have a horrendous time trying to develop an application that works right. Massive redundancy, massive "nulls", every table would inculde ALL components and All Machine Codes... Hmmm. (not very efficient) not at all efficient, and a nightmare to develop, maintain, and expand as needed. Excel allowed me to put a formula (above) in "LaborTime" cell, where I could display "LaborTime" based on "JobCode", using only the JobCode that pertained to THAT component on THAT machine. (No redundancy, some (acceptable) "nulls"). Labor Times may change in the future (Table Update). Requested expansion to many other machines. (Migrating to DBMS from Excel). LaborTime table design? Duh...dazed and confused. you haven't provided enough information about how LaborTime relates to the rest of the data - is each value related to a JobCode only? or does the value vary based on other variables as well as (or instead of) the JobCode? so i can't suggest anything here. also, you began by using the term "Machine" as seemingly interchangeable with the term "ModelIndex", but much later used the term "Machine" and "Machine Code" as seemingly interchangeable. so i'm lost; i don't know if you're using different terms to describe the same entity, or if you're describing distinct entities with each term. at any rate, i strongly recommend that you read up (or read more) on relational design principles. i think you're going to be the best person to model your process, once you understand how to do it correctly for a RDBMS. for more information, see http://home.att.net/~california.db/tips.html. suggest you read at least the first two tips, though you'll find the next seven helpful as well when you begin building the tables/relationships structure. hth "CBartman" wrote in message ... Posting question as last resort - been using this forum for long time with wonderful results - great job people. Bleery eyed search - finding no viable solution. Labor Time Guide - Problem: (I guess I would call this a ) 3 dimensional array in an Excel sheet, with the cell formula being "=if(A1 = "A", "number",if(A1 = "B", "number",if(A1 = "C", number, "number not found") "A1" being equal to a user selected list box result (A-Z), which results in a cell value equal to a labor time ("number"). "Machine" (ModelIndex) has many "Systems" / "Systems" can belongs to man different (ModelIndex) "Machines" "System" has many "Groups / "Groups has only one "System" "Groups has many "Components" / "Components" has only one "Groups (What you can DO to this component) "JobCode" is a predefined code list (A-Z) applicable to ALL machines (ModelIndex). Not every component uses ALL "JobCodes" (Hence the formula to calculate only the "A1" "JobCode" that pertains to the component, else "Not apllicable" message) Excel Array: Left column lists ALL possible components on ALL (ModelIndex), which is a combination of selected "System|Group|Component" (no "nulls"). "Header column" lists ALL possible Machines (MachineIndex). Not all Machine use ALL components (many "nulls"). What I visualize here is a table for every ""JobCode" (A-Z), with the fields being: Table "JobCodeA" - ComponentCode | MachineCode | LaborTime Table "JobCodeB" - ComponentCode | MachineCode | LaborTime etc, etc... Massive redundancy, massive "nulls", every table would inculde ALL components and All Machine Codes... Hmmm. (not very efficient) The only difference in the tables would be the time entered, based on the table name. Must to be a better way. The tables for selecting the model of machine, the system, the group, and the component went well, but populating records with LaborTime is based on JobCode, which is where I'm stuck. Excel allowed me to put a formula (above) in "LaborTime" cell, where I could display "LaborTime" based on "JobCode", using only the JobCode that pertained to THAT component on THAT machine. (No redundancy, some (acceptable) "nulls"). Labor Times may change in the future (Table Update). Requested expansion to many other machines. (Migrating to DBMS from Excel). LaborTime table design? Duh...dazed and confused. (Sorry this ended up being so long) "Subject should be a brief, meaningful summary of your question or comment" Thoughts? |
Thread Tools | |
Display Modes | |
|
|