Reporting query

[spike_access]

Hi all,

I have a very specific case from which I want to pull reporting data from a database.
I have attached an example XLSX with the tab "Data" which represents data from the table, and the tab "Output" with what I want the query/report to create for me.

Summary
I want to count how many episodes are completed, from a delivery perspective, both per Show and Season level and that per Region.
Part from the Type, Title, Season and Episode fields, the only IDs that can be used to "link" episodes to a show or season is the "Show international ID" and the "Season international ID".

An episode is considered COMPLETED when status in "Receipt status" = On platform, and when both "Audio on platform" and "Subs on platform" has status COMPLETED. Any other combination of values is not considered completed.

If "Type" field contains Feature, it should not be counted in any way.

Is there any way of creating this kind of query output in Access?

Best

 

[theDBguy]

Hi. Welcome to AWF!

I might have to give it a test, but that looks like it's just a straightforward Totals query to me.

PS. Looking closer, it may require some pivoting as well. So, maybe a combination of a crosstab query in the mix?

 

[Pat Hartman]

Welcome aboard.

1. The data doesn't match the output. For example - 2 Days only has rows for Netherlands and I can't find any rows for 100 foot wave for season blank for any country.
2. Pivoting the count can be done with a cross tab but crosstabs only pivot one column at a time. I don't understand what the data under the country names column is supposed to show. I'm pretty sure you could have different values for each row so what value would you want to show when there are multiple values?

 

[spike_access]

Hi both,
Thank you for taking time to assist!

In the output tab, where the season is blank is the summary of the Show, so basically all episodes in that Show not split up by season.
In the 100 foot wave example the episode count 3 is because there's only a season 1 with three episodes, as opposed to 2 Dope Queens which has two seasons with four episodes each, hence the blank season row for 2 Dope Queens says 8 episodes.

So calculating the episode count for each region is based on how many episodes in that region for the given show.

In a nutshell, I need to present when a show is "fully completed", a.k.a when all episodes for a given show has: "Receipt status" = On platform, and when both "Audio on platform" and "Subs on platform" has status COMPLETED in a region. Same for seasons.

Hope this clarifies