Relationships between tables 3

[mrfilez]

I am having to learn Access without training. So, at this point I feel really stupid, but here goes. This is for a Help Desk DB that I have to create.
I have finally gotten all of my info grouped the way I think it should go. I have the following tables.
tblName: UserID,First, Last
tblInfo: InfoID, UserID, Title, ReportsTo
tblSeat: SeatID, UserID, PodNum, PositionNum
tblComp: CompID, UserID, SerNum, ModNum
tblCompLoc: LocID, CompID, Loc, InUse
tblWarranty: WarID, CompID, War, DatePur, WarExp
tblProblems: TicketNum, CompID, Probtype, ProbDesc, Resolution
tblDate: DateID, TicketNum, DateReceived, DateCompleted
tblStat: StatID, TicketNum, Closed
tblTech: TechID, TicketNum, Tech

At this point I have read til my eyes are going to fall out, about linking these tables together and I can not grasp the concept of how it works. I keep getting confused. I can not figure out how I can enter in information and it will go to each field in each table as it is supposed to. Basically I have confused myself to the point that I started questioning whether I was actually sitting in my chair or not.
Any help that anyone could give would be ever so greatly appreciated. Also I was not sure what all I should put here. I did not want to make a really long post and take up the forums space, but I was afraid that noone would be able to help without the info I have given here. Please advise me on whether this post was ok.

Totally confused,%-)
Don

 

[ghubbell]

Don,
Your post is about as good as they get! Don't worry about quantity...just quality! BTW if you're doing this on your own you're doing great! ;-)

I'll try to get you started here and then maybe you'll be on your way:

Your structure is really tidy. The first relationship you'll need is from tblName.UserID to tblInfo.UserID . You decide: Do you want 1 user to have "many" "infos" or just one. If its the former, drag a join line (relationships window) from tblName.UserID to tblInfo.UserID, and define the relationship at a minimum to "enforce referential integrity". You can probably go all the way with this to "Cascade delete" as if the user is gone then you probably don't want to keep their info around either. If its the latter, mesh the tables together as one.

I'll take a look at tblSeat now. My guess is that you have 1 seat and it could, over time, have many users? If I'm correct then this is again a join line from your tblName.UserID to tblSeat.UserID...as before, one to many ,"ERI", "CUCD" as you prefer. If I'm wrong: 1 seat, 1 person period, then it could as well be meshed back with the first table.

I'll stop here as I'm a little lost on what you're up to with the whole scheme and would need further explanation. I will tell you though, In the event you are going to have say, 10,000 users but only 5 of them would use the "seat" info, you are better to leave the table seperate and make the join a "one to one". This really only saves space and does little else.

Once relationships are established, you will do the data entry through forms. By refering to other tables i.e: you want to know the userID to put it in to the UserID in the Info table, you might build a combo box that is based off of the User table. It might show you the user name but would actually hold the UserID. When you select "Gord Hubbell" you might get userID #101, and this ID would be placed (bound) in to your tblInfo.UserID field. With a relationship established you cannot install a userID in to the tblInfo.UserID unless it already exists in the tblName.UserID field. Same, same: You change Gords ID from 101 to 201, with cascade update, all of gords ID records will be changed. Ditto on the cascade delete: Remove Gord and all of the records GordsID is in will be removed too.

Please write back with your vision as from what I see in your structure, you have something pretty clear. I'm just a little foggy and don't want to lead you down the wrong path.

Hope this has helped you,
Gord
ghubbell@total.net

 

[mrfilez]

This is such a huge help!!! If nothing else than to have someone let me know one way or the other whether I am understanding this stuff and doing it right.
So, just to make sure I am understanding this correctly, tblName.UserID is a 1 to many relationship to tblInfo.UserID. Which in specifying a join it would include all records from tblInfo and only one record from tblName. For example:
UserX from tblName and Titlex, TitleY, TitleZ from tblInfo.
This is called a right outer join??

And away we go...
The users sometimes switch from manager to manager, and even get different titles. I would still need to look up the old information on them so as to keep track of the problems they have had or that the computers have had.
I am going to use this to see if certain problems follow certain users around. This DB is also a way to build a knowledge base of problems and resolutions. It will help me keep up with inventory also. There is no inventory list for the department I am in. I am basically having to step into the middle of a work "blizzard" without any documentation on anything and without knowing what I have to work with and if any of it is under warranty.
We now have more users than we have positions. How this works is beyond me, as they are only asking for 4 more stations and have ~15 people in training, that should be hitting the floor in a month or so.
Users will be sitting at different postions everyday. What I want from the DB, is to be able to pull up a persons name and see all the computers they have had problems with, what the problems were, and what the resolutions to the problems were. Same goes with pulling up a computer, or problem type.
If I need an Org chart, I should be able to pull it from this DB using a Query. I would like to be able to run a query to see what tickets are still open.

Each UserID should have an entry for each of the non-PK fields listed in all of the tables. Basically every field in the tables would have redundant data in them, if put into one table.

I hope that I have explained the DB at least to where you received the information you were expecting.
It has taken 3 weeks, working almost 40 hours a week to get to this point, which is one of the reasons I feel so stupid.

Thanks again,
Don

 

[ghubbell]

Don,
I know someone who took about 3 months to get to where you are so don't feel bad. He hangs out at Tek-Tips a lot now and, well, you figure it out ;-) . I'll study this a little tonight and see what I can see. There's lots of wonderful folks here and I hope we'll see their input too. Gord
ghubbell@total.net

 

[sko]

>I would still need to look up the old information on them so as to keep track of the problems they have had or that the computers have had.
I am going to use this to see if certain problems follow certain users around. What I want from the DB, is to be able to pull up a persons name and see all the computers they have had problems with, what the problems were, and what the resolutions to the problems were. Same goes with pulling up a computer, or problem type.

You will need to know the current user when the problem is reported so I would add a field in your problem table for userid (you may even want to call it 'reported by'. The same might be true of the computer location.

I would move the fields in tblDate and tblStat to the problem table since there seems to be a one to one relationship there.

tblProblems:
TicketNum, CompID, Probtype, ProbDesc, Resolution, UserID DateReceived, DateCompleted,
StatID (This could have values like In Progress, Completed, Waiting Scheduling)
TechID (Unless there can be more than one tech assigned to the problem)

My 2 cents worth,
Sharon

 

[mrfilez]

Thanks Sharon,

I am open to any and all suggestions. I have begun to think that my original Table designs were not quite right. I set them up that way because I would have Multiple problems on the same date and there are only two techs. Tech names would be repeated endlessly. but as I look back over some of the other databases I have access to, I see lots of redundant data in some of the tables. Which leads me to think that maybe my idea of how the concept worked was not quite right. So I am now trying to rework my tables. How many tables would you suggest using in this scenario? I think I may have it down to three. But I am unsure if this is wise or not.

Thanks,
Don

 

[sko]

Don,

You're off to a good start, you just need to consolidate some of your tables. Think of each table as storing information about a single subject with the fields describing the subject. I'd eliminate tblDates and tblStatus. This information is part of the problem description and belongs in the problem table.

You won't need a separate table for techs. You could keep them in the same table with users and identify them using your 'title' field.

Warranty date & date purchased is part of the equipment description and probably belongs in tblComp.

You will need to know where the computer is located so you will need to include a field in tblComp for the location. You will also need a field for the user. In both cases, all you will store in tblComp is the LocID and UserID. These will link back to tblLocations which would be a list of all available locations just like tblUser (tblName) would be a list of all users.

Do you want to create a mini-database to test some of these concepts?

 

[mrfilez]

If you would like I can e-mail you what I have so far. I reworked my tables. I made tblStat into a list of status's like you suggested. I made table Tech a list of techs. Both of these tables are called in look-ups for thier fields in tblProb. I merged tblInfo into tblName. I took tbl loc out, as I will know where the computer is based on the info from PodNum and PosNum, which is the Pod and Position number where the user is sitting. I had originally thought about having this setup to cover inventory, but that would not be a good idea. I am setting up a seperate Inventory DB. I am working on a copy of this at home(Access 2000) and here at work(Access 97). It is very interesting working with both versions at once.
Anyway, here is my new table structures.

tblName: UserID(pk), FirstName, LastName, Title, ReportsTo
tblSeat: SeatID(pk), UserID, PodNum, PosNum
tblComp: CompID(pk), UserID, SerNum, ModNum
tblStat: Stat
tblTech: Tech
tblProb: TickNum(pk), CompID, UserID, Probtype, Probdesc, DateRec, Tech, Stat, Resol, Resol2, Resol3, Resol4, DateComp

I have made a form with three subforms. frmName and its subforms are DataEntry only. I have a button for adding the record to the database and moving to a new record. I have also made certain fields required. Names, Title, ReportsTo, PosNum, SerNum, ModNum, Stat, ProbType, ProbDesc.

These are all fields that the tech needs to have before anything is done. The PodNum does not apply to all users so I did not make it required.

I am going to make a form for Editing an Open ticket. This is going to be probably my hardest challenge. It will require more code than what I have done to date. The Techs should not be able to go back and change data from the required forms, with the exception of Stat. I would also like for the techs to not be able to choose Closed under status until all fields have been filled in, with the exception of Resol2 thru 4.

I am sorry this post is so long. I get kinda caught up in this stuff. I am thouroughly enjoying the challenges of Access and VB.

Thanks,
Don

 

[sko]

>Both of these tables are called in look-ups for thier fields in tblProb.

I'm curious, are you using combo boxes or Dlookup?

Could you describe the fields in your problem table.
What data will ProbType hold? Is ProbDescription a memo field? What might I find in the resolution fields? What is a Pod Number?

 

[mrfilez]

This is a DB I am setting up for a Help Desk. I am left to do this because they will not buy something to keep up with the Tickets, and they have no way of doing it now. In the long run, the DB will make my job worlds easier.

I am using combo boxes. I do not know how to use Dlookup.

ProbType info is what type of problem occurred; Windows, Printer, Network, Netsvape, etc. Just what program or equipment is have a problem.

ProbDescription is a Text field(255 characters).

The Resolution field is for all steps taken to solve the problem. This is a text field(255 characters).

Most of my users sit in groups of 6 at round, partitioned desks. These are called Pods. Each spot in the partition is called a position.

I really appreciate your help and your time.

Thanks,
Don

 

[sko]

Don,
When do you sleep?

That's what I thought ProbType was. Add another table to your database to hold problem types. This can be the source for a combo box in your 'Repair Order' form. I know someone who is using Access to track fleet maintenance ;-)

What's your e-mail?

 

[mrfilez]

Sleep???? Is that something new?

From 7am to 3 pm CST my e-mail is f613493c@mailfxhome1.fedex.com
During that time and the rest of what everyone refers to as my life, the address is mrfilez@midsouth.rr.com.

If you could, try to CC my mrfilez account with everything. That way I can get to it at home also.

>ProbType table populating a combo box.
Yeah I had thought about doing that. I am trying to gather as many different software and hardware problems that they get at work.
I really appreciate your time and help.

Thanks,
Don