Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2013

    Unanswered: creating duplicate rows based on a cell value + changing date values

    Hi all,

    I am looking for an Access solution for a macro that I use in excel.

    I have a db with price info for a specific period, namely Date from; Date until; price.

    In excel I transform this db to a db with a record for every day the price is valid.
    So that I have the specific price for every day the price is valid.

    My intuition tells me that doing this in access could save me a lot of time.

    I have found the code that could create this db, but I still have one issue. How can I adjust the date from being the beginning date to having each datevalue between the Date from and Date until.

    VBA access code I would use :
    '-----code begin---------
    Sub DuplicateRows()
    Dim db As DAO.Database
    Dim rsSource As DAO.Recordset
    Dim rsTarget As DAO.Recordset
    Dim i As Integer

    Set db = CurrentDb
    Set rsSource = db.OpenRecordset("table1")
    Set rsTarget = db.OpenRecordset("table2")

    Do While Not rsSource.EOF
    For i = 1 To rsSource![Field2]
    rsTarget!Field1 = rsSource!Field1
    Next i

    End Sub
    '------code end--------

    Excel vba code:

    Dim WS As Worksheet
    Sheets.Add.Name = "DB"

    Dim LR As Long, i As Long, e As Long, LR2 As Long

    With Sheets("GFU Meeru Vilamendhoo")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LR
    .Rows(i).Copy Destination:=Sheets("DB").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(.Range("B" & i).Value)
    Next i

    End With


    With Sheets("DB")
    LR2 = .Range("A" & Rows.Count).End(xlUp).Row
    For e = 2 To LR

    One = ActiveCell.Value - 1
    Two = ActiveCell.Value
    If Two > 1 Then
    ActiveCell.Offset(0, 5).Select
    Selection.AutoFill Destination:=Selection.Resize(Selection.Rows.Count + One), Type:=xlFillSeries
    ActiveCell.Offset(Two, -5).Select
    ActiveCell.Offset(1, 0).Select
    End If

    Next e

    End With

    It would be great if somebody could help me out!

    Thanks in advance.


  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    sorry thats the wrong approach in the db world...

    you have price, and if you want to look at price history, presuambly you'd also store a date that price came into force.

    you can retrieve whatever price was in force for whatever item at whatever point in time by structuring your query.

    select top 1 Price, DateOfPrice from mytable
    where ProductID = whatever AND DateOfPrice <= aspecifieddate
    order by DateofPrcice DESC

    changing from a spreadsheet approach to a db approach can take a bit of gettign used to.
    there is no longer the concept of a specific cell
    specific formulas and so on
    you have the data, you have queries which can manipualte data, you have consumers of data (be they screens,m forms, reports)....
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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