Unable to save query result to variable?

Question

I'm having trouble finding a way to use data from a query in a join, tried a lot of things.

I have the following query:

(SELECT COUNT(t1.ticketCount) as ticketCount, Ticket.memberId, Year FROM
(SELECT YEAR(date) as Year, showId, Ticket.memberId FROM
Show, Ticket
WHERE Ticket.show = showId )  as t1
GROUP BY Ticket.memberId, Year) 

Basically I want to use the data from that query as a table, table1 in a join to do this:

INNER JOIN (SELECT Year, MAX(ticketCount) AS MaxTicketCount
FROM table1 GROUP BY Year) groupedtt
ON table1.Year = groupedtt.Year
AND table1.ticketCount = groupedtt.MaxTicketCount

My question: How would I go about using the data from the 1st code section above as a table table1 in the second code section(all in one query)?

Thanks!

For example the output of the first code segment would be

And the results after the Join would be

Example of showTable:

Example of Ticket table:

Answer 1

I solved the problem now by making two different querys and doing a select from those two as tables. As shown by the answer here: Create View in MS Access 2007