Fleet and Tooling db

[bopsgtir]

Hi all very new to access, but am very keen on learning the program as i believe it could really help out my business.



Really im just looking for some advice of table design to keep track of the following information

Tech Name
Tech ID
Tech User ID
Tech Email Address
Tech Phone Number
Start Date
Leave Date

Vehicle there in
Make
Reg
Lease Company
Date Hired
Date Off Hired

Vehicle Damage
Sign Off Sheet if Vehicle changes Driver

Tools assigned to Tech
Tools lost
Tools replaced

Im just trying to work out how many tables to make and how to link them

[ajetrumpet]

what kind of business do you run? looks like car repair? one opinion of your tables would be something like:

TECHS

• tid - PK
• tname
• temail
• taddress
• tnotes

ORDERS

• oid - PK
• customer
• customerinfo
• vehicleid - FK of VEHICLES (vid)
• onotes

VEHICLES

• vid - PK
• vmake
• vmodel
• vyear

ORDERDETAILS

• oid - FK of ORDERS (oid)
• workingtech - FK of TECHS (tid)
• damageamount
• damagenotes
• repairtime
• invoiceamount
• additionalnotes

TOOLS

• tid - PK
• tstatus (lost or not?)
• assignedto - FK to TECHS (tid)
• ttype

the orders would obviously be the repairs that would come into the shop. it's a simple hierarchy. FK means the 'many side' of a relationship to the PK field in the other table. that can be expanded upon of course, if more information has to be spread out further (more tables). e.g. - invoice details, customers, etc...

[bopsgtir]

Its actually telecommunications, i have 100 techs in hired vans and the idea is to keep track of all there tools to make sure firstly they were issued them and if they have then lost them they will be charged, also a history of the vehicles with sign off sheets if vans have moved drivers so when the van goes back to the lease company i know who was in a van and any damage cause when they were in it, also it helps when i get parking tickets from the council going back months.

[ajetrumpet]

your tables should really look like what I posted below, just different names and names of fields. the hierarchy remains generally the same. e.g. - one tech to many tools, etc, etc...

that's IMO of course.

[bopsgtir]

ok i will give that a go.

[bopsgtir]

Techs

Tech ids PK
Tech First Name
Tech Surname
Tech Email Address
Tech Start Date
Tech End Date
V Reg FK To V Reg

Tools

Tool Id PK
Tool Type
Tool Price
Tools Status
Assigned To Tech FK To Tech ID

Vehicle
V Reg PK
V Make
V Model
VHire Date
V Off Hire Date

Does that make Sense, i also need to note any Damage to the vans when the tech first goes into it and when he finallay comes out of it, what table would that go in

[ssanfu]

You shouldn't use spaces in the field names..... major headaches later.

Techs (Table)

Tech_ID (PK)
Tech_Badge_Num (Badge Number? - some way to identify which Tech - NOT SSN)
TechFirstName
TechSurname
TechEmailAddress
TechStartDate
TechEndDate
V_Reg (FK To V Reg)


TechVehicle (Table)

TV_ID (PK)
TV_Tech_ID (FK)
TV_V_Reg (FK)
TV_Date_Out (Assigned to Tech)
TV_Date_IN (Turned in by Tech)



Tools (Table)

Tool_ID (PK)
ToolType
ToolPrice
ToolsStatus
Assigned_To_Tech (FK To Tech ID)
DateToolOut (When Tool assigned to Tech)
DateToolIn (When Tool returned from Tech)


Vehicle (Table)

V_Reg (PK)
V_Number (How do you Identify this specific vehicle? License Plate? VIN? Company assigned number? All of the previous?)
V_Lease Company
V_Make
V_Model
V_Hire_Date
V_Off_Hire_Date


Damage (Table)

D_ID (PK)
V_Reg (FK to Vehicle table) (Long)
D_DateDamaged (Date)
D_DamageAmount (Currency or Single)
D_InvoiceAmount (Currency or Single)
D_InvoiceNumber (Text)
D_DamageNotes (Memo type)

Just some of my ideas. RED is what I added/comments.....

[bopsgtir]

thank you that makes sense. in tech table the tech id PK is not a autonumber it is a unique 5 digit number assigned to all techs so would that be an ok PK to use.

[bopsgtir]

in tech vehicle we would just use the van reg so is that ok to leave as the PK

[bopsgtir]

Also in tools, where you put tool returned by tech how would you determine if it was lost or returned due to wear and tear and lost will be recharged to tech but wear and tear would be no charge.

[bopsgtir]

here is my relationship report, this is the first time ive used access so bear with me,

does this make sense to you guys

[bopsgtir]

Sorry use this one as it didnt fit on one page

[ssanfu]

1) You might think about adding a junction table (Many to many) between Techs and tools. (One tech can have many tools and One tool type (multi meter) can be out to many Techs.)

Techs (Table)

Tech_ID (PK)
Tech_Badge_Num
TechFirstName
TechSurname
TechEmailAddress
TechStartDate (Hire)
TechEndDate (Term)



Tech_Tools (table)

TT_ID (PK)
Assigned_To_Tech (FK To Tech ID)
Tool_ID (FK to Tools table)
Date_Tool_Out (When Tool assigned to Tech)
Tool_Condition_Out
Date_Tool_Returned (When Tool returned from Tech)
Tool_Condition_In
Tool_Part_Num (if available: Bar code, SN, etc - optional)
Comments (Memo type field)



Tools (Table)

Tool_ID (PK)
ToolType
ToolDescription
ToolPrice

-----------------------------------------------------

2) In the Tech table:
* Doesn't the [Tech_Mobile_Number] belong in the Mobile_Phone table?

* You don't need the [Tech_Vehicle_Reg] field in the Tech table.