Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Dec 2005
    Posts
    10

    Exclamation Unanswered: Database design issues for a metallurgical lab

    I have a few questions. I was recently asked by my employer to convert our hand written logs sheets to an access database. The log sheets contain the test results on metallurgical work we do for Chrysler and GM. I have no problem designing the tables for this. But, they want me to make a query which shows a CPK chart for the test results of each customer. Also I was asked to make another query which would be displayed in a forum format with 4 fields. One field for a particular furnace, one for the part number, and a start and end date. After plugging in this information a report would be displayed, this would show the test results for this given time frame.

    I am a sophomore, majoring in computer science but I have never done anything like this before. I am familiar with Microsoft access. I took it as a prerequisite my freshman year and next fall I will start on some more advanced database design classes.

    So I have two weeks to design this. I’ll start w/ the table layouts but how do I produce a CPK chart in Microsoft Access?

    Is it possible to create a forum so the user can set the parameters for such a query without making a new one every time?

    Thank you and any advice on how I should go about this project would be great =))

    Chad

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    Welcome to the post

    Yes to you parameters Question

    To help you With your time line.
    Your tables should take about 75-80% of your time

    Fill out every thing in the Degin of the tables and check the feild setting.

    If you don't have the table right them the database won't work well you will be fighting all the time.

    GOOD LUCK
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Dec 2005
    Posts
    10

    thank you

    Thank you; this project is tough and a little over my head. But how can I create a CPK chart in MS Access?

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    what a CPK Chart i'll just been inside MS access

    Has it got a X Y axis then that where i would start look @ what is Common
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    CPK chart, that was new to me as well, having done a quick google it looks like a normal distrubtion / simple line chart.

    so providing you can marshall your data appropriately you should be able to do what you want with the chart wizard.

    You should be able to parameterise your underlying query for the report
    ou should be able to parameterise the queries for the chart wizard. effectively its a specialise sub report inserted into a standard Access eport. So if you change your base your link fields on the parameters you want to pass to the chart then it should work ok
    HTH

  6. #6
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    You do not normally chart CPk values by themselves. They are a derived statistic from the result of a control chart or average and range chart with the CPk metric as a descriptive statistic. If indeed this is what you mean, I would suggest you create the charts in excel using the output of a query. If in Access, then that is much more difficult, and to honest I would not try it, nor have I done so. You can of course output the results to a report and calculate the CP and CPk values from there.

  7. #7
    Join Date
    Dec 2005
    Posts
    10

    thank you

    Thank you all for the responses. I'm sure I'll have more questions after christmas. I'm going to start on the easy part first (tables) and normalize them. Then the hard part will begin lol. May i give links to my sample database as i design it so you can look it over and give sugestions?

    Merry Christmas to everyone

  8. #8
    Join Date
    Dec 2005
    Posts
    10

    simple question

    ok i have some tables made. I don't have my Access book at work and i forgot how to create a drop down box in a forum that would select a table. After a table is selected a sub forum is displayed w/ the info. How do i create this?

  9. #9
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by chad101
    ok i have some tables made. I don't have my Access book at work and i forgot how to create a drop down box in a forum that would select a table. After a table is selected a sub forum is displayed w/ the info. How do i create this?
    Hi Chad101,

    Being an absolute beginner, you should be sure to keep you Access book handy. Though you are seemingly in over your head, the best thing is to read your book thoroughly and get a real good solid understanding of Relational Database design and such. You are trying to get into the 12 foot end of the pool before even getting your toes wet. Any case, to get your ComboBox (drop-down) in DesignView of your form simply click on the ToolBox icon to open all of your tools. Click on the ComboBox tool and place it on your form. Actually, it would be best to have your Tables/Queries structured properly as that makes it all the better to begin with. Second, I hope you base your Forms/Reports on Queries and not just Tables. Do yourself a very good one and do READ as much as you can and learn it from there. The reward will in fact be yours. Hope you had a Very Happy Christmas holiday and also have a very Happy New Year.

    have a nice one,
    BUD

  10. #10
    Join Date
    Dec 2005
    Posts
    10

    thank you

    Very true, at times I want to tell them to hire a professional. But, I know I am capable of pulling this off. I guess I need to sit down and write out a basic plan. Instead of rushing into things. I started out with a basic table layout for each of the big 3 automakers and one for Tenneco. One problem is each customer provides multiple part numbers with different check requirements.

    Some parts call for “total case”and or “effective case” checks. Total case checks are done by crushing 5 to 10 parts @ 30,000+ lbs and picking your “highest” and “lowest” crush part from the tested samples. The parts are then sectioned, sanded, polished, etched, and looked at under a microscope @ 200 times for the case measurement. Which range from .002 up to .060 thousandths…depending on the part. Effective case checks are also crushed and sectioned. The only difference is effective case is a micro hardness measurement @ 200 times w/ a diamond indenter. Some require additional checks such as core hardness readings and outer diameter hardness readings on a RC scale.

    After the testing we have a log book which asks for the “log number”(1,2,3 etc), “date”, “tech initial”, if the check pieces where “sent to customer"(yes/no), “customer name”, “part number”, “furnace”, “results”, “accepted or rejected”(yes/no), if rejected we enter a “defect code”(A,B,C, etc).

    Should I make separate table for each part number? Since the check requirements depend on each part number?

    TABLE “part 123456”
    date -- (mm/dd/yy)
    techId – (id code)
    furnace -- (furnace code)
    sentToCustomer – (yes/no)
    crush 1 --.003
    crush 2 --.003
    crush 3 --.0065
    crush 4 --.0015
    crush 5 --.002
    totalCaseHigh -- .0185
    totalCaseLow -- .017
    *MFH -- (yes/no)
    accepted --(yes/no)

    TABLE “part 12345678”
    date -- (mm/dd/yy)
    techId – (id code)
    furnace -- (furnace code)
    sentToCustomer – (yes/no)
    crush 1 --.0115
    crush 2 --.012
    crush 3 --.0095
    crush 4 --.0145
    crush 5 --.008
    crush 6 --.0055
    crush 7 --.007
    effectiveCaseHigh -- .0185
    effectiveCaseLow -- .017
    coreHardnessRC -- 30.6
    *MFH -- (yes/no)
    accepted --(yes/no)

    TABLE “part 2223332”
    date -- (mm/dd/yy)
    techId – (id code)
    furnace -- (furnace code)
    sentToCustomer – (yes/no)
    crush 1 --.0115
    crush 2 --.012
    crush 3 --.0095
    crush 4 --.0145
    crush 5 --.008
    crush 6 --.0055
    crush 7 --.007
    HardnessRC1 -- 48.3
    HardnessRC2 -- 44.7
    HardnessRC3 -- 43.3
    HardnessRC4 -- 45.5
    HardnessRC5 -- 47.2
    HardnessRC6 -- 45.1
    coreHardnessRC -- 22.9
    *MFH -- (yes/no)
    accepted --(yes/no)

    One problem is we have thousands of part numbers =((

    Should I design a table that has a “customer name”, “part number” and all check requirements? Then if a particular part number dose not call for a certain check…that filed would be left blank?

    * MFH = metal file hardness
    Last edited by chad101; 12-28-05 at 23:55.

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Take a few tutorials in basic relational database design. Your proposal breaks every best practice rule I can think of.

    This isn't a bad one to start with.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  12. #12
    Join Date
    Dec 2005
    Posts
    10
    Thank you =) The tutorial is very helpful . I have one problem w/ my “inspection table” which I will explain later in the post.

    This is the plan I am using to figure out my entities and attributes for the database. I wrote a short description about the hand written log book we use at work, and I underlined the verbs in MS Word.

    Code:
    A log book is an array individual fields. 
    Each field is made up of 11 columns 
    (date, initial, log #, sent to customer, customer, dept, part #, load #,
     results, acc/rej, and defect code).
    
    The date pertains to the time a particular part was inspected.
    Initial indicates who checked the part.
    Log # starts at “1” Jan 1st and increments for each check throughout the
    year
    Sent to cust asks if “inspected” samples are sent back with finished product.
    Customer displays owner of finished product.
    Dept indicates the processing area of plant.
    Part # distinguishes the part type.
    Load # may display what furnace and run count for the year to date. 
    Or, tub number in an array of tubs processed through a continuous furnace. 
    Results give a list of values on test results.
    Acc/rej  is a simple “yes/no” question asking if finished product is
    acceptable to ship to customer.
    Defect Code represents a particular defect found during inspection
    Tables
    Log -- *LOGNum, LOGDate, &LOG_TECId, &LOG_CUSTId, &LOG_FURId &LOG_PARTId, &LOG_INSPId, LOGaccept, &LOG_DEFCode
    Customer -- *CUSTId, CUSTName,
    Part number -- *PARTNum, &PART_CUSTId,
    Technician -- *TECHId, TECHName
    Defects -- *DEFCode, DEFType
    Furnace -- *FURId, FURType
    Inspection type -- *INSPid,

    * Primary key
    & Foreign key

    One problem with the Inspection table; we have different inspections for each part number. Some require 10 RC punches others only require 5 RC punches. Some Require a Total Case inspection. Others Require an Effective Case inspection or both TC & EFF Case.

    Some part #s require all tests (i.e. Crush, Total Case, Effective Case, RC, & R15N)

    Once I figure out the basic table setup I can use a query to build the final log. And develop a forum for the data entry. I’ll worry about the CPK reports later

    EDIT:
    Here is a sample database Let me know what you think. I still need help on the INSP table
    Your Download-Link: http://rapidshare.de/files/10087240/...Table.zip.html
    Last edited by chad101; 12-30-05 at 06:35.

  13. #13
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    Just one point to bear in mind, CP and CPk are descriptive statistics for grouped data.

    David

  14. #14
    Join Date
    Dec 2005
    Posts
    10
    Thank you, I am still working on the table structure at the moment. Once I finish this, the cpk chart should be a snap. I have the LOG part figured out. The final hump is figuring out my INSPECTION table. I will show you some sample inspections we run in the Lab so you have a better understanding of my problem.

    The inspections require a set test of attributes, entered by the user. Our total/effective case depth results range from .000 to .099 thousandths of an inch. RC, RB, R15N, R45N, and Birnell results range from 0.0 to 100.0 depending on the particular check. The only pass/fail test we have is MFH (metal file hardness); which I haven’t implemented into the database yet. The inspection log will contain a yes/no field asking if the particular part passed the over all inspection

    Example checks


    Fisher 23510A949
    RC inspection – 10 pieces – range = 0.0/100.0
    Straightness inspection – 100 pieces - “we only write down the range on this” i.e. 0/100 failed or 1/100 failed etc etc. (not implemented in database yet)

    Fisher 23511 A840
    RC inspection – 10 pieces – range = 0.0/100.0

    Tenneco 436610
    Crush inspection – 10 pieces - range = .000/.099 (table not made yet)
    Total Case inspection – 2 pieces – range = .000/.099
    Core RC inspection – 2 pieces – range = 0.0/100.0 (table not made yet)
    MFH – 2 pieces - yes/no (not implemented in database yet)

    Tenneco 43616
    Crush inspection - 5 pieces – range = .000/.099 (table not made yet)
    Total Case inspection – 2 pieces - range = .000/.099
    Effective Case inspection – 2 pieces - range = .000/.099
    RC inspection – 2 pieces – range = 0.0/100.0

    P&A (Customer not in database yet)
    Total Case inspection – 2 pieces - range = .000/.099
    R15N inspection – 12 pieces - range = 0.0/100.0
    R45N inspection – 12 pieces - range = 0.0/100.0
    MFH – 2 pieces - yes/no (not implemented in database yet)

    Winzler (Customer not in database yet)
    Total Case inspection – 10 pieces - range = .000/.099
    Effective Case inspection – 10 pieces - range = .000/.099
    MFH – 2 pieces - yes/no (not implemented in database yet)

    We have hundreds of customers besides the Big 3 and all call for different inspections with differentiating piece counts used in the inspection. I uploaded the new design and fixed the tables based on the suggestions gave to me. My inspection table relationships is incorrect at the moment; but it revels the over all structure I am trying to create.


    Thanks, Chad
    Attached Files Attached Files

  15. #15
    Join Date
    Dec 2005
    Posts
    10

    One more question…

    Sorry not a fan of double posting I just have an idea I want to share followed by one more quick question.

    I was over looking my design and I think the most appropriate way to design my form is by having the main log table in the default form then once the log info is entered; the subform containing the inspection table is loaded tblINSP PK = tblLOG PK. This table will contain different fields for each “type” of inspection preformed on part number located in log table. The user then picks the inspection types and hits “enter” this will load another subform which will contain a table representing each “inspection type”. Each table will show only one “blank” record, for data entry, on separate lines. All PKs in the “inspection type” tables = tblINSP PK.

    I think that makes sense lol! I took a perquisite class based solely on MS Access last winter. But we stopped at macros. The final two chapters talk about VBA Coding in MS Access but nothing really helpful.

    My only question is how can you make a subform that loads multiple “inspection type” tables based on the result entered in the inspection table?

    I did a quick screen shot of a sample forum I designed to help clarify my question.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •