CREATE table based YEAR

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

CREATE table based YEAR

Post by sal21 »

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: CREATE table based YEAR

Post by HansV »

I 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
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: CREATE table based YEAR

Post by sal21 »

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

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: CREATE table based YEAR

Post by sal21 »

HansV wrote:
29 Jul 2021, 10:47
I 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
peraphs i'm wrong...
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.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: CREATE table based YEAR

Post by HansV »

That is correct.
Best wishes,
Hans