Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jan 2008
    Location
    Naperville, Illinois, United States
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am looking to create an annual record for a list of assets, beginning on when the asset is purchased and going forward. For example, if I bought a car in 2008, I would need a record for 2008, 2009, etc, up to a fixed year for all records, ie 2015. The start date will be different for each item in the list, but the end date will be the same for all records. Any suggestion?

    Basic Table Structure is:

    Year Acquired
    Asset Description
    Cost

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    A belated welcome to the Lounge!

    Will the first record for each asset be in that table, or do you need to read the list of assets from another table?

  3. #3
    New Lounger
    Join Date
    Jan 2008
    Location
    Naperville, Illinois, United States
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, I joined a while back and have searched the forum to solve many problems. However, this is one I haven't been able to resolve yet.

    Not being a database "Expert", I'll use an example..

    The base table lists ID, date aquired, description and cost:

    B0342, 2008, Honda, 23000

    I need to create a query that adds an annual record:

    ID, Date Aquired, Year, Cost, Annual Expenses
    B0342, 2008, 2008, Honda, 23000, 1400
    B0342, 2008, 2009, Honda, 23000, 1450
    B0342, 2008, 2010, Honda, 23000, 1500
    B0342, 2008, 2011, Honda, 23000, 1300
    B0342, 2008, 2012, Honda, 23000, 1350

    The Annual Expenses for past years will be pulled from another table that has ID, Year, Annual Expenses. The costs for future years will be calculated based on a % of the cost (I can do that part with iif statements).

    Does that help explain the question?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You should be able to use the following code as a starting point. You'll have to modify it for your situation. The code requires a reference to the Microsoft DAO 3.6 Object Library.

    [codebox]Sub AddRecords()
    Dim dbs As DAO.Database
    Dim rstIn As DAO.Recordset
    Dim rstOut As DAO.Recordset
    Dim lngYear As Long

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    Set rstIn = dbs.OpenRecordset("tblBase", dbOpenDynaset)
    Set rstOut = dbs.OpenRecordset("tblData", dbOpenDynaset)

    Do While Not rstIn.EOF
    For lngYear = rstIn![Year Acquired] To 2015
    rstOut.AddNew
    rstOut!ID = rstIn!ID
    rstOut![Year Acquired] = rstIn![Year Acquired]
    rstOut!Year = lngYear
    rstOut![Asset Description] = rstIn![Asset Description]
    rstOut!Cost = rstIn!Cost
    rstOut.Update
    Next lngYear
    rstIn.MoveNext
    Loop

    ExitHandler:
    On Error Resume Next
    rstOut.Close
    Set rstOut = Nothing
    rstIn.Close
    Set rstIn = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    [/codebox]
    I have attached a sample database with two tables and a code module containing the above code. If you run the procedure AddRecords, the table tblData will be populated using the records from tblBase.
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Jan 2008
    Location
    Naperville, Illinois, United States
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans, Thank you. I have not used modules before (I am a dangerous novice). A couple of questions about this: It is saving the data to a table. Is there a way to do this and have the results in a query? The reason being that the base table is updated monthly (completely replaced from an external source). Also, can the input into the module be a query? The base data has dates YYYYMMDD, but I create a year field based upon different criteria (it may or may not be the same year). I have a query that provides the base year, but not a table.

    While I don't understand everything in the module, I can see enough as to how it works and how I can use it. I appreciate your help!

    One last question, I am using Access 2000 - Will this work with older versions of access?

    David

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The input for the code can be a query as well as a table: in the line

    Set rstIn = dbs.OpenRecordset("tblBase", dbOpenDynaset)

    you can replace the name tblBase with that of a query that returns the records to be processed. The output must be a table, though - a query is merely a way to display data from one or more tables, it doesn't store data itself.

    The code will work in earlier versions of Access too.

Posting Permissions

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