have a var YEAR=2021
have a table named STANZE in field TIPO have:
ID TIPO
1 SINGOLA
2 DOPPIA
3 TRIPLA
4 SUITE
5 SUPER SUITE
now i need SQL, to append into table PREZZI (have this fileds: TIPO, DAL and AL) , a squence of values for each TIPO, similar:
ID TIPO DAL AL
1 SINGOLA 01/01/2021 31/01/2021
1 SINGOLA 01/02/2021 28/01/2021
...
1 SINGOLA 01/12/2021 31/12/2021
2 DOPPIA 01/01/2021 31/01/2021
2 DOPPIA 01/02/2021 28/01/2021
...
2 DOPPIA 01/12/2021 31/12/2021
...
5 SUPER SUITE 01/01/2021 31/01/2021
5 SUPER SUITE 01/02/2021 28/01/2021
...
5 SUPER SUITE 01/12/2021 31/12/2021
I'm on vb6 and access database
in effect for each TIPO create a step of init and the end of each month
CREATE table based YEAR
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: CREATE table based YEAR
I assume that you meant 28/02/2021 instead of 28/01/2021.
Try this:
Try this:
Code: Select all
Sub AddRecords()
Dim CNN As ADODB.Connection
Dim RS1 As ADODB.Recordset
Dim RS2 As ADODB.Recordset
Dim ID As Long
Dim TIPO As String
Dim M As Long
Dim DAL As Date
Dim AL As Date
Set CNN = New ADODB.Connection
CNN.Open ConnectionString:="..."
Set RS1 = New ADODB.Recordset
RS1.Open Source:="STANZE", ActiveConnection:=CNN, CursorType:=adOpenKeyset, Options:=adCmdTableDirect
Set RS2 = New ADODB.Recordset
RS2.Open Source:="PREZZI", ActiveConnection:=CNN, CursorType:=adOpenKeyset, Options:=adCmdTableDirect
Do While Not RS1.EOF
ID = RS1!ID
TIPO = RS1!TIPO
For M = 1 To 12
DAL = DateSerial(Year(Date), M, 1)
AL = DateSerial(Year(Date), M + 1, 0)
RS2.AddNew
RS2!ID = ID
RS2!TIPO = TIPO
RS2!DAL = DAL
RS2!AL = AL
RS2.Update
Next M
RS1.MoveNext
Loop
RS2.Close
RS1.Close
CNN.Close
End Sub
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4334
- Joined: 26 Apr 2010, 17:36
Re: CREATE table based YEAR
HansV wrote: ↑29 Jul 2021, 10:47
I assume that you meant 28/02/2021 instead of 28/01/2021. YES!, sorry
Try this:
Code: Select all
Sub AddRecords() Dim CNN As ADODB.Connection Dim RS1 As ADODB.Recordset Dim RS2 As ADODB.Recordset Dim ID As Long Dim TIPO As String Dim M As Long Dim DAL As Date Dim AL As Date Set CNN = New ADODB.Connection CNN.Open ConnectionString:="..." Set RS1 = New ADODB.Recordset RS1.Open Source:="STANZE", ActiveConnection:=CNN, CursorType:=adOpenKeyset, Options:=adCmdTableDirect Set RS2 = New ADODB.Recordset RS2.Open Source:="PREZZI", ActiveConnection:=CNN, CursorType:=adOpenKeyset, Options:=adCmdTableDirect Do While Not RS1.EOF ID = RS1!ID TIPO = RS1!TIPO For M = 1 To 12 DAL = DateSerial(Year(Date), M, 1) AL = DateSerial(Year(Date), M + 1, 0) RS2.AddNew RS2!ID = ID RS2!TIPO = TIPO RS2!DAL = DAL RS2!AL = AL RS2.Update Next M RS1.MoveNext Loop RS2.Close RS1.Close CNN.Close End Sub
-
- PlatinumLounger
- Posts: 4334
- Joined: 26 Apr 2010, 17:36
Re: CREATE table based YEAR
peraphs i'm wrong...HansV wrote: ↑29 Jul 2021, 10:47I assume that you meant 28/02/2021 instead of 28/01/2021.
Try this:
Code: Select all
Sub AddRecords() Dim CNN As ADODB.Connection Dim RS1 As ADODB.Recordset Dim RS2 As ADODB.Recordset Dim ID As Long Dim TIPO As String Dim M As Long Dim DAL As Date Dim AL As Date Set CNN = New ADODB.Connection CNN.Open ConnectionString:="..." Set RS1 = New ADODB.Recordset RS1.Open Source:="STANZE", ActiveConnection:=CNN, CursorType:=adOpenKeyset, Options:=adCmdTableDirect Set RS2 = New ADODB.Recordset RS2.Open Source:="PREZZI", ActiveConnection:=CNN, CursorType:=adOpenKeyset, Options:=adCmdTableDirect Do While Not RS1.EOF ID = RS1!ID TIPO = RS1!TIPO For M = 1 To 12 DAL = DateSerial(Year(Date), M, 1) AL = DateSerial(Year(Date), M + 1, 0) RS2.AddNew RS2!ID = ID RS2!TIPO = TIPO RS2!DAL = DAL RS2!AL = AL RS2.Update Next M RS1.MoveNext Loop RS2.Close RS1.Close CNN.Close End Sub
but i have see in googling,... a very old time ago...
.Update
when add a new recordset not is require with ADO, instead with DAO yes...
Correct?
Last edited by sal21 on 29 Jul 2021, 12:02, edited 1 time in total.
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands