Looping unique values with dates.

[engelwood]

In the beginning I thought I fully understood my problem and started to write some VBA code to solve it. (At best I'm a novice, but I'm trying to use VBA when possible to facilitate my learning.) At this juncture I've realized that the problem is a little different than I first understood it.... so I hope I haven't gone down a rabbit hole with this one.

I have a table, CalData, that has two fields CAL and EFF. CAL are work centers and EFF is a series of event date lists. It looks like the following.

CAL EFF
340100 5/1/16
340100 5/15/16
060100 6/1/16
060200 6/1/16
... etc..


Ultimately I'm trying to create a separate table (tblCal) that has a record for each work center and date (for 60 days starting from earliest date). I wrote some VBA, but was only able to loop through the 60 days for one work center. I wanted to get each work center, but I got stumped on how to proceed. I do appreciate the help!


<code>

Public Sub Ibetss()

Dim rs As DAO.Recordset
Dim rst As DAO.Recordset
Dim dbv As DAO.Database
Set rs = CurrentDb.OpenRecordset("CalData")
Set dbv = CurrentDb
Set rst = dbv.OpenRecordset("tblcal")
Dim DD As Date
Dim D As Integer
Dim DE As Date


'Finds the minimum date
DD = DMin("Eff", "CalData")

'Loops the daily increment
For D = 1 To 60
DE = DateAdd("d", D, DD)

rst.AddNew
rst("Cal").Value = rs!Cal
rst("Eff").Value = DE
rst.Update


Next D

End Sub
</code>

 

[xenou]

Ultimately I'm trying to create a separate table (tblCal) that has a record for each work center and date (for 60 days starting from earliest date).

This sounds like the wrong approach. You shouldn't need all these separate tables. What are you trying to do with them?

 

[engelwood]

This sounds like the wrong approach. You shouldn't need all these separate tables. What are you trying to do with them?

Well I only have the two tables. One with the data (CalData) and the one I'm appending too (tblcal). Ultimately I'm trying to create a separate table (tblCal) that has a record for each work center and date (for 60 days starting from earliest date).


Currently CalData looks like:

CAL EFF
340100 5/1/16
340100 5/15/16
060100 6/1/16
060200 6/1/16
... etc..

I'm trying to make tblcal look like
CAL EFF
340100 5/1/16
340100 5/2/16
340100 5/3/16
... etc... (through 6/30 = 60 days)
060100 5/1/16
060100 5/2/16
etc... (through 6/30 = 60 days)

 

[Micron]

I won't ask why you need to create all of these date/value combinations, but it looks odd.
You don't have an outer loop to cycle through the work centers (rs). After your Dim statements, something like

If Not (rs.EOF And rs.BOF) Then
 rs.MoveFirst
 Do While Not rs.EOF
   'Finds the minimum date
   DD = DMin("Eff", "CalData")

   'Loops the daily increment
   For D = 1 To 60
     DE = DateAdd("d", D, DD)
     rst.AddNew
     rst("Cal").Value = rs!Cal
     rst("Eff").Value = DE
     rst.Update
   Next D
 rs.MoveNext
Loop
more stuff

Additional Stuff:
It doesn't make sense to create a database object (dbv) then not use it
Set rs = CurrentDb.OpenRecordset("CalData")

You could probably also write
DE = DateAdd("d", D, DMin("Eff", "CalData")) and not bother with an additional date variable. For that matter,
rst("Eff").Value = DE might also be rst("Eff").Value = DateAdd("d", D, DMin("Eff", "CalData"))

It's customary to use int for an integer variable, or intSomething (or lngSomething [long], etc). Makes it easier to follow - especially 100 or so lines down.

Before you end your sub, close recordsets then destroy all the objects you have set.
Obviously, you have Option Explicit at the top of every module?
How I might write it:

Public Sub Ibetss()

Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset
Dim db As DAO.Database
Dim int as Integer

Set db = CurrentDb
Set rs1 = db.OpenRecordset("CalData")
Set rs2 = db.OpenRecordset("tblcal")

If Not (rs1.EOF And rs1.BOF) Then
  rs1.MoveFirst
  Do While Not rs1.EOF
    'Loops the daily increment
     For int = 1 To 60
        rs2.AddNew
        rs2("Cal").Value = rs1!Cal
        rs2("Eff").Value =  DateAdd("d", D, DMin("Eff", "CalData"))
        rs2.Update
     Next
     rs1.MoveNext
  Loop
End If
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing

End Sub

This is air code (from the air space between my ears) and has probably overlooked something - especially when changing your variables. It also has no error handling.

 

[xenou]

Okay, I was a little confused by your description but I think you only want 1 table with the work centers and dates. When you said you wanted a separate table for each work center I thought you meant you wanted 60 tables!!!

You can do this without vba or loops. Use a cross join.

 

[xenou]

Example:

Tables called tblWorkCenters and tblDates

--------------
| WorkCenter |
--------------
|     340100 |
|     060200 |
|     254700 |
--------------



-------------
|  TranDate |
-------------
| 01-May-16 |
| 02-May-16 |
| 03-May-16 |
| 04-May-16 |
| 05-May-16 |
-------------

Query

SELECT 
    WorkCenter, 
    TranDate
FROM 
    tblDates, tblWorkCenters
ORDER BY 
    WorkCenter, TranDate;

Result:

--------------------------
| WorkCenter |  TranDate |
--------------------------
|     060200 | 01-May-16 |
|     060200 | 02-May-16 |
|     060200 | 03-May-16 |
|     060200 | 04-May-16 |
|     060200 | 05-May-16 |
|     254700 | 01-May-16 |
|     254700 | 02-May-16 |
|     254700 | 03-May-16 |
|     254700 | 04-May-16 |
|     254700 | 05-May-16 |
|     340100 | 01-May-16 |
|     340100 | 02-May-16 |
|     340100 | 03-May-16 |
|     340100 | 04-May-16 |
|     340100 | 05-May-16 |
--------------------------