Cinema in Context



How To Do

How many cinemas were active in Rotterdam in every year between 1921 and 1940?


For this question we will work with the ‘My Data’ set: by saving data and modifying it in MS Access, we can create a listing n overview of the number of cinemas in Rotterdam in the specified period.

Method

Step 1

  • Open Cinema Context in a new window
  • Enter ‘Rotterdam’ in the first search field and select City from the pull-down menu behind it.
  • Enter ‘1921’ and ‘1940’ in the fields marked active between.
  • Click on
  • Click on add all to my data.
  • In the saved data window (if the window is not visible, click on My Data at the top of the page), click on download contents. Save the results file in a folder you can access easily.

Step 2

In order to answer this question, you must have the file HowToDoInAccess.xslt on your computer. If you have not installed this file yet, then follow use the method indicated in our FAQ Where do I find the transformation file and what is it for? and then continue to Step 3. If you have installed this file already, then follow the procedure outlined below.

  • Launch MS Access.
  • Select File > New from the upper left corner.
  • On the right-hand side of the screen, create a Blank database.
  • Store the database in a folder that is easily accessible.
  • Click on File > GetExternal Data > Import.
  • Select XML from the pull-down menu Files of type and then choose the file you had saved before (the saved data set) with the found data.
  • Click on Import.
  • Click on Options > Transform.
  • Select HowToDoInAccess, click OK to confirm, and then OK once more to import the data.

back to top

Step 3

You will now see a series of tables in the right-hand side of the database window (if not, click on Tables under Objects on the left). By designing a query, we can link these tables to each other.

  • On the left-hand side, click on Query under Objects, and then double-click on on the right. A help window will appear with the names of all tables.
  • Select tblCinemaActivePeriod and click Add. Then click on Close.
  • Click on Tables > Create table by entering data.
  • In the column Field 1, enter the years ‘1921’ thru ‘1940’.
  • Right-click on Field 1 and select Rename Column. Give Field 1 the name Years.
  • Click the cross to close the window, after which you will have to give the table a name. Name it Year.
  • Access will warn you that the primary key has not been defined. This is not necessary, so select No.
  • You will now see Year listed among the other tables. Drag it to the upper half of the Query.




  • From the table tblCinemaActivePeriod, select the field cinema_id and drag it to the first column in the lower half of the query module.
  • From the table Year, select the field Years and drag it to the second column in the lower half of the query module.
  • Click on (Totals) at the top of the toolbar.
  • In the column cinema_id on the line Total, select Count, and in the column Years on the line Total, select Group by.
  • In the top line of the third column, enter: Left([d_opened];4). This way, you will only search through the first four numbers from the left in the field d_opened.
  • In the top line of the fourth column, enter: Left([d_closed];4). This way, you will only search through the first four numbers from the left in the field d_closed.
  • In the column Left([d_opened];4) select Where from the line Totals, and enter >=[Year] at the bottom on the line Criteria. This filters out all the dates before or during a certain year.
  • In the column Left([d_closed];4) select Where from the line Totals, and enter <=[Year] at the bottom on the line Criteria. This filters out all the dates after or during a certain year.
  • Save the query: File > Save > and enter a name like ‘CinemasPerYear’ and click on Save.

If you now click on View > Datasheet view, you will see the table with the answer to the research question.



back to top

Discussion

Based onLooking at this result, you can ask yourself a number of questions:

  • Are all of these 20 cinemas ‘regular’ cinemas or does this number also include travelling cinemas?
  • What is your definition of ‘cinema’? Would you include a theatre that only screens films occasionally?