Results 1 to 10 of 10
  1. #1
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    May 2011
    Location
    KSA
    Posts
    481

    Question Process Alteration Database Design Methodology.

    Hi there,

    I am interested to build a database which should be able to control and report Manufacturing Process Alteration Instructions and advise.

    The following fields/elements will be participating:

    - A Unique Process Alteration No. PK
    - Date Process Alteration issued: Date/Time


    - Machine/Product ID (* This includes 4 type of information which is already existing in 4 different Tables like Machines, Items, Comp.A, Comp. B)
    - Description

    * I tried Union Query to combine all 4 tables to feed information to "Machine/Product ID" field but in two tables the description field contains Multi values so it is not effective any more. So can we have a selection option for the user at the top of form so which can lead to the concerned type of data from the related table. Like, if user is going to issues instructions for Process Alteration in "Machines" so he select machines and that table information appears in "Machine/Product ID" and "Description" fields?

    - Current Parameters (*This field will be containing the information that the current parameters/Instructions running on that machine/product.)

    * How it should automatically appeared in this field when select "Machine/Product ID"?

    - New Parameters (The Parameters/Instruction required to change the existing one and implement).

    * Some new will be required to type in and some times existing ones will be required to select, so how can provide the list of existing ones?



    - There are other fields as well which i can manage like Who Purpose/Initiated this alteration which will be from Team members list then approvals and notes.


    Please advise to move on for this concept with better database design.


    regards

    Zee

  2. #2
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    (* This includes 4 type of information which is already existing in 4 different Tables like Machines, Items, Comp.A, Comp. B)
    I'm confused by this statement. Are Items products? What are Comp. A and Comp. B?

    I think you need to identify the entities and relationships first. To stimulate your thought here's a first draft of what maybe your entities.

    Process Alteration
    Product
    Machine
    Production Line (Do you have this?)
    Setting (Call it what you will. I think this is what you mean by parameter.)
    Person (You talk about team members and approvals)

    There may be more than the foregoing.

  3. #3
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Mr. Rod,

    Thanks for your concern and you are very close to my opinion. Well, 4 type of information means there are four tables which already exists in database for other purposes which includes:

    - Machines/Prodn Line
    - Product
    - Composition -A
    - Composition - B

    The Process alteration instructions can be issued only for those above type of elements and those are already existing in tables. so my question was how to bring them in one table to use in "process Alteration Form" while filling the other information's, setting, people, etc.

    I tried Union Query to combine all 4 tables to feed information to "Machine/Product ID" field but in two tables the description field contains Multi values so it is not effective any more. So can we have a selection option for the user at the top of form so which can lead to the concerned type of data from the related table. Like, if user is going to issues instructions for Process Alteration in "Machines" so he select machines and that table information appears in "Machine/Product ID" and "Description" fields?

    regards

    Zee

  4. #4
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Hi Zee,

    I tried Union Query to combine all 4 tables to feed information to "Machine/Product ID" field ...
    OK, the first rule is never, never use one field for two purposes! 'Machine/Product Id' is very bad. Now I suspect that some Process Alterations pertain to a product on a machine. Let me confine my thoughts to this scenario and only this scenario. Look at the following basic entity relationship diagram.

    Click image for larger version. 

Name:	1.jpg 
Views:	16 
Size:	33.6 KB 
ID:	9750

    I have identified an entity for a manufacturing Process which is the manufacture of a product on a machine. The Product and Machine entities may already exist as two of those four tables you mention. Now I assume a machine has a number of variable settings (e.g. speed, temperature). If it does not already exist I believe you need an entity for Setting. Each Process Alteration will affect one or more setting, so there is another entity for the value of the setting which I have called Parameter.

    At first sight there appears to be a circular relationship between Process, Machine, Setting and Parameter. Closer inspection however shows that the 'direction' of the relationships is not circular

    So the physical table design of some of the above may look similar to:

    tblProcess
    ProcessID (PK)
    ProductId (FK)
    MachineID (FK)
    ...

    tblMachineSetting
    MachineSettingID (PK)
    MachineId (FK)
    ...

    tblMachineSettingValue
    MachineSettingValueID (PK)
    MachineSettingID (FK)
    ProcessID (FK)
    ...

    OK, this covers machine settings. I suspect that there may be other alterations to environment (unlikely) and/or formulae/component parts. (Is the latter what you mean by Composition?) So the entity relationship diagram is not finished.

    I shall stop here because I cannot really understand why you are trying to use a union query at this juncture in your design. Maybe I have totally misunderstood what you are asking. So before I go too far I shall await your response.

  5. #5
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Hi Rod,

    Its really a great work from yous side. No i am not using this way, its just for form label, the field label is MacPrdID.

    Sorry, i think am not able to explain you in proper way, lets try here again. i have a database that includes other applications like Lab. Test and Production Data record. This Alteration Instructions can be given to change settings for Machine/Product/Standard Parameters independently selection those from their concern tables like TblMachine, tblProduct, tblBodycomposition, tblglazecomposition. i think it will be clear now?

    You have very good skills and i am sure you can help me out for other thread also where a sample databse is attached as well.

    https://www.accessforums.net/databas...est-29437.html


    hope to hear from you.

    regards

    zee

  6. #6
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Zee,

    Yes, I was in danger of heading off in the wrong direction - or was I? But more of that a little later.

    Now, let me follow your original post as closely as I can. Please do not take offense when I comment on your English. I mean no harm but am eager to point out how misunderstandings arise. I spent many years consulting in a country where English was not the cradle tongue and I never bothered to learn the indigenous language, so I have become well skilled in 'reading between the lines' - but in your case I got it wrong!

    ... to build a database which should be able to control and report Manufacturing Process Alteration Instructions ...
    From what you have said subsequently you are not building a database or controlling Process Alteration Instructions. In fact you seem to want to:

    ... have a selection option for the user at the top of form so which can lead to the concerned type of data ...
    Yes, this is possible and I would favour it over your original idea of trying to put everything in the same place. I can immediately think of two ways of doing this. I'm not saying my ways are the optimum; they are just ways I know will work.

    The first contains an array of four combo (or list) boxes, one for each affected entity. The following shows a mock-up of what I mean.

    Click image for larger version. 

Name:	1.jpg 
Views:	4 
Size:	9.9 KB 
ID:	9761



    Each box has a row source specifically designed for the source table or query. If the choice is exclusive, then selecting a value in one box automatically (via a VBA routine) sets the other boxes to Null. As the form is saved (or whatever) another VBA routine places the chosen value(s) - probably as text - into the control (or field) that is the source for printing. Notes:

    • Use combo boxes if the choice in any category is exclusive.
    • Use multi select list boxes if a multi choice is possible in any one category.
    • If multi choices across categories is possible then this is the only feasible solution from the two I present.
    • The actual solution will be a little more complex as the above ignores which setting or parameter is affected.


    The second solution uses two boxes.

    Click image for larger version. 

Name:	1.jpg 
Views:	14 
Size:	9.1 KB 
ID:	9760
    The choice in the first box affects what is shown in the second box, indeed it may even show and hide different controls. Notes:

    • Use this solution if the applicability is always confined to one category only.
    • The actual solution will be a little more complex as the above ignores which setting or parameter is affected.


    ---

    Neither of my solutions allow for updating any value in the database, nor allow for creation of new records or relationships. The intent is to retrieve information for reporting purposes only.

    ---

    ... but in two tables the description field contains Multi values ...
    Oh no! The Access development team should be lined up against the wall and shot for introducing that one. However using the Recordset2 and Field2 objects it is possible to retrieve the whole range of values.

    ---

    Finally I don't really see the point of spending all this effort just to report on the affected machine/product/composition. I suspect sooner or later there will be a need to track Process Alterations and that is where my original post applies.

    Anyway let's have yourthoughts and we'll see if we can take it a step further.

    PS I'll look through the other thread.

  7. #7
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Mr. Rod,

    Season Greetings. I appreciate your efforts and it will not be wrong to say that you are really got great skills and abilities. Bye the way, thanks to highlight the issues related to my English and you are absolutely. I am trying to Improve with the help of this site. After posting you the my last thread, i was thinking to share with you the master idea of database, so May you support and advise me in better track. please wait for my next post, where i am going to share with you the process flow chart and the attributes of process.

    thanks a lot.

    regards

    zee

  8. #8
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Hi Mr. Rod,

    Please find here the Link for my main thread which covered all enquirers.

    https://www.accessforums.net/databas...tml#post144167


    Hope to have your valuable inputs.

    regards

    zee

  9. #9
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Zee,

    I'm willing to help but I think it's more appropriate to use email correspondence rather than cluttering this forum. I am therefore sending you my email address via a forum private message.

  10. #10
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Mr. Rod,

    Sure, see you there. regards

    zee

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

Similar Threads

  1. Need help to simplify this process
    By shanea.kr in forum Access
    Replies: 1
    Last Post: 07-10-2012, 01:40 PM
  2. Replies: 4
    Last Post: 05-10-2012, 07:35 AM
  3. Database Design for Specification Database
    By khwaja in forum Database Design
    Replies: 2
    Last Post: 11-24-2011, 03:58 AM
  4. Replies: 3
    Last Post: 03-28-2011, 11:49 AM
  5. Replies: 1
    Last Post: 06-23-2010, 10:52 AM

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