New Database Structure

[molsen123]

Hi All

I have been tasked with converting a spreadsheet to an access database. I thought it would be simple, but after some analysis, its not that easy. I am also self-taught, and after reading the commandments, I thought I would seek some advice!

I work for a growing firm of Tree-Surgeons (no pun intended), who service a sizeable chunk of the power grid in the UK. At any time, we have up to 150 operatives in the field, cutting vegetation around power lines. Because of the obvious danger, the power company need advance warning of where any teams will be cutting on any given day.

Various team leaders call in each day and give their intended locations for the following day to an individual who's responsibility it is to log the info onto a central spreadsheet, an Excel workbook. Each workbook contains a worksheet for each day (Mon-Fri). Each worksheet contains the following fields:

1. Number (Unique Numerical Sequence for the individual)
2. Name
3. Phone Number
4. Working/Not Working
5. Type of Line (132kv, Extra High Voltage (EHV), High Voltage (HV), Low Voltage (LV))
6. Area (in this case a lookup of 8 geographical areas)
7. SubStn Number (a name of exact location followed by nn/nnn) (LV Only)
8. Circuit and Pole Numbers (a location followed by nn) (HV Only)
9. Locality (nearest village/town)
10. Grid Reference (nn/nnn/nnn)
11. Time On-Site
12. Time Off-Site

So far I have created a table (Called Contact) for the individuals:

ContactID (Key)
ContactTeamNumber
ContactFirstName
ContactSurname
ContactMobileNumber
ContactTitle
ContactStatus

Individuals can be either:

Team Leader
Team Member
Sub-Contractor – Team Leader
Sub-Contractor – Team Member
Consultant
Surveyor/Manager
Manager
Private – Team Member
Private – Team Leader
Office

Team Members can move between teams, but Team Leaders remain largely static.

I guess I should create a separate table for Team Leaders and Team Members, with a one-to-many relationship between them? All of the others could be around the network but would not be attached to a Team Leader, so I guess they can be in the same table as Team Leaders for the purposes of tracking their location.

Also, what is the alternative to using a look-up for some of the fields, for example the Area field? I was concerned about breaking one of the commandments, and can’t see a reason why, but I await enlightenment!

Many thanks in anticipation of your help.

 

[plog]

I guess I should create a separate table for Team Leaders and Team Members

No. My suggestion is that you need to create a Team table. I don't know what fields you will need in it (possibly active dates), but it should have an an ID number. You will also need a table called TeamMembers that will thave fields for that Team ID, a ContactID (from your Contact table), and a role field which ties back to that list of what individuals can be.

Additionally you should remove ContactTeamNumber from your Contacts table because the TeamMembers table will now be taking over that data.

 

[gemma-the-husky]

seriously.

in a company persuade your bosses you need some professional help/training to get started. You do.

Access is not excel, and there is a steep learning curve to start.

on the positive side, access will handle your data in a much more controlled fashion than you can ever get with excel. and it is far far quicker for large amnounts of data.


one major hurdle is to stop thinking about using any calculation that relies on another row of data. So in excel where a value for cell B12 might be C11 * A12, or something similar - you need a totally different approach in access. ie row 12 needs to be considered as independent of row 11. indeed the underlying concept is that there IS NO absolute row 11 or row 12.