Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Modelling (2000)

    Hello my database has a staff register table that is linked to the Human Resources database, any updates to the HR then auto feed to mine and I have to do little maintenance on that table. It's not normal linked tables in the usual way however, a macro runs to build a copy table from the HR into mine, then deletes my table and rebuilds in mine stripping with SQL in the macro from the HR copy to my new built table, then deletes the HR copy. I now have a problem in that I need to add a new field into my staff table to order tracking of certain actions for new staff members, such a field does not exist in the HR database. If I modify my table, next time I run the macro I will lose extra field in my table as a result. It would be too time consuming to change things as they stand in terms of how my tables are updated from the HR d/b. Can anybody propose a solution.

    Many thanks Darren.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Modelling (2000)

    I don't understand what you want if you can't change anything. And since you haven't given us any details of how the macro works etc., I can't come up with suggestions.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Modelling (2000)

    Why is your table being deleted instead of simple cleared and repopulated? That would allow the field to remain, although it wouldn't be very useful, since its values would be cleared. You might be better off creating a separate table for the information you want in that additional field and giving it a foreign key to the table you get from HR.
    Charlotte

  4. #4
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modelling (2000)

    The macro runs SQL and imports a staff table from the HR database, SQL then deletes entries from my staff table then insertd from HR tabke to my table. Hope this gives more background, can you offer more guidance?

    Thanks
    Darren

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Modelling (2000)

    Do the entries deleted from your table (partially) concern the same people that are being added from the imported table? If so, an update query for matching records plus an append query for new records would be better than deleting and adding all records. This would preserve the values in the additional field for existing records.

    Charlotte's suggestion of creating a separate table should also work. Say that your staff table has a primary key EmployeeID. The separate table would have two fields: EmployeeID plus the additional field you need.. The EmployeeID field allows you to link the records in the two tables.

  6. #6
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modelling (2000)

    Thanks Hans, thats fine

  7. #7
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modelling (2000)

    Hello again, I have visited this now, whilst I undersatnd your direction,I I am having diffculty putting it into action. My append query adds all exisiting and new records into the new table or it would if it wasn't for the duplication of PKs. What do I need to put in the QBE so that only new records from lets say from table A with fields AA ,BB, CC are transfered to table B with corresponding AA BB CC? Assume AA is the PK?

    Thanks Darren.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Modelling (2000)

    For the append query, set the criteria for the AA field to

    Not In (SELECT [AA] FROM [NameOfTable])

    where NameOfTable is the name of your staff table.

  9. #9
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modelling (2000)

    Saviour Hans, thank you muchly

    Darren.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •