By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,869 Members | 1,756 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,869 IT Pros & Developers. It's quick & easy.

How to display individual and summary records in a query?

P: 1
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:
Expand|Select|Wrap|Line Numbers
  1.    Do Until rst.EOF
  2.       iFld = 0
  3.       lRecords = lRecords + 1
  4.       Me.lblMsg.Caption = "Exporting record #" & lRecords & " to " & sOutput
  5.       Me.Repaint
  6.  
  7.       For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
  8.          wks.Cells(iRow, iCol) = rst.Fields(iFld)
  9.  
  10.          If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
  11.             wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
  12.          End If
  13.  
  14.          wks.Cells(iRow, iCol).WrapText = False
  15.          iFld = iFld + 1
  16.       Next
  17.  
  18.       wks.Rows(iRow).EntireRow.AutoFit
  19.       iRow = iRow + 1
  20.       rst.MoveNext
  21.    Loop
  22.  
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
Nov 13 '08 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 374
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:
Expand|Select|Wrap|Line Numbers
  1.    Do Until rst.EOF
  2.       iFld = 0
  3.       lRecords = lRecords + 1
  4.       Me.lblMsg.Caption = "Exporting record #" & lRecords & " to " & sOutput
  5.       Me.Repaint
  6.  
  7.       For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
  8.          wks.Cells(iRow, iCol) = rst.Fields(iFld)
  9.  
  10.          If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
  11.             wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
  12.          End If
  13.  
  14.          wks.Cells(iRow, iCol).WrapText = False
  15.          iFld = iFld + 1
  16.       Next
  17.  
  18.       wks.Rows(iRow).EntireRow.AutoFit
  19.       iRow = iRow + 1
  20.       rst.MoveNext
  21.    Loop
  22.  
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
Nov 17 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.