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

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

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.

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

Have you tried using First([Attended]) as your value ?

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

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.

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

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 ...