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.