Results 1 to 3 of 3
  1. #1
    BPWags is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    1

    Access 2016 - Relational Database

    I will try to ask my question succinctly.

    I am charged with maintaining an employee roster. These employees represent several different departments and have various roles in the organization.


    Likewise, I am charged with maintaining an inventory of all vehicles. Again, vehicles are assigned to several different departments for use by employees with various roles.




    Things like credentials, demographics, seniority dates, drug tests, accidents etc., need to be tracked for employees in addition to the vehicles to which they are assigned.


    Purchase information, annual mileage, maintenance costs, etc., need to be tracked for all vehicles.


    I am assume this would require multiple related tables. Would it make sense to have one database for all of this data? Would it be easiest to utilize forms for data input by separate individuals?


    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    tables:
    tEmpoyees, tDepts, tVehicles, tVehWork.

    tVehicle table:
    VIN (key)
    Make
    Model
    VehType (car,truck,SUV, etc)
    DriverID (who has the vehicle..EmpID, Null = free)
    Dept

    tVehMaint table (of work history done)
    VIN
    WorkDate
    WorkDone (brakes, oil chg, etc)
    WorkID (who performed work)
    etc

    tEmpTest table:
    TestID (auto)
    EmpID
    TestDate
    TestDone

    tVehHistory table:
    VIN
    DriverID (who took it)
    CheckoutDate
    ReturnDate
    Destination
    Damage
    etc

    tVehAccidents table:
    VIN
    AccDate
    AccDamage
    Driver

    the Vehicle form can have subforms for Accidents, Maintenance

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Yes, multiple related tables. I would start with 1 database.

    Always, always use forms for data entry. Users should never be able to access tables directly.

    Start by designing the tables & relationships using pencil and paper, whiteboard, etc.

    "Old Programmer's Rule" is this:
    If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Database Won't Open In Access 2016
    By VariableZ in forum Access
    Replies: 2
    Last Post: 12-29-2016, 01:17 PM
  2. Replies: 2
    Last Post: 06-19-2016, 12:32 PM
  3. Replies: 2
    Last Post: 04-27-2016, 06:20 AM
  4. Replies: 4
    Last Post: 03-31-2014, 11:47 PM
  5. Replies: 1
    Last Post: 01-17-2013, 07:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums