Bitten off more than I can chew?

[bumpydog]

Afternoon all,

Before I start I must point out that I'm a relative beginner.
I've been asked to set up (what seemed) a fairly basic database which would allow helpdesk operators to identify sites and on call staff related to them.
Staff are grouped into three categories and related to different sites according to which rota they belong to (the rota for staff and sites doesn't change).
This I could cope with, but only one of the staff related to each site will be on call at any one time.
What I'm aiming to do is to have a form with a combo box listing all the sites. When a site is selected either the on call staff from the three categories are shown in text boxes, or, a button is pressed taking the user to a seperate page with the on call staff displayed.

At present I have the following tables:
Site
Staff1
Staff2
Staff3
and three link tables between Site and the Staff tables.
The fields are fairly standard for all tables i.e. ID#, Name, TelNo etc and I have a OnCall field for the Staff tables with a Yes/No data type.

I just can't get the required info to be displayed.
Any help would be much appreciated (even if it is pointing out how stupid I am!)

Many Thanks,
bumpydog

 

[dhookom]

The first thing I would do is combine the three staff tables into a single table with a field that describes the rota or whatever.

If you want to find out how to cascade combo boxes, check some of the samples at

http://www.rogersaccesslibrary.com.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bumpydog]

I did try that Duane but I got a bit muddled.
There are three categories of staff:
Controller
Operator
Electrical
Each member of staff belongs to a rota e.g. 30 Controllers are grouped into rotas 1 - 5.
By having all staff in one table and fields Rota and StaffCategory isn't there quite a bit of redundant data?
Also when linking Staff to Site table Access told me that it was an indeterminate relationship.

As I say I am new to this so excuse any obvious lack of intelligence!

 

[dhookom]

You have really never provided an example of the fields in your tables or sample records.

Without more information, I would create a table of staff "tblStaff" that probably contains no scheduling, rota, group, or category information. Make sure you have a primary key such as StaffID. Use the StaffID field in other tables that store information regarding scheduling or rota or category.

Again, without more information, it's near impossible to assist.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[solun]

I agree with Duane - you need to figure out what tables you actually need...

Your staff table would have StaffID, Name, contact info, and category (if each staffer can only be a member of one category). If each staffer is also a member of just one rota, then it would be included in this table. You can use a lookup table for the categories (then if the company decides to rename the categories, you can easily rename them without messing up your code) and rota (what the heck is a rota?).

Then you can have a table of sites - Site ID, sitename, telephone, etc.

The interesting part is figuring out how and when the staff is assigned to the site.

Hope this helps.



Heidi I. Jones
Ridgerunner Consulting
Questions Answered. Problems Solved.

http://www.ridgerunnerconsulting.com

 

[lespaul]

having fields named:

Staff1
Staff2
Staff3

is usually a good indicator that your tables aren't normalized. See The Fundamentals of Relational Database Design for more on normalization.




Leslie

 

[bumpydog]

Thanks all for your responses.

I have (at present) the following tables & fields:
tblSite (Site#, Name, Address, Tel No)
tblController (Controller#, FirstName, Surname, Mobile#, ControllerRota, OnCall)
tblOperator (Operator#, OperatorRota and same fields as above)
tblElectrical (Electrical#, ElectricalRota and same fields as above)
tblControllerSite (Site#, Controller#)
tblOperator (Site#, Operator#)
tblElectrical (Site#, Electrial#)

Each site has a number of Controllers, Operators and Electrical staff assigned to it, grouped into rotas i.e. Site 1 has Controller 1, 2, 3 and 4 assigned to it (they are all part of ControllerRota 1), Site 2 has Controller 5, 6, 7 and 8 (who make up ControllerRota 2). Each site will also have an ElectricalRota and OperatorRota associated with it). Only one member of staff from each rota is on call at any one time i.e. the staff on call are the ones which need to be displayed in association with their corresponding site.
To explain it in real terms, a site has three groups of staff that are responsible for maintenance work outside of normal working hours. The helpdesk operators need to be able to check which members of staff are on call for a particular site at any given time.
Does that make any more sense?

The tables/fields I have allow me, on a form, to select a particular site (via a combo box) and view the rotas for that site with the current OnCall Controller, Operator and electrial staff checked.
Ideally I'd like to select a site and have only the current on call staff members displayed.

solun - a rota is a group of staff members who are responsible for a number of sites on designated days, taking it in turns to be on call

Thanks in advance for any kind souls willing to help.