Results 1 to 13 of 13
  1. #1
    Delfina is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jan 2019
    Location
    Bartlesville OK
    Posts
    47

    Update Table with New Information without Updating the Old Information

    I have two tables. One is Projects and the other is Providers. The Provider table is used to enter information into the Project table with the use of a dropdown box with the names of the providers, services, etc. The information in the Provider table changes from time to time for each provider in that table. What would be the best way to keep the changes to the provider from changing the past entries in the project table and only change the ones going forward. I am thinking I am going to have to re-enter all the information for that provider and mark the old provider information as inactive. Any advise would be much appreciated.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you expand your description of the "business process(es)" involved?

    What exactly is a Project?
    What is a Provider? Where do Service(s) and Product(s) fit?
    What is your concern with new providers and old records?

  3. #3
    Delfina is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jan 2019
    Location
    Bartlesville OK
    Posts
    47
    The Projects table has Customer Names and address in it. The Provider table has the Provider names and internet speeds and pricing in it. Internet speeds and pricing do change ever so often.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Options:

    1. save Provider data that can potentially change into Projects - this will likely involve code (macro or VBA)

    2. create a new record and have another field in table that flags the old record as 'inactive' to exclude from query - might want to split Providers table

    Option 1 might be the easiest to manage.
    Last edited by June7; 01-31-2019 at 10:02 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Delfina is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jan 2019
    Location
    Bartlesville OK
    Posts
    47
    I will work on that. I would like to leave this thread open though for a little bit in case I have any problems.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Delfina,

    You mention Customers and Projects and Providers. You should make sure your design meets your requirements before getting too deep into physical database. It's your project, so you can proceed as you like, but your design is suspect in my view. Very few databases involve only 2 tables.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    So maybe:

    Providers table (ProviderID, ProviderName, address)

    ProviderProducts table (ProductID, ProviderID_FK, Description, Price, DateActive, DateInactive)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Delfina is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jan 2019
    Location
    Bartlesville OK
    Posts
    47
    I actually have a lot more tables. I am just mentioning the two because the information that goes into the Project table from the provider table is the most crucial.

  9. #9
    Delfina is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jan 2019
    Location
    Bartlesville OK
    Posts
    47
    I am going to work on this for a little while. I will upload a copy when I get finished. It may be tomorrow. I am going to try to query the providers to only show the active one when the dropdown box in the Project form is used.

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Delfina,

    This is a fairly common issue, mainly showing up in invoicing applications. Here is a link explaining the basics:https://answers.microsoft.com/en-us/...2f?db=5&auth=1 . It can get quite a bit more complicated:
    https://softwareengineering.stackexc...of-keeping-tra
    https://answers.microsoft.com/en-us/...3-1458aabd3809

    As June mentioned the easiest would be to save the "changeable" provider info into corresponding fields in your projects table.

    Cheers,
    Vlad

  11. #11
    Delfina is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jan 2019
    Location
    Bartlesville OK
    Posts
    47
    Problem has been solved. I added two Yes and No columns to the provider table. One inactive and the other active. Then I did two queries making one Active=Yes and Inactive=No. Then I went into the provider table and did a combo with a lookup from the active query. Now only the active shows up in the dropdown box, and it does not delete the inactive from the project table. You guys are really amazing. I couldn't have done it without you.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Don't understand why two yes/no fields.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Delfina is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jan 2019
    Location
    Bartlesville OK
    Posts
    47
    Because when I did it with just with one with the provider dropdown queried on active, the inactive records disappeared from the project table. With two, the inactive records stays there, but going forward I can only select active records from the provider list to populate the project table.

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

Similar Threads

  1. Replies: 10
    Last Post: 09-25-2018, 08:18 PM
  2. Replies: 7
    Last Post: 10-16-2016, 01:16 PM
  3. Replies: 2
    Last Post: 05-14-2016, 04:43 PM
  4. Replies: 3
    Last Post: 01-09-2014, 07:45 PM
  5. Replies: 4
    Last Post: 09-03-2009, 02:01 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