Television episode tracker

[ilikecats]

Around a decade ago, I used a little program called epCheck. (Spam filter prevents me from posting link, so look up Skwire Empire.) Looked like this:

Unfortunately, it broke a few years ago and dev never got around to fixing. I'd like to try to recreate some of it in Access, mostly to see if I can. (I'll worry about getting the information later.)

I think I'd have two tables, Series and Episodes. What I'm unsure of is how to handle Seasons. Episodes are grouped under seasons, which are grouped under series. Does that make it its own table? And is anything else I should add?

 

[plog]

I think season would just be a numeric field in the episode table.

If you have other pieces of data that go with a season but not an episode nor the series, then you might need a seasons table. But I can't think of such data.

 

[ilikecats]

yeah, that's what I thought. Is it possible to group by series, as seen in the screenshot?

 

[June7]

Did you mean "group by season"? Can sort by season but a form would not allow for a season header, at least not easily if at all. That would be feature of a report.

I wonder what was used to code that GUI.

I downloaded and ran it. Maybe part that broke is data download or requires establishing account on data site.

Have you confirmed you can automate data download?

 

[Gasman]

It gets it's data from TheTVDB.com and they have an API and access is free is you earn less than $50k per year?
Though for an individual that is not needed, or so they say.

I cannot get it to work. I get the series number in the list, not the series name?
Have you tried contacting the developer?

 

[ilikecats]

The program is using an old verison of the API that isn't functional. It stopped working in 2019 and the author said he' "wants to fix it" but never did. There's a forum link on the Skwire Empire page.

I just want something to quickly check episode descriptions for a few shows without popping onto wikipedia. Yes, I can just copy the wikipedia pages to a notetaking program, but why not try this instead?

 

[Pat Hartman]

yeah, that's what I thought. Is it possible to group by series, as seen in the screenshot?

No. That isn't how forms work. You can make something that looks like the image if you make it a report.

 

[cheekybuddha]

I just want something to quickly check episode descriptions for a few shows without popping onto wikipedia. Yes, I can just copy the wikipedia pages to a notetaking program, but why not try this instead?

So you can use the new API from Access.

You could also 'mimic' the look of the control in your app by using a listbox for each season in a continuous form

 

[June7]

by using a listbox for each season in a continuous form

How many listboxes would that be?

 

[CJ_London]

You can do this with a form -see this link

Making a form look like a report

As a result of this post https://www.access-programmers.co.uk/forums/threads/emulate-reports-grouping-levels-with-forms.326697/ I thought I would diddle around and come up with some examples. This is a simple format - only shows the customer once and uses conditional formatting to change the...

www.access-programmers.co.uk

Think it is the third example

 

[tvanstiphout]

I think season would just be a numeric field in the episode table.

If you have other pieces of data that go with a season but not an episode nor the series, then you might need a seasons table. But I can't think of such data.

I can think of the set of actors that might change for each season.

 

[cheekybuddha]

How many listboxes would that be?

As many as there are seasons in a show.

But really only one, since a continuous form just repeats a single row

 

[June7]

I should have said earlier "couldn't be done and allow form data to be editable".
If data editing is not important, a report can sit on a form, however, mouse scroll wheel won't work - have to drag scroll bar.

Cheeky, I don't see how one listbox could show all seasons and appear as shown in OP image, unless it uses UNION query as RowSource and VBA to requery when navigating main recordset.

 

[CJ_London]

allow form data to be editable

So far as I can see the OP hasn’t said it’s a requirement, but if it is, you can pass the recordset to ado although you would lose the ability to sort/filter unless you write your own shortcut menus

 

[cheekybuddha]

Cheeky, I don't see how one listbox could show all seasons and appear as shown in OP image, unless it uses UNION query as RowSource and VBA to requery when navigating main recordset.

RecordSource for continuous form is something like:

SELECT
  s.ShowID,
  se.SeasonID
From Shows s
INNER JOIN Seasons se
        ON s.ShowID = se.ShowFK
ORDER BY
  s.ShowName
  se.SeasonNumber

Then listbox RowSource would be:

SELECT
  ID,
  [Number],
  Episode,
  Title,
  AirDate
FROM episodes
WHERE SeasonID = [SeasonID]
ORDER BY
  [Number]
;

Continuous subform linked Master/Child to main form on ShowID

 

[June7]

Where is SeriesID in that RowSource? How would that RowSource show all seasons for a specific show as shown in OPs image? Master/Child Links have nothing to do with listbox (or combobox) data.

 

[cheekybuddha]

And how would that RowSource show all seasons for a specific show?

Presumably via normalised tables:

Shows -> Seasons -> Episodes

 

[June7]

As already mentioned by plog, probably don't need a table for Seasons. Season would just be a number field in Episodes table. Initial year of airing would be attribute of Series table. Except now I see the site offers data like Season Artwork and Season Trailers. If OP wants those details, a Seasons table would be needed.

Still not seeing how you would expect that RowSource to produce output to mimic OP's image.

 

[Edgar_]

I know you're trying to learn, but if you get stuck, this file could help. I'm doing the no-seasons table approach here.

 

[KitaYama]

Personally, I wouldn't go with showing a report in a form.
If I wanted to use this app, I would keep @Edgar_ 's design and instead of the report, I would use a from with a tree view that ONLY shows seasons and episodes. @MajP's tree view class is very suitable for this case. It has a lot of features like drag & drop, move, sort, edit that makes it more useful than a report. The form can be linked to the main form (parent/child) and clicking the series list, will update the tree view.
The tree can be collapsed or expanded as default, per user's preference.
I've not access to my PC, so I can not show a sample, but I'm sure you can imagine how it looks.

Again, it's only a personal preference and you may not agree with it and prefer a report instead.