SQL for loop between dates

[toothpaste]

I have the following 2 tables:

• table Guide which includes the guide's name and work day.
• table Activity which has a date and the guide id who created the activity

Given a guide id and 1 date range, I would like to automatically create activities on the work days in which the guide can work.


for example: Joe works on Mondays. Chloe works on Thursdays

given Joe, from date January 1st 2017 and February 1st 2017 -> 5 Activities will be created
given Chloe, from date January 1st 2017 and February 1st 2017 -> 4 Activities will be created

[andy49]

I'm not sure I could do it in SQL. easy enough to create a table with the dates in vba and then query them against the days and the people that you given


Sent from my iPhone using Tapatalk

[toothpaste]

Can you please elaborate?

[andy49]

Code similar to this would fill in Table datatable with the date you want

You'd need to create a table called Datetable with two field

datefield (date/time)
dayfield (string)

Sub Main()
    Dim myDatestart As Date
    Dim myDateString As String
    Dim mydateend As Date
    Dim strSQL As String
    myDatestart = #1/1/2017#
    mydateend = #2/1/2017#
    
    
    Do While myDatestart <= mydateend
        myDateString = Format(myDate, "yyyy-mm-dd")
              strSQL = "INSERT INTO DateTable (DateField,dayfield) VALUES(#" & myDateString & "#, " _
 & "Format(#" & myDateString & "#," & Chr(34) & "dddd" & Chr(34) & "))"

        CurrentDb.Execute strSQL
        myDatestart = myDatestart + 1
    Loop
End Sub

You could then query this table with the tables which you have (if I understand it right)

Either a crosstab to get the total days for each worker or a select query to get all dates with tasks

[toothpaste]

Thank you, I used your code to do something very similar:

    Do While myDateStart <= mydateEnd    
        myDateString = Format(myDateStart, "mm-dd-yyyy")
        currentDay = Weekday(myDateString, 1)
        
        'Is today his usual day?
        If currentDay = usualDay Then
            'Yes, add a new activity
            strSQL = "INSERT INTO activities (guide, garden, activityDate)" _
            & "VALUES (" & cboGuide & "," & cboGarden & ", #" & myDateStart & "#)"
            CurrentDb.Execute strSQL
        End If


        myDateStart = myDateStart + 1
    Loop

[andy49]

Excellent job


Sent from my iPhone using Tapatalk