Here is a challenge...
I have 3 tables linked together in the following formation:
Genres <-> CDATA (Transactions) <-> Advance (Days in advance, where each record is a number 0 - 365)
See screenshot 1.
There are 35 genres, which are displayed in the "genres" layout. Within this layout, I have a portal set up to pull in all transactions that match that genre. e.g., there are 77 CDATA (Transactions) records where Genre = Acoustic ; portal is displaying 77 related records.
For each of those related records, I am able to see how many days in advance (See screenshot 2).
All fine, so far, until...
I want to be able to count within that selection of related records how many transactions are 0 days in advance, how many are 1 days in advance, how many 2, 3, 4 etc... So, I want to see it like this:
00 days in advance - 39 transactions
01 days in advance - 01 transactions
02 days in advance - 01 transactions
03 days in advance - 01 transactions
04 days in advance - 01 transactions
05 days in advance - 03 transactions
06 days in advance - 02 transactions
07 days in advance - 03 transactions
How can I get these figures? I have gone around in circles trying many different methods including creating extra occurrences of tables, extra fields, different types of referencing, but it is always returning 11288 transactions for 0 days in advance, which is the number of transactions made 0 days in advance, regardless of genre.
I'm able to see which "advance" records are represented from the perspective of the genre (See screenshot 3). So, I can see that there are no Acoustic tickets where days in advance = 11, 12 or 14. But I cannot see how many times each "advance" record is represented for that genre (Acoustic), if that makes sense...
Thanks very much in advance!