Results 1 to 4 of 4

Thread: Archive Records

  1. #1
    Join Date
    Mar 2006

    Unanswered: Archive Records

    I have a form, that runs off a query, which in turn populates a table. The main table stores calculation from the query (Federal Tax, State Tax, etc.) The set up works fine, but I want to archive the records in the main table that are completed by a closing date.

    What I did was create an append query that transfers all files with a closing date to an archive table. I then created a delete query to delete the files in the main table. Works great, but when I look at the calculations like Federal Tax, State Tax, etc., all are 0's. In the main table, they were stored with numbers present.

    How do I retain the amounts in these fields when they are archived to another table.

    Hope this makes sense to anyone. I even had a hard time explaining it.



  2. #2
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    If the number is correctly stored in the main table but isn't copied to the archive table, something is probably wrong with the SQL of the append query. What is it?

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    I'm guessing that in pursuit of normalisation you used a calcualtion to generate the tax. when you copied to the archive table you lost the linkage between the rate and value.

    With thing like taxes I always prefer to flout the rule of normalistaion, especially if you are using invoices or archiving. beancounters always need to see the exact precise figure

    I'd check precisely how the tax value was derived in the original table, then make sure you fake the same process in the archive table. I wouldn't mess around with formulae in the archive table, make sure its exactly the same value as the orginal invoice (down to the last penny) that way round you don't play that wonderfull game accountants love of hunt for the 1p variance in the sum of multi thousand records.

    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Dec 2004
    Madison, WI

    Losing Numbers

    How are the fields in your archive table for the numbers set up. For example, if you set the tax fields up as Long Integer in the Archive table and in your main table they are defined as Double or Percent and you have values such as .33 in the main table, they will not append correctly.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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