02-20-13, 06:07 #1Registered User
- Join Date
- Feb 2013
Unanswered: creating duplicate rows based on a cell value + changing date values
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 :
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
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)
LR2 = .Range("A" & Rows.Count).End(xlUp).Row
For e = 2 To LR
One = ActiveCell.Value - 1
Two = ActiveCell.Value
If Two > 1 Then
Selection.AutoFill Destination:=Selection.Resize(Selection.Rows.Count + One), Type:=xlFillSeries
It would be great if somebody could help me out!
Thanks in advance.
02-20-13, 06:25 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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