Help Desk database 2

[Rustaferd]

I am a beginner with Access. The best way to learn is to find a project and work through the process. I did this with a Help Desk database I created.

Now I am working on redoing the access database I created on the fly for the Tech Support logs. I've got a spreadsheet that I have the breakdown of all the tables I think I will need. I have a spreadsheet breaking down the forms as well.

I could use some help later in making sure I am linking or joining the tables appropriately. But before I get ahead of myself there are a couple of questions that have come up that I should get answered before moving forward.

I have created a table called RootCause.
I have also created a table called CallResolution.

What I would like to do whenever I open a new issue I have to assign a root cause to the issue from the dropdown menu (linked to RootCause table). When I select this root cause, I want a list (history) of all the resolutions made for this root cause.

I don't know if this is clear but I am not sure of the best way to do this. Do I need to link the RootCauseName of the RootCause table or the RootCauseID of that table to the CallResolutions table so that whichever root cause is selected for the issue, a list of call resolutions linked to the RootCause table will be listed. From that list I can choose which Call Resolution fits the call issue and select that for my call record - or I can simply add to the Call Resolution.

Second, I would like to add images or files to a database call record so that the record will have an image or file that is related to the issue giving a deeper call record. Any idea on how to do this? What kind of table do I need?

I hope this is clear enough. I hope there is someone out there that can help me or direct me.

Thanks,
Russ
russb@lominger.com
952-345-3650

 

[PaulBricker]

First, you will have to have some relationship between RootCause and CallResolution so that specific Causes are related to specific Resolutions.

Second, Access doesn't handle images (you don't actually say what kind) well because of the memory needed to store them. What you want to do is store the Path to the image in a Table and then use an image control on your form to display the image.

Paul

 

[Rustaferd]

Thanks Paul.

I was thinking that I would need to set up a link from RootCause table to CallResolution table. However, what I am not sure is if I need to create some sort of subform to display the list of linked data related to the RootCause. Then find a way to select from this list to populate the call record's resolution field.

Any further thoughts?

Russ

 

[PaulBricker]

Just to clarify my second point first, you would store the image in a folder on your drive and not in Access. Then store the path in a Table.

The relationship between the two tables would be something like this

In the RootCause Table you might have
CauseID 'Primary Key FD12345
CauseDescription Failed Disk

In the CallResolution table you might have

CallID 'PrimaryKey CID121603
CauseID 'Foreign Key FD12345
ResoluitonDescription Cleared obstruction

CallID CID21345
CauseID FD12345
ResolutionDescription Clean out Peanut Butter sandwich

Both CallID's have the same CauseID but with different descriptions. You could use these in cascading combo boxes where the user selects a RootCause.CauseID from Combo1 and then Combo two's Row Source becomes
Me.Combo2.RowSource = "Select CallID, ResolutionDescription From CallResolution Where CallResolution.CauseID = '" & Me.Combo1 & "'"

This would return CallID's CID121603 and CID 21345
Or if you want a form/subform then your Master/Child link is on CauseID.

Paul

 

[Rustaferd]

Paul,

Thank you so much for taking the time to break down this question with your example. Keeping in mind that I am just a beginner at Access, I think I have a pretty good understanding of what you are talking about here. Of course the best way that I can learn is by seeing and doing. So I will attempt to create some small table and forms to test whether I do understand your solution or whether I am still in need of further clarification.

Thank you so much for your help.

Russ

 

[PaulBricker]

You're welcome. Once you get things rolling, if you have questions, post them with some sample data so that we have a better idea what you data looks like. I was just guessing at examples. Actual data will make it easier for everyone to understand.

Paul

 

[Rustaferd]

Thanks, I will do that.

 

[Rustaferd]

I have created a rough draft of my access Help Desk, but have some questions about a subform that is not showing up. How can I post the access files or screen grabs?

 

[PaulBricker]

My understanding is you have to post them on your website for viewing. You can't do it on Tek-Tips.
What is happening with the subform.

Paul

 

[Rustaferd]

I don't have a web site to post them on,but could email any info needed.

What is happening is the subform is not displaying in Form View, but does show up in Design View mode.

My guess is that you would need to see the access database at this point as it would be tough to guess why this is happening without digging into it.

Thanks,
Russ

 

[PaulBricker]

Well, there has been a lot of discussion lately about taking things off line. We've been encouraged to keep it at Tek-Tips if at all possible so I would like to start there and only work off line as a last resort. Tell me what the process is to view the forms and exactly what should be happening that isn't. Also, it helps to post the actual names of the forms/tables/etc that you are using so that any suggestions don't have to be translated from alias' to actuals.

Paul

 

[Rustaferd]

I'll first post my tables, then follow up with my forms, then follow up with what I am trying to do with them.

Here is a list of my tables:

Tables:

Call Field Name Data Type Description
CallID AutoNumber Internal Tracking ID
CallPersonID Number Link (FK) to valPerson
CallIssueDescription Memo Detailed call description
CallStatusID Number Link (FK) to valCallStatus
CallSourceID Number Link (FK) to valCallSource
CallIssueTypeID Number Link (FK) to valCallIssueType
CallSeverityID Number Link (FK) to valCallSeverity
CallIssuenameID Number Link (FK) to valCallIssueName
CallOperatingSystemID Number Link (FK) to valOperatingSystem
CallProductNameID Number Link (FK) to valProductName
CallProductVersionID Number Link (FK) to valProductVersion
CallSolutionDescription Memo Detailed call resolution description
CallDateOpened Date/Time Date the call was created
CallDateClosed Date/Time Date the call was closed
CallDateReopened Date/Time Date the call was reopened

valCallStatus Field Name Data Type Description
CallStatusID AutoNumber Internal tracking ID
CallStatusDescription Text Such as Open; Closed;

valCallSource Field Name Data Type Description
CallSourceID AutoNumber Internal tracking ID
CallSourceDescription Text Such as Phone; Fax; Email; Note;

valCallSeverity Field Name Data Type Description
CallSeverityID AutoNumber Internal tracking ID
CallSeverityDescription Text Such as Critical; Severe; Significant; Marginal;

valCompany Field Name Data Type Description
CompanyID AutoNumber Internal tracking ID
CompanyName Text Name of company
CompanyAddress1 Text "Address, line 1"
CompanyAddress2 Text "Address, line 2"
CompanyCity Text Company city
CompanyState Text Company state
CompanyZipCode Text Company zip code
CompanyCountry Text Company country
CompanyPhoneNumber Text Company phone number
CompanyIntlPhoneNumber Text Company international phone number
CompanyFax Text Company fax
CompanyNotes Memo Company notes
CompanyURL Text Company web site

valPerson Field Name Data Type Description
PersonID AutoNumber Internal tracking ID
PersonName Text Name of Caller
PersonCompanyID Number Link (FK) to valCompany
PersonAddress1 Text "Address, line 1"
PersonAddress2 Text "Address, line 2"
PersonCity Text Person city
PersonState Text Person state
PersonZipCode Text Person zip code
PersonCountry Text Person Contact country
PersonPhoneNumber Text Person Phone number
PersonPhoneExt Text Person Phone extension
PersonIntlPhoneNumber Text Person International number
PersonCellPhonePager Text Person Cell phone number or pager number
PersonFax Text Caller Contact fax
PersonNotes Memo Person Contact notes
PersonEmailAddress1 Text Person e-mail address 1
PersonEmailAddress2 Text Person e-mail address alternative
PersonTypeID Number Link (FK) to valPersonType

valPersonType Field Name Data Type Description
PersonTypeID AutoNumber Internal tracking ID
PersonTypeDescription Text Such as Tech; Rater; Admin; Associate;

valProductName Field Name Data Type Description
ProductNameID AutoNumber Internal tracking ID
ProductNameDescription Text Helpful description for specified product

valProductVersion Field Name Data Type Description
ProductVersionID AutoNumber Internal tracking ID
ProcuctVersionDescription Text Helpful description for specified product version

valOperatingSystem Field Name Data Type Description
OperatingSystemID AutoNumber Internal tracking ID
OperatingSystemDescription Text Helpful description for specified Operating System

valCallIssueName Field Name Data Type Description
CallIssueNameID AutoNumber Internal tracking ID
CallIssueName Text Use this field to assign names to prevalent issues
CallIssueNameDescription Memo This text is used to populate the Call Issue Description field

valCallIssueType Field Name Data Type Description
CallIssueTypeID AutoNumber Internal Tracking ID
CallIssueTypeDescription Text Root Cause for the Issue

Note Field Name Data Type Description
NoteID AutoNumber Internal Tracking ID
NoteCallID Number Link (FK) to Call table
NoteDate Date/Time Date the note was created
NoteEntry Text Note Text


I will post the list of forms. (Sorry - it looks like the formatting here is not going to make it easy to read)

 

[Rustaferd]

Note: The forms are very similar as the tables. I'll try to send this out later.

The first form that displays is the Main form.

In this Main form I will have a field called Call Ticket ID which is from the CallID field in the Call table.

In the Main form I will have an Opened field, a Closed Field, and a Reopened field.

In the Main form I will also have a Caller Field, which is from the CallPersonID field linked to the Call table. This field is where you can enter a person's last name. If the person is in the database a list pops up and you can select the Caller. If the name is not in the database a message displays: "joe" is not in the list. Add it? If you select Yes then you are brought to the ValPerson form where you can add the Caller's first and last name along with all the other info the Caller may have (phone, fax, email, address, state, city, etc.). Once entered you will be brought back to the Call form where the subform displays the ID,Phone,Extension,Email,Company,Type (type of caller).

The problem that I am having currently is the subform is not displaying in the Call form except in design-view

Note: There are other fields as well in the Call form, but wanted to focus on this subform issue.

Thanks,

 

[BSman]

When you return to the main from from your form to add a new caller you have to include the code to force the subform to requery (or refresh). That way it should find the new data.

 

[Rustaferd]

http://home.comcast.net/~rustaferd/wsb/index.html

The above web site should show the Main form and then a link that shows the Main Caller Subform.

 

[PaulBricker]

The picture does help. I assume that the subform for the caller info is supposed to show just below the Combobox for Caller. Do you have the LinkChild/LinkMaster properties set up? To do that, you can click on the subform in design view ONCE, the look in the properties box for Link Child, Link Master. Make sure you have a relationship set up there. Also, make sure the Visible property is set to Yes for the subform.
Let me know about the LinkChild/LinkMaster settings first and we'll go from there.

Paul

 

[Rustaferd]

I got it to display when I removed the Link Child and Link Master fields, leaving them blank.

Here is what I have:

Source Object: MainCaller
Link Child: blank
Link Master: blank
Enabled: Yes
Locked: No

In View mode:
The company field has a #Name?
The Type field has a #Name?

Now what I am noticing if I try to enter a name in the Combobox for the caller I get an error message:
The value you entered isn't valid for this field. For example you may have entered text in a numberic field...
I did notice there is a 0 in the Combobox field which is another quetion why?

Thanks,
Russ

 

[PaulBricker]

If you just want to display info about the caller, why do you need a subform. Generally, subforms display multiple records associated with a specific Key value. Like
CustID 1234 'Main Form value
PurchaseDate Item
12/27/03 Boots
12/28/03 Gloves
12/29/03 Hat
12/30/03 Scarf

So CustID is the Main Form and PurchaseDate and Item are fields in the subform. If you want to display address, phone etc about a Caller on the Main form, just put all that info on the Main Form with the caller and use the Column Property of the Combobox to display the related info. The way it works is you put all that info in the underlying Row Source for your Caller Combo Box. YOu set the Column Count property to however many fields there are and set the Bound Column to 1 and the Column Width property to
.5";0";0";0";....0"

Then the control source for each textbox is set to
=ComboName.Column(1)
=ComboName.Column(2)
.
.
.
=ComboName.Column

Combo boxes are zero based columns so 0 is the first column, 1 the second, 2 the third and so on.

Paul

 

[Rustaferd]

Thanks paul that was useful information. I guess I felt that I had to use a subform because I was trying to pull out a subset of information from the Caller's data, not all of it. What you suggested makes sense. I will give this a try to see how it looks and works.

I appreciate your time and effort to help me understand Access...Not everyone will take this kind of time and I deeply appreciate it!

 

[PaulBricker]

Good luck. It should present much trouble but post back with any issues you run into. My explanations aren't always clear and precise, even to me.

Paul