CODEWITHSUNDEEP
sqlms-accessvba
NumberCruncher
NumberCruncher

Reputation: 29

How to automatically generate records into a table

I'll try my best to keep this concise

My tables and relationships look like this.

[Relationships][1]https://i.sstatic.net/3QFmV.jpg

What I'm trying to achieve is this. A new testID is added each month. Every student does every exercise for each testID, this is recorded in the results table. When a new test is added I want to automatically add new records in the results table so that there is a new record for EACH exercise for EACH student.

Suppose I have 10 students and 8 exercises and I add a new TestID 2016-12. I want 80 new records to be created in the Results table with testID 2016-12, one for each Exercise-Student combination.

I thought about trying to use an append query to do this but didn't get far.

I know how to insert one record using VBA so have been thinking I need to set up a loop, but can't figure out how to loop through studentID's and ExerciseID's.

Is there any way to create a query that produces every student-exercise combination and then use that to append to the table with a specific testID.

Sorry if this is a bit all over the place. THank you for your

Upvotes: 1

Views: 223

Answers (2)

Skippy
Skippy

Reputation: 1590

You can do this as an Append query. It will look something like this:

Cross join append query

When you run this query it will ask you for the TestID and then add a row for every Student/Exercise combination against that TestID.

Upvotes: 1

Gustav
Gustav

Reputation: 55841

Open two recordsets with the students and the exercises and loop these while adding records to a third or fourth recordset.

You can use this example as a starting point - at least you will get the idea:

Public Sub AddRecords()

    Dim dbs         As DAO.Database
    Dim rss         As DAO.Recordset
    Dim rst         As DAO.Recordset
    Dim fld         As DAO.Field

    Dim intCount    As Integer
    Dim lngRevenue  As Long
    Dim datDate     As Date
    Dim booNext     As Boolean

    Set dbs = CurrentDb
    Set rss = dbs.OpenRecordset("Select * From tblSales Order By [Purchase Week]")
    Set rst = dbs.OpenRecordset("Select * From tblSalesWeek")

    If rss.RecordCount > 0 Then
        datDate = rss.Fields("Purchase Week").Value
    End If
    While rss.EOF = False
        rst.AddNew
            If DateDiff("d", datDate, rss.Fields("Purchase Week").Value) = 0 Then
                intCount = intCount + rss.Fields("Customer Count").Value
                lngRevenue = lngRevenue + rss.Fields("Revenue").Value
                booNext = True
            Else
                booNext = False
            End If
            For Each fld In rss.Fields
                Select Case fld.Name
                    Case "Purchase Week"
                        rst.Fields(fld.Name).Value = datDate
                    Case "Customer Count"
                        rst.Fields(fld.Name).Value = intCount
                    Case "Revenue"
                        rst.Fields(fld.Name).Value = lngRevenue
                    Case Else
                        rst.Fields(fld.Name).Value = rss.Fields(fld.Name).Value
                End Select
            Next
        rst.Update
        If booNext = True Then
            rss.MoveNext
        End If
        datDate = DateAdd("d", 7, datDate)
    Wend
    rst.Close
    rss.Close

    Set fld = Nothing
    Set rst = Nothing
    Set rss = Nothing
    Set dbs = Nothing

End Sub

Upvotes: 1

Related Questions