Using access for our theatrical company's archives and history

[billfry]

My name is Bill, and I am an actor. For nearly 50 years my wife and I ran a small-scale two-person touring theatrical company, based in London, UK, and travelling all over the British Isles, through much of western Europe, across the United States and once even in Kenya.

We retired in 2008, when our joint ages added up to 170, and started to write instead. We had begun a history of our company, when my wife died in 2011, so I am left to finish it alone. A famous University has asked to curate our archives, for which I am collecting our old printed material and other memorabilia.

As we gave about 4,000 performances together (mostly one-night stands) it seems sensible to make a database of the enormous quantity of information we have in our records. That is why I need Access, but at my age it is hard to teach an old dog new tricks.

 

[Steve_Geary]

Hi Bill,
Sounds like you have had a very interesting and full life. Best of luck with the archiving, and I think you've come to the right place for any help you'll need!
Steve

 

[davies107]

welcome aboard Bill!

your life is truly amazing! youre not alone with your work. people here are amazing, they will surely help you.

good luck Bill and enjoy your stay!

 

[Severin]

A good start would be to start entering the data in an Excel spread sheet. That will help you enter the data in an uniform way which is needed in a database. Later it's easily changed into an database.

In Excel you make the first row the headings and then when you enter data keep the format persistent. Meaning if you have a heading saying PerformanceDate then make sure that you only enter dates in that column, if you don't know a date then leave it blank. Etc.

Create a tab for each area and if you want your pictures added then put all of them in one folder, you can make subfolders within this folder. Then you enter data in columns something like this: Date, the corresponding row number on the corresponding tab, Title, Description, Picture path

When you have been doing this for a bit and you see the system, then come back to the forum and ask for help to convert into database. Then you can use this database to enter the remaining part of the data in an easier way!~)

 

[billfry]

To my shame, it is almost a year since my last post, and I never even said thank-you for the three friendly replies you sent me last November. Please, Steve Geary, Davies 107 and Severin, will you accept my grateful thanks and profound apologies? My only excuse is that I am very ancient and almost forgot about the Access Forum. On the rare occasions that I did get back to it, I didn't seem able to make it work.

In spite of Severin's advice I brashly started our table of Performances in Access 2007 rather than Excel, and I seem to have enjoyed beginner's luck. The fields are: ID; Day of the week; Date, month & year; Place (that is to say, the building - hall, theatre, church or whatever); Town (& county); Show ID (referring to separate table of our 40-odd Shows); and Description (quality of performance, size of audience & their reaction, local organiser, problems if any, & other items of interest). Recently I switched to Access 2013, and so far I have put in some 2,700 performances out of a possible total of around 4,000. This means I am just over two-thirds of the way through.

There are other tables beside Performances & Shows. For instance there are tables of our printed Publicity (programmes, posters, flyers etc), there is a table of Venues, which I haven't taken very far yet, and there will need to be a list of significant People, such as sponsors, directors, photographers, costumiers etc. The most important of all is a table of Incidents (into which I have already entered about 1,000 records) describing a whole variety of events in our touring life, most of which involved travelling in a camper van. None of these other tables can only be satisfactorily completed until I have solved one very odd problem.

When I originally designed the Performance table, I wanted to be able to print copies if required, so somehow I succeeded in making each record 3 rows deep. This enabled me to get at least 15 records on a page, so that the whole table now runs to about 180 pages this far. I need the records in the Incidents table to have the same depth and those in the Shows table to have many more rows, but the trouble is that I've forgotten how I originally got those 3 rows. All my efforts to do it again have ended in failure, & none of the questions I put to Microsoft Access Help seems to prompt any sort of solution, so I am badly stuck. Can any of you wonderful people tell me the answer? Help!!

 

[Galaxiom]

You did the right thing going straight to Access. It also sounds like you are doing a pretty good design for a beginner.

Firstly the problem you asked about. You should be able to just drag the line that separates the records in the record selector area on the left of the table. However you should venture into using forms instead of writing directly into the table.

Fields of Day of the Week, month and year are not necessary. Access can calculate all these directly from the date so you don't need to store that extra info.

With regard to your People table you will want a Many-to-many relationship between it and the table that holds the types of things they did. This is done with a junction table and it allows one person to be many different things.

 

[billfry]

Dear Galaxiom, Thank you so much. Your advice about adding extra rows was absurdly simple and worked at once. My only question is, Why was I so stupid that I didn't discover it for myself? (Perhaps you had better not answer that one.)

I'm interested in your comment about not needing the Day of the week. I had guessed that Access could work that out, but it's helpful for me to show the Day on the table for two reasons: 1) It helps to check that I haven't made a mistake (and I do make them). 2) There is a significant variation in audience numbers during the week, and it's valuable to see it at a glance. Is it possible to let Access work out the Day and enter it automatically?

There is one interesting connection that I've not been brave enough to attempt yet. On average we used to add a new show every 18 months and at the same time drop an old show, so as to keep our repertoire down to 6 shows at any one time. (Believe me, that is enough; each show was in two halves of 45 minutes with an interval in between, which meant each of us was talking for about 45 minutes per night. Multiply that by 6, and it means we each had to carry enough lines in our heads to keep going for 4½ hours, roughly twice as long as playing the part of Hamlet uncut.)

More or less every year, we used to bring out a new brochure which naturally contained details of those 6 shows, and more often than not there was at least one change in the repertoire. I have done a table of these Brochures, but I don't feel I'm quite ready yet to relate them to the table of Shows; they are going to need to link to 6 each.

For the moment I am bashing on with the Performances. Now that I'm ⅔ of the way through, there will soon be light at the end of the tunnel, and this is the big one that I've got to get behind me. Many thanks for your help. Please brace yourself for the prospect of hearing from me again.

 

[Galaxiom]

I'm interested in your comment about not needing the Day of the week. I had guessed that Access could work that out, but it's helpful for me to show the Day on the table

Is it possible to let Access work out the Day and enter it automatically?

In Access 2013 a table can have a Calculated field. However if you started in early versions of Access you might still be using the mdb file format which does not support this feature. You would have to convert to accdb format.

However you really should take the next step to entering your data in Forms. The form provides features that will help you enter data. Among other things it can have a textbox that automatically calculates and displays the day of the week when you enter the date.

There is one interesting connection that I've not been brave enough to attempt yet. On average we used to add a new show every 18 months and at the same time drop an old show, so as to keep our repertoire down to 6 shows at any one time.

More or less every year, we used to bring out a new brochure which naturally contained details of those 6 shows, and more often than not there was at least one change in the repertoire. I have done a table of these Brochures, but I don't feel I'm quite ready yet to relate them to the table of Shows; they are going to need to link to 6 each

Many novice developers would include six fields in the Brochure table to record the IDs of the six shows. However this is much better done as a Many-to-many relationship using a junction table.

This structure makes it far easier to retrieve the information for reports, such as listing the Brochures that contain a particular Show. With six separate fields such queries become messy because they must look in each of the six fields of the brochure table.

The junction table stores records with the BrochureID and the ShowID. So a Brochure will have six records in the junction table, one for each show that it included.

Entering this data really does require the use of forms as it is very difficult to complete the junction table without them.

 

[Galaxiom]

Please brace yourself for the prospect of hearing from me again.

I would be more than happy to help in any way I can.

My great grandparents were touring performers in the late nineteenth and early twentieth century. They went all over the British Commonwealth doing comic operas. My grandfather was from his third marriage. Daughters of the second marriage also continued the tradition but it seems not to have been picked up by our branch.

My grandfather never told us a single thing about this and we discovered it after connecting with another branch of the family while doing genealogy research online. We had always wondered why my grandfather was born in South Africa. It also revealed why his mother was recorded his birth certificate as Rose Brandram. Apparently she borrowed this stage name from a famous contemporary, Rosina Brandram.

 

[andrrivalle]

My grandparents were very much happy with this at that time when I was a child and they always love to see it repetedly.Anyhow thanks to all here for nice contribution.





HCG Drops

 

[billfry]

Dear Galaxiom, Thanks again for your incredibly swift response. I haven't had much time today, and I couldn't quite work out how to make a Calculated Field of Mon/Tue/Wed etc, though I could see that the software offered Calculated Date.

Your comments on Many-to-Many look fascinating, and I will have a serious go at it in due course. Just now I am pushing on a bit frantically with the Performance table and have just reached 2,750, which brings me to Monday 4 June 1984. Slow progress, admittedly, and still a long way to go.

At the risk of being boring[FONT=&quot]―[/FONT]Thanks again, Bill

 

[Galaxiom]

Bill, you seem to have enough to do just entering the data without having to worry about learning the intricacies of designing databases.

If you would like to post a sample of your database I would be happy to set up the structure for the junction tables and the forms required.

Make a copy and delete most of the data. Just leave enough to show examples of the kind of records you have. Then do a Compact and Repair which will clean out the deleted stuff. Then zip it to reduce the size and allow you to attach it to your post.

I will set up a front end that you can connect to your live database.

BTW. I do hope you are keeping regular backups in a safe place.

 

[billfry]

Galaxiom, you're unbelievable. It will be quite marvellous if you are willing to set up all the fine tuning for me. Hours seem to go by while I struggle with Microsoft Help. The curious thing is that, whereas Google corrects my silliest errors, Help insists on their preferred word, so that it's no good my writing "show" if they have decided to use "display" instead. (Some genius ought to publish a thesaurus in reverse, so that fools like me could type into it our own version of a question, and the thesaurus would translate it into something Help could understand!)

I will try to prepare the sample for you this week, but please be patient with me; I'm slow and have plenty to interrupt me. Moreover, I'm self-taught about computing, so there are great gaps in my knowledge[FONT=&quot]―[/FONT]if I haven't had to do something already, the chances are I won't know how to. Even your admirably clear and simple instructions sometimes present me with problems. For instance, I think I've seen the "Compact and Repair" command on MS Access and will gladly give it a go; but, although I'm pretty sure I know what "Zip" means, I have no experience of doing it and don't really know where to start. Then again, I have learnt to attach a file to an e-mail, but I have no idea how to attach it to a post like this, though it rather looks as if there may be some instructions further down this page that will guide me through.

Incidentally I have never Backed Up as such (and I'm not really sure how it's done), but every day I save the Database 4 times: the 1st as "Archives" in a folder called "Archives" among my Documents, the 2nd as "Copy of Archives" in the same folder, the 3rd as "Copy of Archives" in a folder called "Archives" on Skydrive, and the 4th as "Archives" in the same Skydrive folder. In that connection, I was frustrated recently by a new add-on to MS Access 2013: suddenly, when I pressed "Save As", it seemed that all I could do was save the file as a template, which was not what I wanted at all. In the end an "expert" friend obligingly disabled the add-on for me, so that I could go on saving the database 4 ways, but I suspect this was only a second-best solution; it's hard to believe that I had fully understood what Microsoft were doing. As you can see, you are dealing with the bottom of the class here. Humble thanks, Bill.

 

[Galaxiom]

Some things are just worth supporting and your archives fit the picture for me. With very little effort I can contribute something that will let you concentrate on the data which is something you are uniquely qualified to do.

The easiest way to zip something is to install 7-zip.

Glad to see you are backing up. It is also a good idea to back up onto another media in case of the unlikely event of someone hacking your cloud storage and wiping it. It does happen.

 

[billfry]

I don't wonder you get a lot of thank-yous. Taken note of your zip advice. Do you think I'm backing up enough? I will send you a gutted database as soon as I can manage it, but for the next few days I'm a bit pushed.

 

[billfry]

Dear Galaxiom, Sorry you've had to wait so long for this message. I have now made a special copy of my Archive and called it Th_Rbt_Galaxion, in which I have stripped all the tables but one to three sample records. At the time of writing this message, I have not yet zipped it, but I have downloaded 7-zip and will try to compress everything before attaching the file and sending it to you.

The one table I haven't stripped is called Shows, which I have left in its entirety because it can give you an overview of the whole operation. After the ID Number it gives the Title of each show, and then its initials, which appear in the related tables. Then come various details: Director, Category (small play, 2-p[erson] revue, etc) and Author(s). Next is the Opening date, which is the order in which I have arranged them, and Music. It is my intention to add the Number of performances and Last performance, but I might as well leave those until I have finished the T R Performances table.

This T R Performances is by far the biggest of all, as I expect it to run to about 4,000 records, whereas so far I have put in nearly 2,800, bringing me towards the end of October 1984, but the samples I have left for you come from a continental tour nearly a year earlier. As you can see, I have put in the Weekday (which I have typed manually, not knowing how to get it automatically), the Date, the Place (i.e. the building or institution), the Town, the ID number from Shows and the initials of the particular show. The final field is a brief description of the evening (occasionally afternoon or even morning) with the name of the local organiser, the numbers and reaction of the audience and anything else of interest. In the long run, I should like to add two more ID numbers from tables that I hope to create: one for Sponsors and the other for important People.

The next most important table is called Incidents, which describes events other than performances that seemed to us to be important. There are already more than 1,000 records in this table, but its construction is still rather primitive. Day, Date, Place and Town are self-explanatory, but we may need to clarify some of the entries under Incidents. For instance, I have tried to include all the significant plays we have seen, we ourselves do some writing (S stands for my wife, Sylvia Read, whereas I am entered as W for William Fry), RaDiuS is the shortened name of the Religious Drama Society, and Mercy is our pet-name for our final motor caravan, completed for us on a Mercedes chassis by Jennings Coachwork of Crewe. The three samples given here are admittedly rather boring, but from time to time we have exciting tales to tell, such as the morning when we met a murderer on the run with the police hot on his track. (It was interesting that Sylvia recognised him at once, whereas I refused to believe her. He was too cunning for the policeman in a van and escaped down a deserted railway line, finally being arrested over six months later.)

There are a number of other tables I would like to add, but I am now trying to attach the database. The trouble is I don't seem able to do it. Please help me! Bill

 

[billfry]

Dear Galaxiom, Sorry you've had to wait so long for this message. I have now made a special copy of my Archive and called it Th_Rbt_Galaxion, in which I have stripped all the tables but one to three sample records. At the time of writing this message, I have not yet zipped it, but I have downloaded 7-zip and will try to compress everything before attaching the file and sending it to you.

The one table I haven't stripped is called Shows, which I have left in its entirety because it can give you an overview of the whole operation. After the ID Number it gives the Title of each show, and then its initials, which appear in the related tables. Then come various details: Director, Category (small play, 2-p[erson] revue, etc) and Author(s). Next is the Opening date, which is the order in which I have arranged them, and Music. It is my intention to add the Number of performances and Last performance, but I might as well leave those until I have finished the T R Performances table.

This T R Performances is by far the biggest of all, as I expect it to run to about 4,000 records, whereas so far I have put in nearly 2,800, bringing me towards the end of October 1984, but the samples I have left for you come from a continental tour nearly a year earlier. As you can see, I have put in the Weekday (which I have typed manually, not knowing how to get it automatically), the Date, the Place (i.e. the building or institution), the Town, the ID number from Shows and the initials of the particular show. The final field is a brief description of the evening (occasionally afternoon or even morning) with the name of the local organiser, the numbers and reaction of the audience and anything else of interest. In the long run, I should like to add two more ID numbers from tables that I hope to create: one for Sponsors and the other for important People.

The next most important table is called Incidents, which describes events other than performances that seemed to us to be important. There are already more than 1,000 records in this table, but its construction is still rather primitive. Day, Date, Place and Town are self-explanatory, but we may need to clarify some of the entries under Incidents. For instance, I have tried to include all the significant plays we have seen, we ourselves do some writing (S stands for my wife, Sylvia Read, whereas I am entered as W for William Fry), RaDiuS is the shortened name of the Religious Drama Society, and Mercy is our pet-name for our final motor caravan, completed for us on a Mercedes chassis by Jennings Coachwork of Crewe. The three samples given here are admittedly rather boring, but from time to time we have exciting tales to tell, such as the morning when we met a murderer on the run with the police hot on his track. (It was interesting that Sylvia recognised him at once, whereas I refused to believe her. He was too cunning for the policeman in a van and escaped down a deserted railway line, finally being arrested over six months later.)

There are a number of other tables I would like to add, but I am now trying to attach the database. The trouble is I don't seem able to do it. Please help me! Bill

 

[Galaxiom]

Attachments can only be done in this site's Advanced editor. Clikc the Go Advanced button.

Manage Attachments. Choose File and browse to the location on your computer.

Then press Upload.

The file will need to be in zip format. The 7-zip default is 7z so you need to change this to zip in the zip process.

 

[andrrivalle]

Thanks for such a nice input.It's good to be part of this larger discussion.

 

[billfry]

Here goes. After struggling for two days, I had to appeal for some professional help, but here at last is the compressed file. I do hope it proves to be legible and intelligible. Sorry to be such a fool, Bill. (Maybe it will be easier next time!)