Forum Discussion

CathyWilbur's avatar
CathyWilbur
Qrew Member
5 years ago

Crosstab report with data data

I have two tables (students, courses)  which are related in an associative table (student courses).  I have a unique compound key on the associative table, so I know there is only one occurrence for each student/course combination.


I need to create a 'crosstab' like table with the courses across the top and students down the left, with the completion date (field in student courses table) as the detail in the crosstab which can be saved as a spreadsheet. 

Unfortunately, I can only summarize (count) the data, not display the 'date' data (completion date) 

  Algebra History
Bear, Yogi 1/1/18 6/1/19
Doe, Jane 2/15/18 1/15/16
Flintstone, Fred   2/5/17
Lewis, Rita 2/5/17  
Smith, Bob 6/1/19 6/1/19

Here's what I've tried
1. summary field in student table, one field per course, using 'max' to get the date. This works, but I have 55 'courses' and I really don't want that many columns in the student table

2. created a new table using title of course as field name, then used automations to load the data. I run into issues with the merge, but I'm still playing with this to see if I can get it to work. 

3. I'm also going to see if I can use connected data to solve???

Appreciate any hints or ideas on how to solve. Feels like I'm missing some really easy solution?






------------------------------
Thanks, CW
------------------------------
Relationships
reports and charts

1 Reply

Sort By
  • DonLarson's avatar
    DonLarson
    Qrew Commander
    5 years ago
    Cathy,

    You have the data now the trick is to turn it into information.  Unfortunately I know of no way to make a summary and cross tab display what you want in Quick Base.

    You mentioned that the Summary field to the Student table is giving you the result but requires Summarizing every Course.   I have clients with hundreds of fields in their tables.  Managing the forms for thoughtful workflows can be a challenge, but having 55 Summary fields will not hurt your application. 

    If that gets you the report you need then it is a pretty painless method compared to having to take your data externally to do visualizations.   If your reporting needs get even more complex, namely your Student Courses table will no longer have unique combinations, then using something like Tableau or Power BI becomes your next option.  They are pure reporting tools and will not manage the day to day workflows like Quick Base.

    If you want to discuss those options, please reach out.

    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------