Results 1 to 8 of 8
  1. #1
    ctonline is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    10

    Linking Usage dates table with Incidents table

    I have one table that shows the dates that an employee took off for a business trip and another table to show what type of trips and how long of the trip. The employee may have more than one trips or incidents.

    How do I link the trip number or Incidents table to the dates that the employee took off for it on the usage table? I want to link Trip 1, Trip 2, Trip 3 to what dates the employee had used for it.

    I currently manually put in a record number for the Trips. ie Trip 1 = Record 1, Trip 2 = Record 2, Trip 3 =Record 3 and on the Usage Table Record 1 to match the Trip 1 on each of the days he took for that trip.



    I want to be able to determine from this link how many days and what dates was taken for a specific trip number the employee took.

    How does a standard database normally set up to link an incident to the usage dates for it? What would be the suggested field for me to set up for this? Can it be done automatically or would I have to manually input it?

    Thanks.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Some sample data AND the desired results will help. I find the easiest is to use Excel and paste it here - you get a table that you can tweak if you need to.
    Try not to use jargon or intermix your terms. I can't tell if a trip and an incident are the same thing or not.
    At least, you need a table for users, one for trip types (that's optional, but will help you control what's in it) and one for trips. The trips table can hold user ID as a foreign key, trip type as a foreign key, and the dates. Other fields like comments/notes could be in this tblUserTrip as well. Once you have the requirements (whatever that may be) you use forms to control data inputs.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ctonline is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    10
    I will have a User ID field to on each 3 tables to link them; employee table, Trips table, and Usage dates table. I want to link the record or trip ( ie trip 1, trip 2, trip 3) taken to the dates taken for that trip.

  4. #4
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    It's still not clear what your requirements are. An employee table is simple and we all understand that.
    A trips table I assume would have a destination, a departure date, a return date, and some other descriptors.
    Whats the Usage dates table? Is that a junction table between employee and trips?
    Can more than 1 employee take a particular trip?

    As micron stated, some sample data would be helpful.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  5. #5
    ctonline is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    10
    I am sorry if it was unclear, but this is just a scenario, so I don't have the actual data. The Usage table would have the field "employee ID" and "date" (each date that he was out on a trip), the 3rd field would be "Record" I want to link this "Record" field to the "Record" field on the Trips table showing each of the trip the employee took (Record 1 = Trip 1). The problem with doing it this way is that I have to do it manually entering the record number on the Trip Table and the Record number on the Usage Table. The output I would want to display on a form is to show only the dates from the Usage table matching the Record from the Trip table.

    Trip Table Usage Table
    Employee ID 123 Employee ID 123
    Trip Start 2/1/2020 Date 2/1/2020
    Trip End 2/2/2020 Record 1
    Record 1
    Employee ID 123
    Date 2/2/2020
    Record 1
    Employee ID 123 Employee ID 123
    Trip Start 3/11/2020 Date 3/11/2020
    Trip End 3/13/2020 Record 2
    Record 2
    Employee ID 123
    Date 3/12/2020
    Record 2
    Employee ID 123
    Date 3/13/2020
    Record 2

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    You need tables like:
    tblEmployees: EmployeeID, ForeName, LastName, ...;
    tblEmployeeActivities: EmployeeActivityID, EmployeeID, ActivityType, ActivityDescription, DateFrom, DateTo, ...; (ActivityType may be e.g. smallint field with values 1 for business trip, and 2 for incident, ...)
    tblCalendary: CalendaryDate, ...; (you may prepare this table for some number of years into future)
    probably a table tblEmployeeStatuses: EmployeeStatusID, EmployeeID, EmployeeStatus, DateFrom (EmployeeStatus may be e.g. smallint field with values 1 for emplyed, and 0 for unemployed) too - but this is outside of current therad's theme.

    Now to get dates for employee having any activity, you need a query from tblCalendary with tblEmployeeActivities linked through LEFT JOIN with condition CalendaryDate being between DateFrom and DateTo in tblEmployeeActivities for this activity.

  7. #7
    ctonline is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    10
    Thanks Arvil. The tblCalendar you had mentioned I may not need, because the dates he is out can actually be imported from his attendance or timesheet record.

    Another issue is that his trip may be broken up into different dates and he may not be out continuously. Trip 1 may be taken from 2/1/20, 2/2/20 and 2/5/20, 2/6/20 and it would still be consider the same trip 1.

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by ctonline View Post
    The tblCalendar you had mentioned I may not need, because the dates he is out can actually be imported from his attendance or timesheet record.
    You wanted to avoid entering all dates manually - did you? Either you change your data structure so it will be normalized for relational database, or you have to register every date manually.
    And there will be possible future problems with current data structure, like the need to redesign your reports again and again, whenever something changes.

    Quote Originally Posted by ctonline View Post
    Another issue is that his trip may be broken up into different dates and he may not be out continuously. Trip 1 may be taken from 2/1/20, 2/2/20 and 2/5/20, 2/6/20 and it would still be consider the same trip 1.
    When this is the case, the only way to handle this effectively is to have database structure normalized, and to have an additional table.

    tblEmployeeActivities: EmployeeActivityID, EmployeeID, ActivityType, ActivityDescription;
    tblEmployeeActivityPeriods: EmployeeActivityPeriodID, EmployeeActivityID, DateFrom, DateTo.
    You can have any number of time periods registered for same activity. And you can design reports which can handle any number of activity periods too.

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

Similar Threads

  1. Replies: 3
    Last Post: 02-25-2016, 02:56 PM
  2. Include field in table or create for each usage?
    By louise in forum Database Design
    Replies: 3
    Last Post: 09-05-2015, 10:45 AM
  3. Relationships and table usage
    By gwboolean in forum Access
    Replies: 10
    Last Post: 09-04-2015, 04:37 PM
  4. Replies: 1
    Last Post: 01-20-2015, 12:27 PM
  5. Replies: 5
    Last Post: 07-12-2014, 02:55 PM

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