Results 1 to 7 of 7
  1. #1
    blueraincoat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2011
    Location
    Shanghai,CHN
    Posts
    99

    Search what step is the repair work in

    Hi all,
    I'm working on a repair system. The who repair process have many steps like analyze,repair,clean,assemble,test and so on.And there maybe many units under repair but in different step.I would like to add an function in my main board so when I search by the repair id I could know which step is the unit in.
    The only way I could think of is adding a field in every table and changing the filed to yes and the others to no when repair process changes.
    But this seems a little bit compliacted, I'm not sure if there's a simple way to achieve this.
    Any comments is appreciated.Thanks in advance.

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Do you have a date associated with each step? If so, you can use that. Or does each step occur in a specific order?

  3. #3
    blueraincoat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2011
    Location
    Shanghai,CHN
    Posts
    99
    Yes, I have a date associated with each step. So I should search for the date which is null, and the unit should the be in the previous step, am I right? In this case, would the default value of the date field set to Now() be OK?

    The whole repair work is under a same specific order.

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    It sounds like you have a completion date for each step, and when a new step is started you add a record with a completion date that is null. Correct?

    If so then, yes you could use that since for an repair item there will be only 1 step with a null completion date. You can use a query to pull the record for each repair item where the date field is null.

  5. #5
    blueraincoat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2011
    Location
    Shanghai,CHN
    Posts
    99
    Sorry for my late reply.

    Actually the date field I have is the record date. When I finished one step of the repair work. I enter the data including the completion date.
    So I think may be the best way is to use select case. If the date of the fourth step is null and the date of the 3rd step is not, then the repair work must be in the fourth step.

    By the way, in the idea you gave, how can I get multiple fields form multiple tables through a query?

  6. #6
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    By the way, in the idea you gave, how can I get multiple fields form multiple tables through a query?
    In a relational database, tables hold data that are related to each other, for example, I assume that you have a work order table of some sort and related to a work order are the steps to complete it. Since a work order can have many steps, that describes a one-to-many relationship in database lingo. So a typical structure may look something like this:

    tblWorkOrders
    -pkWorkOrderID primary key, autonumber
    -WorkOrderNumber
    -fkCustomerID foreign key to a table that holds the customer information; all foreign key fields should be a long integer number datatype
    -dteWorkOrder (date of the work order)

    tblCustomers
    -pkCustomerID primary key, autonumber
    -txtFName
    -txtLName
    -txtAddress
    etc.

    I would assume that the steps you mention like analyze, repair, clean, assemble, test would apply to many work orders, so instead of typing them over and over again for each work order, it would save you (and the other users of the database) time to just put them in a table with each step being its own record.

    tblSteps
    -pkStepID primary key, autonumber
    -txtStep

    Now you need to relate the appropriate steps to the work order. A work order can have many steps (so a one-to-many relationship). Also, a step can apply to many work orders, so we have a second one-to-many relationship. When you have 2 one-to-many relationships between the same two table, you make the relationship using a junction table which I show below:

    tblWorkOrderSteps
    -pkWOStepID primary key, autonumber
    -fkWorkOrderID foreign key relating back to tblWorkOrders
    -fkStepID foreign key relating back to tblSteps
    -dteCompleted (date completed)

    Tables are joined to one another via the primary key-->foreign key relationship. You would build these relationships in the Relationship Window in Access. So going back to your question about bringing multiple fields from multiple tables together, you would create a query. In the query, you can add one table, two tables or more or even other queries. If there is a relationship between the tables and you bring them into a query, you can select any field from any of the tables that make up the query to show in your results. You can base forms or reports on queries so that you can bring all the related information together in one place. But for now, I would just concentrate on getting the tables set up properly because without a sound table structure everything else will fall apart.

  7. #7
    blueraincoat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2011
    Location
    Shanghai,CHN
    Posts
    99

    It seems that I have been absent-mindedness lately.
    Thanks for the detailed explanation.

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

Similar Threads

  1. !!!!Urgent!!!! Search code doesn't work!
    By Laetilae in forum Programming
    Replies: 4
    Last Post: 12-13-2010, 10:34 PM
  2. Repair DBthrough VBA
    By Riamor in forum Programming
    Replies: 6
    Last Post: 08-14-2010, 02:01 PM
  3. my search form won't work?
    By sams in forum Forms
    Replies: 5
    Last Post: 05-06-2010, 02:08 PM
  4. Is There a Way to Debug or Step Through VB code?
    By jeffbase34 in forum Programming
    Replies: 1
    Last Post: 05-28-2009, 08:14 PM
  5. Replies: 4
    Last Post: 04-29-2009, 04:59 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
  •  
Tech Forums: Microsoft Office Forums