Results 1 to 6 of 6
  1. #1
    crimedog is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    141

    Handling Historical Data with different assignments

    I have a Table Reps - that shows Rep, Manager, Start Date, Exit Date etc...


    Also Table Manager - that has Manager, Start Date, Exit Date
    AND Table Sales - - Customer, Rep, Manager, Amount, Date etc...

    Right now it is working great - I have a Form to enter Sales that has a combo Box to choose Rep (No Manager choice because I figured that would come from the Rep Table)

    Now - I realize that as tha Data ages - Or if I go back and dump last years info, that the Manager Assignments have changed - And yet when I pull a report on Last year's data I want to pull sales for Manager based on last year's assignments not the current assignments

    Also - as reps leave and the exit date becomes not null, I want the ability to choose the filter for the combobox cboRep to have a include retired reps option (maybe a check box - if checked would add a filter comboBox row source by not null in exitDate field)

    What do you suggest?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Perhaps a foreign key in the sales table to hold the PK value of a relative manager will do the trick. When dept assignments, active Booleans, and such change the ability to accurately query by date parameters I will create a permanent relationship using an FK. Examples may be a vehicle assignment during a specific task, a project manager during a specific phase, a sales associate during a specific shift.

    A vehicle, manager, or sales associate may change, relative to the event documented within the table. However, a vehicle may be assigned to a different person or need to go for maintenance, a manager may get a promotion, a sales associate may be relieved for break.

    Permanently storing the relative PK in an FK for the event will manage the data for historical reference.

  3. #3
    crimedog is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    141
    So, use a bridge table to make the manager in the Rep Table and the manger in the Order table FK (foreign keys)?

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    You could create a junction table to supplement queries. If you create a record for an event, you can include key values from relative tables/fields. You may be able to create a record in one table for events or you may need a junction table if you are running into a many to many situation.

    I would start with a single table and some FK's to log events. I would then imagine a scenario and role play how the user would perhaps add records. If it feels like adding Many FK fields in a single record, where the many FK fields represent a single PK FIELD in another table, then you have discovered a need for a junction table. ; EDIT: You want many on both tables for the need of a junction table. Both sides have many records that relate to each other in one way or another. (hard to describe until after you stumble across the scenario)

    It depends on the specific event and the workflow. Analyze this to discover the answer. You do not want to commit to spreadsheet. In other words, add a column to an event table to manage numerous shift changes, vehicle assignments, manager promotions.....

  5. #5
    crimedog is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    141
    Ok Bridge table works - Now, How to handle reps that leave.
    Currently on my Sales Order Form (which relates to Sales Table) I have a combo Box (cboRep) to add rep that is populated from the RepT Table Rep field
    works great - And I can figure out how to filter the Rep field where Exit Date is Null... Now how to get it to change to another filter that disregards exit date when you check a box, include retired Reps

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    I would manage this using VBA. So you would have to start building something to get an answer. So many ways to approach it. The specific answer to the question, how you state it.... is to address this in the application design side of things.

    Having said that. Without proper normalization of your data, the application will not function correctly. Regardless of how good you are with SQL and VBA, you will have issues if the relations are bad.

    So, if you can get what you need, or close to it when you look at a query, filter this, filter the other, and sort this way... then build a form.

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

Similar Threads

  1. Replies: 87
    Last Post: 05-21-2013, 11:48 PM
  2. Column assignments in an INSERT INTO type of query.
    By tfulmer in forum Import/Export Data
    Replies: 3
    Last Post: 05-01-2013, 01:42 PM
  3. historical dates for
    By fabiobarreto10 in forum Forms
    Replies: 35
    Last Post: 01-08-2012, 06:03 PM
  4. Historical data
    By Accessgrasshopper in forum Access
    Replies: 0
    Last Post: 02-28-2011, 06:39 PM
  5. Replies: 1
    Last Post: 12-09-2010, 08:29 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