Problem with Looping thru Recordsets 1

[dnayana]

I'm trying to create a report based on two recordsets. I would like to display all the records in both recordsets, however, the report is only displaying the last record from each recordset. All records from recordset #1 MUST display in the report and the records from recordset #2 will display based on the Project/Task code in recordset #1. How can I display all the records from both recordsets and ensure each item in recordset #2 is matched with the appropriate Project/Task in recordset #1? Attached is the code I have that isn't working ....

P.S. I already viewed some of the posts and have tried some suggestions, but I'm still getting the same results. BTW, I'm using Access 2000 (not sure if this makes a difference with anything.)

Thanks in advance.

Nicole :-{}

Private Sub GetReportTotals()
On Error GoTo Err_0973Report
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strSQL As String
Dim strSQL2 As String

'Estimate
strSQL = "TRANSFORM Sum(Round([curEstimate])) AS Estimate"
strSQL = strSQL & " SELECT Format([tblProject.intProjectCode],'0000000') & '-' & Format([tblProject.intTaskCode],'000') AS [Project/Task]"
strSQL = strSQL & " FROM tblProject INNER JOIN (tblFYMonth INNER JOIN tblAcct0973 ON tblFYMonth.intFYMonth = tblAcct0973.intFYMonth) ON (tblProject.intTaskCode = tblAcct0973.intTaskCode) AND (tblProject.intProjectCode = tblAcct0973.intProjectCode)"
strSQL = strSQL & " GROUP BY Format([tblProject.intProjectCode],'0000000') & '-' & Format([tblProject.intTaskCode],'000'), tblAcct0973.intProjectCode, tblAcct0973.intTaskCode"
strSQL = strSQL & " PIVOT tblFYMonth.txtMonthName In ('OCT', 'NOV', 'DEC', 'JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP');"

'Used
strSQL2 = "TRANSFORM Sum(Round([curAmount])) AS SumTotal"
strSQL2 = strSQL2 & " SELECT Format([tblProject.intProjectCode],'0000000') & '-' & Format([tblProject.intTaskCode],'000') AS [Project/Task], tblExpense.intObjectGroup, Sum(Round([curAmount])) AS TotalOfcurAmount"
strSQL2 = strSQL2 & " FROM tblProject INNER JOIN (tblObjectGroup INNER JOIN (tblFYMonth INNER JOIN tblExpense ON tblFYMonth.intFYMonth = tblExpense.intFYMonth) ON tblObjectGroup.intObjectGroup = tblExpense.intObjectGroup) ON (tblProject.intTaskCode = tblExpense.intTaskCode) AND (tblProject.intProjectCode = tblExpense.intProjectCode)"
strSQL2 = strSQL2 & " WHERE (((tblExpense.intObjectGroup) = 52060300) And ((Left$([intBranchCode], 2)) = 60))"
strSQL2 = strSQL2 & " GROUP BY Format([tblProject.intProjectCode],'0000000') & '-' & Format([tblProject.intTaskCode],'000'), tblExpense.intObjectGroup, tblExpense.intProjectCode, tblExpense.intTaskCode"
strSQL2 = strSQL2 & " PIVOT tblFYMonth.txtMonthName In ('OCT', 'NOV', 'DEC', 'JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP');"

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

Set rst2 = CurrentDb.OpenRecordset(strSQL2, dbOpenSnapshot)

'Estimate
With rst
    .MoveFirst
    While Not .EOF
        Me.ProjectTaskEstimate = rst("Project/Task")
        Me.OctEstimate = Nz(rst("Oct"), 0)
        Me.NovEstimate = Nz(rst("Nov"), 0)
        Me.DecEstimate = Nz(rst("Dec"), 0)
        Me.JanEstimate = Nz(rst("Jan"), 0)
        Me.FebEstimate = Nz(rst("Feb"), 0)
        Me.MarEstimate = Nz(rst("Mar"), 0)
        Me.AprEstimate = Nz(rst("Apr"), 0)
        Me.MayEstimate = Nz(rst("May"), 0)
        Me.JunEstimate = Nz(rst("Jun"), 0)
        Me.JulEstimate = Nz(rst("Jul"), 0)
        Me.AugEstimate = Nz(rst("Aug"), 0)
        Me.SepEstimate = Nz(rst("Sep"), 0)
        .MoveNext
    Wend
End With

'Used
With rst2
    .MoveFirst
    While Not .EOF
        Me.ProjectTaskUsed = rst2("Project/Task")
        Me.OctUsed = Nz(rst2("Oct"), 0)
        Me.NovUsed = Nz(rst2("Nov"), 0)
        Me.DecUsed = Nz(rst2("Dec"), 0)
        Me.JanUsed = Nz(rst2("Jan"), 0)
        Me.FebUsed = Nz(rst2("Feb"), 0)
        Me.MarUsed = Nz(rst2("Mar"), 0)
        Me.AprUsed = Nz(rst2("Apr"), 0)
        Me.MayUsed = Nz(rst2("May"), 0)
        Me.JunUsed = Nz(rst2("Jun"), 0)
        Me.JulUsed = Nz(rst2("Jul"), 0)
        Me.AugUsed = Nz(rst2("Aug"), 0)
        Me.SepUsed = Nz(rst2("Sep"), 0)
        .MoveNext
    Wend
End With
   
Exit_0973Report:
    rst2.Close
    rst.Close
    Exit Sub
Err_0973Report:
    MsgBox Err.Description
    Resume Exit_0973Report
End Sub

 

[PaulBricker]

I tried to post something here a few minutes ago but it didn't happen. Let's try again.

1. Create a new query
2. Add both Crosstab Queries to the query.
3. Join the Task fields.
4. Double click on the join line. Select the option that selects all the records from one crosstab and only the matching records from the other crosstab that fills your requirements
5. Add all the fields from each crosstab that you want. Only add the task field from the query that returns all the tasks
6. Base your report on this query.

See if it helps.

Paul

 

[dnayana]

Paul,

Again, thank you soooooo much!!

I'm getting back into programming with Access and my skills are realllllly rusty. :-(

Are there any books or other websites you can recommend to assist me with VBA (w/Access 2000)?

Nicole :-{}

 

[PaulBricker]

Nicole, go to the top of this page, look for the link to Advanced Search. Type in Books, select the one that specifies your forums, not all forums, and click go. There are a lot of recommendation there. Anything by Litwin and Getz would be good, and there are a lot of other recommedations in the archives here.

Paul