Repeat data in a column and set counter

[Neelix]

Hi,

I use Base as a frontend to a mysql database to fill my database with TV series episodes.

Here is my database schema :

And the SQL code :

CREATE TABLE SERIES(
   seriesId INT AUTO_INCREMENT,
   seriesTitle VARCHAR(50)  NOT NULL,
   PRIMARY KEY(seriesId)
);

CREATE TABLE SEASON(
   seriesId INT,
   seasonNumber TINYINT,
   PRIMARY KEY(seriesId, seasonNumber),
   FOREIGN KEY(seriesId) REFERENCES SERIES(seriesId)
);

CREATE TABLE EPISODE(
   seriesId INT,
   seasonNumber TINYINT,
   episodeNumber TINYINT,
   episodeTitle VARCHAR(50)  NOT NULL,
   episodeFrenchTitle VARCHAR(50),
   PRIMARY KEY(seriesId, seasonNumber, episodeNumber),
   FOREIGN KEY(seriesId, seasonNumber) REFERENCES SEASON(seriesId, seasonNumber)
);

I would like to take the data input easer, with automatically repeating the season number and making the column episode number a counter.
Therefore, I set the season number field property ‘Repeat’ to true and the episode number property counter to true.

That doesn’t work.

How could I do ?

[Neelix]

So, I found how to automatically insert and repeat the season number. The trick is to link the seasonNumber fileds from form and subform.

So, it remains the question: how to get the episode number field automatically filled, acting as a counter.

[nicholas59]

Xcuse me, but am a bit confused i.e.: supposed you are retrieving your tables in the LO front end, then you may obtain your Query somehow like this (as an example):

Select COUNT Table1.field1, Table2.field2, TableA.fieldA … From Table1, Table2, TableA;

[Villeroy]

Series_Episodes.odb (12.8 KB)

[Neelix]

I think I didn’t clearly expose my problem.
My form is for data input only. But, there are a lot of episodes in each season, therefore, to save time, I need to automate the episode number input, so I only need to input the original and french titles.

Villeroy, your form is better as mine. At least, it make easier to continue an interrupted input. Thanks.
But it doesn’t automatize the episode number input in the form

[nicholas59]

Dear Mr. Neelix, may I ask you to review SQL architecture, so that you can realize that you cannot obtain in DDA (Direct Data Access) what you would get in SELECT MODE instead?

[Villeroy]

“Automation” implies that you always enter the episodes in the right order. I’d prefer the manual input of this simple integer.

[Neelix]

Ok nicholas59. But, isn’t it possible with a macro ?

Villeroy, You’re right. But, in that case, it were always possible to manually input certain episodes. No ?

[nicholas59]

I beg your pardon Mr. Neelix, I am not prepared enough for coding macros…

Kind regards