Results 1 to 7 of 7
  1. #1
    GrayWolf is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Canada
    Posts
    12

    Table and Relationship Design

    Hello AccessForums.net members,



    I am not new to access, however I have been away from access for over 4 years. My business has assigned me a new task to build an access Db and I was looking for some ideas on table building and relationships.

    Any advice and suggestions are welcome.

    Here is my case:

    We currently have 3 excel spreadsheets that we use to track 3 different types of Objects:

    1 Incident Tickets
    2 Problem Tickets
    3 Change Tickets

    I am tasked to build a DB that will replace the use of the the 3 separate excel spreadsheets because a large number of these ticket types are related, but have very little similarities between them. I cannot revamp the system and change the current data fields used for these ticket types and therein lies my problem.
    Basically I was hoping to get some ideas on how to get started on building these tables and relationships. This seems to be a daunting task at this point and I have spent quite a bit of time reviewing the "basics" for access and researching relationships.

    Here is a very brief description of the ticket types;

    1 Incident tickets:
    These tickets are the day to day issues that happen within the business. We address and fix/escalate the issue.

    2 Problem tickets:
    These tickets are created when enough "Incidents" have been created regarding a single issue that is not an isolated case.

    3 Change Tickets:
    These are generated simply to note that an updare of fix will be implemented into one of our systems.

    The Relationships info:

    Change tickets can be related to Problem or Incident or, as I have seen often, neither. The change is being done as a routine upgrade or patch/fix.

    Incident tickets are the same, they can be related to Problem, Change or again, neither if it is a single isolated case.

    Problem Tickets must have 1 or more Incident tickets related (no exceptions) but may or may not be related to a Change.

    A few of the Tabled and Fields I have to work with, unfortunately thre are very few uniqie fields that relate between the 3 tables, which is really frustrating me for the present:

    tblIncident
    IncidentID - autoNumber
    IncidentNumber - textfield
    Severity - textfield
    Description - textfield
    StartDate - Date/Time
    EndDate - Date/Time
    AssignedTo - textfield
    Related - (not sure - but this field should house the Problem/Change Records that are related to this Incident)

    tblProblem
    ProblemID - autoNumber
    ProblemNumber - textfield
    Description - textfield
    ReporttDate - Date/Time
    AssignedTo - textfield
    Related - (not sure - but this field should house the Incident/Change Records that are related to this Incident)

    tblChange
    ChangeID - autoNumber
    ChangeNumber - textfield
    ApplicationImpact - textfield
    ChangeDate - Date/Time
    Related - (not sure - but this field should house the Incident/Problem Records that are related to this Incident)

    Will I need to build a separate, 4th, table to house the original lodging of a ticket and if so how? Do I also need another table so store the "Related" Field data for use in sub forms?

    Thank you for taking the time to look at this post. I look forward to any assistance and advice.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Sounds like many-to-many relationships.

    Each incident can have many problems or changes, each problem/change can have many problems/incidents?

    This requires junction tables to associate records. However, seems less straightforward with your described data relationships because they are not 'chained'. Incidents don't just feed up to problems (they can go straight to changes or nowhere). Problems must have incidents but might not elevate to changes. Changes might have incidents and/or problems or none.

    Google: access incident tracking database
    You might find something already built you could use or learn from.
    Here is one possibility http://epadata.epa.state.il.us/land/ust/
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    And here is a Help Desk model that might help:

    http://databaseanswers.org/data_mode...desk/index.htm

  4. #4
    GrayWolf is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Canada
    Posts
    12
    Quote Originally Posted by ssanfu View Post
    And here is a Help Desk model that might help:

    http://databaseanswers.org/data_mode...desk/index.htm
    Thank you very much for the assistance, however when I attempt to access this website I am getting security errors. I appreciate your effort.

  5. #5
    GrayWolf is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Canada
    Posts
    12
    Quote Originally Posted by June7 View Post
    Sounds like many-to-many relationships.
    ...
    Google: access incident tracking database
    You might find something already built you could use or learn from.
    Here is one possibility http://epadata.epa.state.il.us/land/ust/
    Hi June7,

    Thank you very much for the suggestion. I was thinking the same, using many to many relationship. I spent most of the night pulling apart the example you linked. So far has not helped with my situation.

    With this example it deals specifically with 1 ticket type which is relationshipped all over the place in there .. he he.

    In my situation I am looking at 3 ticket types and I think part of my confusion is trying to come up with a way to do this without having any empty fields, which may be impossible.

    Curretly I have the 3 tables ... if I am looking at creating a 4th table as the junction, are you able to suggest a method to set up the 4th table and how I would relate the others?

    Thank you.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    This is a data structure I have no experience with and even attempting to design is challenge don't want to tackle. However, will say think might require more than one junction table.

    This link will open the site referenced by ssanfu but the link to the specific Help Desk model errors. http://databaseanswers.org/data_models/index.htm
    However, this will open the model image http://databaseanswers.org/data_models/help_desk/
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    GrayWolf is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Canada
    Posts
    12
    Thank you very much. I will have a look through this information.
    Take care.

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

Similar Threads

  1. many-to-many relationship to a single table
    By roleic in forum Database Design
    Replies: 2
    Last Post: 08-01-2012, 03:41 PM
  2. Relationship Table Help
    By Nick F in forum Database Design
    Replies: 5
    Last Post: 10-10-2011, 01:28 PM
  3. Multiple Table Relationship Design
    By neo651 in forum Database Design
    Replies: 1
    Last Post: 09-30-2011, 01:16 AM
  4. Relationship Design
    By krymer in forum Database Design
    Replies: 3
    Last Post: 11-28-2008, 09:09 PM
  5. design using relationship...
    By dsnyder in forum Database Design
    Replies: 2
    Last Post: 10-21-2008, 12:00 PM

Tags for this Thread

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