Results 1 to 9 of 9
  1. #1
    rebfein's Avatar
    rebfein is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2015
    Location
    So Cal
    Posts
    114

    Red face Need assistance in db design.

    Hi everyone,

    With all your help, I did such a great job with the one DB project (for LCM for parts) that this past Friday, my boss has tasked me with another one. Actually, he wanted this project done in Excel, but with 3100 records and counting, I don't believe that is wise.



    The project is to create a database that will track ECOs (Engineering Change Orders) by model number (in some cases, an ECO will cover a family of models). It seems that several attempts have been made to do this over the past 20 years, and in each case, the person in charge has given up in frustration. I've been importing ALL the information from the six separate spreadsheets from previous attempts into one (for the time being). Each record must contain the ECO #, the date, the drawing number (this has multiple numbers in this cell), a component number, model number, serial number, work order number, a .pdf copy of the ECO and status (Active or Void).

    The objective is that when running a query on the model, all ECOs will appear within the query. Sounds simple, but there are a couple of extenuating circumstances:
    • An ECO can list changes in several models that have a familial connection (XYZ model, Rev 1, Rev 2, etc.),
    • Also, an ECO can list changes in several families of models that share assemblies (the bottom plate assembly for XYZ model, Rev 1; is used in DEF model, Rev 7).


    So, I'm wondering if I need to set up a separate record for each ECO to each model/version (one to one); or one ECO to all the model/families (one to many) or if I can get away with making one record of all the ECOs to each model, version (many to one).

    Any assistance, advice or even telling me how to do this, would be greatly appreciate. And if you're in the Los Angeles, OC or IE areas; I'll send you brownies as a thank you gift!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    how about a child table to the tModel table
    model* (XYZ model)

    tModelRevs
    model* (XYZ model)
    Rev (rev1)
    details

    then you can make infinite revisions to the model.

  3. #3
    rebfein's Avatar
    rebfein is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2015
    Location
    So Cal
    Posts
    114
    Hi Ranman256,

    I'll suggest this to my boss.

    Currently, I've come up with the following:

    Tab1
    ECO#
    ECO_Date
    Desc_of_Change

    Tab2
    ECO#
    Model#
    Rev_Lttr
    Model_Family
    Serial#

    Tab3
    ECO#
    Drawing Numbers (an ECO affects more than one drawing [each assembly drawing has a few to a lot of detail drawings that make the assembly])
    Components
    Work_Order

    What do you think of this organization?

    Thank you,

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Object names should be letters and numbers (exception is the underscore)
    NO spaces
    NO punctuation
    NO special characters

    Do not use "#" in names. "#" is a date delimiter in Access.
    Do not begin a field names with a number. The Access gnomes do strange things when this happens.


    Maybe:

    tblECO
    ---------
    ECOID_PK Autonumber
    ECONum Text
    ECO_Date
    Desc_of_Change

    tblModels
    ---------
    ModelID_PK Autonumber
    ECONum_FK Number - Long
    ModelNum
    Model_Family
    SerialNumber
    Rev_Lttr

    tblDrawings
    --------------------
    DrawingID_PK Autonumber
    ECONum_FK Number - Long
    Drawing_Number Text (an ECO affects more than one drawing [each assembly drawing has a few to a lot of detail drawings that make the assembly])
    Components
    Work_Order


    Can you provide examples of the data?
    It is very hard to say whether a table design will work without knowing what the data looks like.

  5. #5
    rebfein's Avatar
    rebfein is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2015
    Location
    So Cal
    Posts
    114
    Hi Steve,

    I would love to provide you with an actual example, but can't (confidentiality issues). But I'll try to tell you a bit more about it (& FYI, I only use the # as a shortcut when I'm describing stuff ). If I dumb it down a bit, forgive me as I don't know what type of background you have and I'm trying to make this easy to understand (not knowing the audience is a tech writer's curse). So, here we go...

    I work for an aerospace company that makes power inverters and converters. Each time there is a change, for whatever reason. An ECO must be filled out, then signed off. There is quite a bit of information on each form, but my boss is only interested in having some of the basic information all in one place where it can be found easily. But nothing easy, ever came easy...

    First of all, each ECO s/b scanned as a .pdf. Not a lot are and someone is going to be helping me with that. One of the reasons, this database is needed is that some departments lose their hard copies, take our hard copies & we never see them again. Or they misplace them, take the library copy & doesn't return it. The 'librarian' is having a fit over that. Another issue, is that there are multiple models/families of models on each ECO. From what I understand, many managers in the past were lazy & had the draftsman issue one ECO for many models. Currently, there are over 3200 ECOs, many of them with multiple models on each, as few as one or as many as twenty. So, I'm going to be manually entering at least 10K records...Tired yet? I am .

    Ideally, per FAA rules, on each form, there is a ECO #, sheet # of #, & ideally, one ECO for every model worked on. On the form, this is followed by sub-assembly #s for each model assembly and detail drawing #s for each sub-assembly. A small model can have as few as three sub-assemblies (with multiple detail drawings for each), while larger models can have up to 15 sub-assemblies and the detail drawings for each. If the ECO is issued for repairs, the ECO must include the Serial Number (S/N) & the Work Order Number (WO), that the modification effects several generations forward from that point. If the ECO is issued for a model modification, the ECO must include the WO & the S/N AND the words ' & up' after the S/N.

    Another two items to track are revisions and modifications. Basically, a revision is issued when a part/component/whatever is changed. A modification is a change to the unit that makes it more powerful, lighter, etc.

    That's it, in rather a large nutshell.

    Thank you,

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would love to provide you with an actual example, but can't (confidentiality issues).
    I understand confidentiality issues, but can you take a spreadsheet (or the six separate spreadsheets) and enter in 5 - 10 records as examples of how the data is related? Not actual data, made up "things" similar to what you are working with.
    Need to see this to understand HOW data is related.

    Otherwise all we can do is make a SWAG as to the table structures.......

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with Steve that we need to know about the business, processes and constraints. I think somebody is misinterpreting confidentiality.
    If you look at any/all of the data models a Barry Williams' site, I don't think you'll learn anything specific about any database, organization or product. If your boss or you think this is breaking some confidentiality issue, then I suggest you are over-reacting.
    All Steve and the rest of us are asking is a made up scenario that protects your company's details/specialties, but gives us the general description of the business so we have some context.

    When you describe potential tables as tab1, tab2... we're all in the dark. Most readers do not have any intimate knowledge of Engineering Change Orders, let alone any customization in format and/or processing/validation/archiving that your org may do.

    Some generic definition of your terms may also help with context. For example, I don't understand the difference(if any) in revision vs modification. It would also seem that ECO is a superset of these. That is a Change could be a revision or modification??

    You should also be aware that "model" has a special connotation in database - data model has specific meaning.

    The Aerospace ECO related definitions at this site seem very generic, to me and may be useful to you with respect to your table design(s) and help with validation/sequencing/completeness of your processes. There is a lot of info on ECO at this site.

    We need more info if we are going to offer any focused responses/advice.

    You could certainly describe something of value and use values such as Porky Pig, the XYZ process, central city..... just make it fit within your scope.

    Here are examples from RogersAccessLiIbary showing the sort of "level of detail" requested.

    # 1
    ZYX Laboratories requires an employee tracking database. They want to track information about employees, the employee's job history, and their certifications. Employee information includes first name, middle initial, last name, social security number, address, city, state, zip, home phone, cell phone, email address. Job history would include job title, job description, pay grade, pay range, salary, and date of promotion. For certifications, they want certification type and date achieved.
    An employee can have multiple jobs over time, (ie, Analyst, Sr. Analyst, QA Administrator). Employees can also earn certifications necessary for their job.


    #2
    We are Consolidated Widgets, the largest widget distributor in the Mid West. Our customers are small independent Widget Dealers who in turn sell the widgets to the consumer market. On the other end, we have our Manufacturers who make the widgets. Our function is to act as a middleman and warehouse for the independent dealers.

    In an effort to make our Dealers more aware of new trends in the widget market, we are hosting a series of seminars, which allow the Manufactures to explain and extol the virtues of their products. Therefore, we need a computerized system that will help us manage the event. We have selected Microsoft Access as the rapid application tool to accomplish this.

    The seminars work like this: The event takes place over a weekend, Saturday and Sunday. On each day, there will be seven seminars, four in the morning and three in the afternoon. Lunch will be provided for the attendees. Each seminar lasts 45 minutes, allowing 15 minutes time between seminars. In order to manage the seminars more effectively, we are asking each Attendee (there can be more than one Attendee from each Dealer) to sign up for the seminars they wish to attend.

    The Manufactures may have one or more seminars, which may be presented by one or more of their employees. We need to be able to track that information for the web site, if the information changes, we want to be able to make it instantly available. We also need to track other information about the Seminars, like its location (room number), the starting and ending times, the seminar description, etc.

    For the Dealers, we need to track the Attendees and their schedule, purchase order, sales tax rate, total cost to the Dealer, method of payment, etc.

    For our own purposes, we need to track the Attendees, their schedule, our employee who entered the registration information, the cost of each seminar, method of payment, whether it is paid in full, etc.

    We also want to have a simple survey where the Attendees rate each Seminar. We want to be able to give the Manufacturers feedback on how effective their seminars are. The rating will be a simple 1-5, Poor-Good style rating. The Manufactures will get a report giving them the percent for each level and the mean for the seminar. This will be on a seminar/hour basis, so that they can judge which of their people are the most effective presenters.


    Good luck with your project.
    Last edited by orange; 02-21-2017 at 08:01 PM.

  8. #8
    rebfein's Avatar
    rebfein is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2015
    Location
    So Cal
    Posts
    114
    Thank you Orange,


    I know I seem a bit vague in my previous message. Please put it down to the company culture (very constrained) on 'outside' communications. Also, my boss changes his mind quite often about how envisions a project and how that is communicated to me.


    What began as a basic ECO tracking database, has again become more complex; I'll try to explain this latest iteration in more depth, but forgive me if I become 'vague' again; I'm finding this more overwhelming than I previously thought.


    The latest change, as I understand it, is that the database is to be set up to track ECOs as pertains to changes with models, sub-assemblies, wiring, and detail information, not just the models themselves.


    General Background:
    Many of the models use some sub-assemblies that originated in other models. If the model is ABC20(A); some part of the original has been changed in this revision to accommodate more power, use in another plane configuration, the list is endless. Some models have 1 PC sub-assembly; some can have a 2 PC sub-assembly Configuration (Master & Slave or Master & Phase Inverter) or a 3 PC sub-assembly configuration of all three. Also, some models have as few as 7 sub-assemblies to a unit; while others can have as many as 25 - 30.


    An ECO can cover a change that upgrades the power in a model's configuration and effect only one part in a sub-assembly of that model, or that family of models. Many of the ECOs issued effect many components used in each sub-assembly's makeup. Which makes this very much a one-to-many relationship model.


    An example would be as follows:
    Our cornerstone model, DDE40, has been around for 50 years. It is still a strong seller and is now in the latest revision of 'BU.' All model, sub-assemblies and detail parts/drawings are issued with a number and the revision letter. Some of those numbers would be SA1201100 for the Master Assembly and a revision letter of 'AF;' with different (but relatable) assembly number, schematic numbers and wiring diagrams for each model. My boss wants the database to track the ECO in this schema:
    Click image for larger version. 

Name:	schema.png 
Views:	51 
Size:	15.9 KB 
ID:	27654

    However, that is still too little for so much information. So far, I've set up the following:


    ECOTable contains these field;
    ECONum
    ECODate
    ECODescript (why the ECO is being issued)
    Model1, Model2, Model3, Model4, etc. (Each model the ECO effects)


    MODELTable:
    MODELID:
    MODELNUM: (revision number included within)


    Sub-ASSTable: (I've combined the Model & revision number in all the fields, hoping it would be easier)
    ModelRev
    Sub-Ass1, Sub-Ass2, Sub-Ass3, etc. (so far, I have up to 20 fields for models alone)


    DETAILSTable: (there can be as few as 6 details, parts, etc. in a sub-assembly or as many as 118 [I'm trying to pare this WAY down]) I also broke this in two (see Schematic Table below)
    Sub-AssNum
    Detail1, Detail2, Detail3 (so far, I have up to 35 fields here)​


    SCHEMATICSTable:
    MODELNum
    WIRINGDIAG1, WIRINGDIAG2
    SCHEMMSTR1, SCHEMMSTR2, SCHEMMSTR3 (so far, I have up to 35 fields here)

    SCANTable:
    SCANID
    SCAN: (a url to a folder within the database holding .pdfs of the ECO)​

    I'm leaving the parts table out of the equation so far since I have a parts table in the other database project that I can import if needed.

    Thank you and I hope this is enough clarification, if not, let me know,
    Attached Thumbnails Attached Thumbnails schema.jpg  

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Very interesting background. There is a lot of info on process and data acquisition at the link I provided and other related material
    at http://www.arenasolutions.com/resour...ision-control/

    I don't know the ECO business, but I can see that the info at the links I provided is relevant and could help you/your org generally.
    I am also not a technical writer, but my guess is you do things when in your technical writer hat that apply to ECO and your organization.

    What is the big picture of what we are going to write about/do as a business?
    What are the major subjects? What do they mean in plain English?
    What are the relevant attributes of those subjects?
    Is there a clear definition/description of those attributes (so we're all calling an apple an apple and not confusing with oranges)?
    What are the major processes that act upon those subjects?
    What is the sequence of those processes?
    Is there manipulation of info in those processes? What generally, and what when we add more detail?
    Mock up an outline; and sub topics/sub sub topics... Does it flow? Communicate and get feedback.
    Adjust the outline/model; Communicate and get more detailed feedback......

    Often IT and business work from a high level conceptual model--much like your first cut at a major outline of a specification; or much like the artist's concept of a new shopping center. Not much detail, but perfect for communication and feedback. A few iterations and you arrive at a conceptual (corporate) model. Add some detail, perhaps taking only a portion of the conceptual model and "flesh it out" to create a logical model; communicate the model; run some what if scenarios with potential users, management etc; get feedback; adjust the model. Iterate until you get consensus. At this point you'll have an initial blueprint for constructing a physical database.

    If the boss often changes his/her mind about what they want this system to do, then there really is no conceptual model and not much of a plan. Just like the ECO, you'd be changing the conceptual model and recording revisions.

    If you have all the major subjects in the conceptual model, you can work on logical and physical models based on your business priorities. You can reference the models and show where future linkages "would go", even though they are not a current priority.
    You are working from a model and a plan.

    Isn't that similar to what you do as a technical writer? I have not seen anyone who starts by writing the final "for print" article. I'm not suggesting you are doing that; I am trying to highlight that what you do in "a technical writer project" has corresponding steps in designing a database and application. You know more about the process than you think.

    Put this another way. If you were going to hire a contract database person to build this database, what would they need to do their job. Or, if you were hired to write the technical operations document for such a system, what would you need and what process would you follow?

    You may get additional ideas from the free videos in this link.

    Again, good luck with your project.
    Last edited by orange; 02-28-2017 at 02:55 PM.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-19-2015, 09:35 PM
  2. VBA assistance
    By Kwbrown in forum Programming
    Replies: 3
    Last Post: 03-15-2014, 03:09 PM
  3. Qry Assistance Please
    By buck in forum Access
    Replies: 8
    Last Post: 12-31-2013, 08:52 AM
  4. Database Design Assistance would be greatly appreciated
    By Zephyx in forum Database Design
    Replies: 4
    Last Post: 06-27-2013, 12:31 PM
  5. Assistance on DB Design required - please
    By Optimus_1 in forum Database Design
    Replies: 13
    Last Post: 07-05-2010, 04:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums