Reporting query (1 Viewer)

spike_access

New member
Local time
Today, 12:47
Joined
Mar 22, 2022
Messages
14
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
 

Attachments

  • Example reporting W3.zip
    12.8 KB · Views: 213

theDBguy

I’m here to help
Staff member
Local time
Today, 04:47
Joined
Oct 29, 2018
Messages
21,358
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

Super Moderator
Staff member
Local time
Today, 07:47
Joined
Feb 19, 2002
Messages
42,981
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

New member
Local time
Today, 12:47
Joined
Mar 22, 2022
Messages
14
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
 

Users who are viewing this thread

Top Bottom