Can I create a report a little like a cross tab query but without adding anything up? (1 Viewer)

sistemalan

Registered User.
Local time
Today, 09:54
Joined
Jun 19, 2009
Messages
77
Hi there,

Thanks in advance for taking the time to look

I want to present some data in what seems to me like it would be a very natural and normal format, however have so far been unable to figure out how to do it. :banghead:

I record attendance data for a children's after school club in a table as follows (simplified):

AttendanceID - autonumber
AttendeeName - text
AttendanceDate - date
Attended - True / False

The data looks like this in the table:



I would like to be able to easily see who attended on which days, so I would like to therefore see the data laid out like this:




The Crosstab query looks almost like it would do what I wanted, however it seems to insist on adding something up, rather than just showing me the value true or false.

I hope this makes sense and that someone can advise on a way forward.

Thanks,

Alan
 

plog

Banishment Pending
Local time
Today, 04:54
Joined
May 11, 2011
Messages
11,613
Yes, it will do what you want. A cross-tab is a special aggregate query, which means you must assign an aggregate way to handle each field you bring in (Group By, Sum, Count, Max, Min, etc.).

To achieve what you want, you would use either Max or Min underneath the Attended field and then make it your value field in the cross-tab.

The one caveat is, this only works if you have unique Attended values for every unique AttendeeName/AttendeeDate permutation.
 

sistemalan

Registered User.
Local time
Today, 09:54
Joined
Jun 19, 2009
Messages
77
Hi Plog,

Thanks for that. I've achieved what I'd hoped for.

Any way to now display that as a report rather than a query?

Alan
 

richsql

Registered User.
Local time
Today, 09:54
Joined
Feb 9, 2015
Messages
23
Have you tried using First([Attended]) as your value ?
 

sistemalan

Registered User.
Local time
Today, 09:54
Joined
Jun 19, 2009
Messages
77
Hi Rich. That's what worked for me. In fact First, Last, Min or Max works fine.

Wondering how (if it's possible) to display this as a report now.

Thanks,

Alan
 

plog

Banishment Pending
Local time
Today, 04:54
Joined
May 11, 2011
Messages
11,613
Not really. With a cross-tab you've created dynamic field names (12/1/2015, 5/3/2015, etc.) and a report requires static field names (AttendanceDate, AttendeeName, etc.)

Technically, you could hack together some VBA to route around that, but it seems onerous.
 

sistemalan

Registered User.
Local time
Today, 09:54
Joined
Jun 19, 2009
Messages
77
Fair enough. I'm content that I'm not missing anything, and happy that I can always copy and paste the crosstab into Excel and format it a bit more.

Thanks for your help
 

richsql

Registered User.
Local time
Today, 09:54
Joined
Feb 9, 2015
Messages
23
It is going to be difficult to create an access report, below is a basic template for using Excel as your report which could get you started ...

Code:
Dim rstData As Recordset
Dim strFilePath As String
 
Dim intCount As Integer 'Used as counter
Dim xl As New Excel.Application 'Opens an instance of Excel.

 strPath = "C:\Test\Test.xlsx"
xl.Workbooks.Add 'Creates a blank workbook.

 Set rstData = CurrentDb.OpenRecordset("YourQueryName")
 'Prepare the headers: these will grab your date header names

 For intCount = 1 To rstData.Fields.Count
    xl.ActiveSheet.Cells(1, intCount).Value = rstData.Fields(intCount - 1).Name
Next intCount

 'Export the data into the sheet.
xl.ActiveSheet.Cells(2, 1).CopyFromRecordset rstData

  'Save and quit.
xl.ActiveWorkbook.SaveAs strPath
xl.Quit
 

Users who are viewing this thread

Top Bottom