How to display individual and summary records in a query?

[randyse]

Hello all,
This is my first post, please let me know if I've missed any relevant data or guidelines.

I'm using Access 2003 for the database and Excel 2003 for the reporting to avoid having everyone reading the reports install the snapshot viewer. I'm also porting this application to SQL Server/ASP.NET eventually, and there is not a way to install reporting services on the server, so my hope is to use Excel there as well.

I'm using two queries, one to get the relevant data and another using Group By to count and summarize the data. It's a student database and I'm getting the name, day attended, and hours attended for each student. What I want to wind up with I could do with Access reports, but I can't figure out how to do it in VBA with the queries.

The end result I'm looking for would be something like:
Student 1 10/1/2008 5 hrs
Student 1 10/2/2009 2 hrs
Summary Student 1 2 days 7 hrs

Student 2 10/1/2008 2 hrs
Student 2 10/2/2008 3 hrs
Summary Student 2 2 days 5 hrs.

Here are the queries:
Detail Query: [SQL]
SELECT [tblPersonData].[FirstName] & " " & [tblPersonData].[LastName] AS Name, DateValue([LogInTime]) AS DayAttended, DateDiff("n",[LogInTime],[LogOutTime])/60 AS HoursAttended
FROM tblPersonData INNER JOIN tblPersonTimeLog ON tblPersonData.PersonID = tblPersonTimeLog.PersonId
WHERE (((tblPersonData.Site)=[forms]![fdlgAttendanceReport]![cboSite]) AND ((tblPersonData.Role)="Student") AND ((tblPersonTimeLog.LogInTime) Between ([forms]![fdlgAttendanceReport]![txtStart]) And (DateValue([forms]![fdlgAttendanceReport]![txtEnd]) & " 11:59:59 PM")))
ORDER BY [tblPersonData].[FirstName] & " " & [tblPersonData].[LastName], DateValue([LogInTime]);
[/SQL]

Summary Query: [SQL]
SELECT [qselAttendanceReport-detail].Name, Count([qselAttendanceReport-detail].DayAttended) AS CountOfDayAttended, Sum([qselAttendanceReport-detail].HoursAttended) AS SumOfHoursAttended
FROM [qselAttendanceReport-detail]
GROUP BY [qselAttendanceReport-detail].Name
ORDER BY [qselAttendanceReport-detail].Name;
[/SQL]

After creating all the objects and a recordset from a query, I use the following code to kick the data out to the Excel worksheet (wks) I created like so:

   Do Until rst.EOF
      iFld = 0
      lRecords = lRecords + 1
      Me.lblMsg.Caption = "Exporting record #" & lRecords & " to " & sOutput
      Me.Repaint
 
      For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
         wks.Cells(iRow, iCol) = rst.Fields(iFld)
 
         If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
            wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
         End If
 
         wks.Cells(iRow, iCol).WrapText = False
         iFld = iFld + 1
      Next
 
      wks.Rows(iRow).EntireRow.AutoFit
      iRow = iRow + 1
      rst.MoveNext
   Loop
 

I'm guessing I need to write out the detail records, and somehow when the student name changes, write out the summary, maybe with domain aggregate functions, but this is as far as I've been able to get. Right now I'm creating two worksheets, one with detail data, the other with summary data. Pretty inelegant.

Thanks if

[PianoMan64]

Hello all,
This is my first post, please let me know if I've missed any relevant data or guidelines.

I'm using Access 2003 for the database and Excel 2003 for the reporting to avoid having everyone reading the reports install the snapshot viewer. I'm also porting this application to SQL Server/ASP.NET eventually, and there is not a way to install reporting services on the server, so my hope is to use Excel there as well.

I'm using two queries, one to get the relevant data and another using Group By to count and summarize the data. It's a student database and I'm getting the name, day attended, and hours attended for each student. What I want to wind up with I could do with Access reports, but I can't figure out how to do it in VBA with the queries.

The end result I'm looking for would be something like:
Student 1 10/1/2008 5 hrs
Student 1 10/2/2009 2 hrs
Summary Student 1 2 days 7 hrs

Student 2 10/1/2008 2 hrs
Student 2 10/2/2008 3 hrs
Summary Student 2 2 days 5 hrs.

Here are the queries:
Detail Query: [SQL]
SELECT [tblPersonData].[FirstName] & " " & [tblPersonData].[LastName] AS Name, DateValue([LogInTime]) AS DayAttended, DateDiff("n",[LogInTime],[LogOutTime])/60 AS HoursAttended
FROM tblPersonData INNER JOIN tblPersonTimeLog ON tblPersonData.PersonID = tblPersonTimeLog.PersonId
WHERE (((tblPersonData.Site)=[forms]![fdlgAttendanceReport]![cboSite]) AND ((tblPersonData.Role)="Student") AND ((tblPersonTimeLog.LogInTime) Between ([forms]![fdlgAttendanceReport]![txtStart]) And (DateValue([forms]![fdlgAttendanceReport]![txtEnd]) & " 11:59:59 PM")))
ORDER BY [tblPersonData].[FirstName] & " " & [tblPersonData].[LastName], DateValue([LogInTime]);
[/SQL]

Summary Query: [SQL]
SELECT [qselAttendanceReport-detail].Name, Count([qselAttendanceReport-detail].DayAttended) AS CountOfDayAttended, Sum([qselAttendanceReport-detail].HoursAttended) AS SumOfHoursAttended
FROM [qselAttendanceReport-detail]
GROUP BY [qselAttendanceReport-detail].Name
ORDER BY [qselAttendanceReport-detail].Name;
[/SQL]

After creating all the objects and a recordset from a query, I use the following code to kick the data out to the Excel worksheet (wks) I created like so:

   Do Until rst.EOF
      iFld = 0
      lRecords = lRecords + 1
      Me.lblMsg.Caption = "Exporting record #" & lRecords & " to " & sOutput
      Me.Repaint
 
      For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
         wks.Cells(iRow, iCol) = rst.Fields(iFld)
 
         If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
            wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
         End If
 
         wks.Cells(iRow, iCol).WrapText = False
         iFld = iFld + 1
      Next
 
      wks.Rows(iRow).EntireRow.AutoFit
      iRow = iRow + 1
      rst.MoveNext
   Loop
 

I'm guessing I need to write out the detail records, and somehow when the student name changes, write out the summary, maybe with domain aggregate functions, but this is as far as I've been able to get. Right now I'm creating two worksheets, one with detail data, the other with summary data. Pretty inelegant.

Thanks if

Hello Randy,

I guess my question in all of that is, what is it that you want to know how to do?

It seems that you have a good system going, but it is a little over-kill on the exporting to Excel when you have the ability within access to create reports that would provide all the information that you need.

If it is simply about security of student information, there are more than plenty of options that you could make use of the Workgroup file within MS Access that would allow you to create user names and passwords for each student, and give then a custom form that they would allow them to look at there information only and not any one else.

If you need examples on how to do that, there is an Microsoft Artical on that very subject.

http://support.microsoft.com/kb/305542
http://support.microsoft.com/kb/289885/
http://support.microsoft.com/support/access/content/secfaq.asp?SD=gn&LN=en-us&gssnb=1