RMweb Gold Stubby47 Posted February 9, 2017 RMweb Gold Share Posted February 9, 2017 Miss P, Can I suggest you add a smaller spreadsheet, just containing the books done / being done, as a quicker download, and add instructions on the website to contributors "to check this list first !" ? Stu Link to post Share on other sites More sharing options...
Miss Prism Posted February 9, 2017 Share Posted February 9, 2017 I am reluctant to add a third, 'smaller' sheet to notifiy books that have been done or being done. (The sizes of the current files are not worth worrying about.) But I have added a note on the webpage for potential contributors to check the main file to see what has been done or being done. Link to post Share on other sites More sharing options...
Clearwater Posted February 9, 2017 Share Posted February 9, 2017 Question - if a photo has more than one loco, should there be 1 row per loco added to the spreadsheet ? I would suggest 'Yes'. ( makes note to avoid books on loco sheds ) The approach I took was to identify a primary subject and put the others in a "other locos". Not sure how miss P has handled on the template. However, is a 2s job to enter the details for the first loco, copy the common info and paste it into subsequently rows with the other locos now in the primary field. Link to post Share on other sites More sharing options...
Miss Prism Posted February 9, 2017 Share Posted February 9, 2017 Not sure how miss P has handled on the template. I have retained your initial format, Clearwater, with the 'Other locos' column. On the blank sheet, I have done a few notes above each column to indicate briefly what each column is for. No doubt improvements could be made to those notes in due course. And, as you say, doing a new row for another loco is no biggie. Link to post Share on other sites More sharing options...
RMweb Gold The Fatadder Posted February 9, 2017 Author RMweb Gold Share Posted February 9, 2017 Question - if a photo has more than one loco, should there be 1 row per loco added to the spreadsheet ? I would suggest 'Yes'. ( makes note to avoid books on loco sheds ) The way I've looked at handling these is to either add two lines (if both locos are clearly visible) or comment in the notes if they are not clear. Link to post Share on other sites More sharing options...
HowardGWR Posted February 9, 2017 Share Posted February 9, 2017 The way I've looked at handling these is to either add two lines (if both locos are clearly visible) or comment in the notes if they are not clear. I am a data analyst by trade (retired of course). The entity in that spreadsheet, outlined by the OP, represented by each row in it, is a photo, not an engine, although I think the file was probably started that way. Thus the unique identifier of the row is not an engine number, but the book /page number in which it appeared. If the same photo was reproduced in many books, there would be an entry for each appearance and thus a row. It then becomes irrelevant how many times an engine appears in the database. Each row has attributes and these could include an engine number, or if there were two engines (double heading or shed photos), two. Under relational database rules you can't do that, quite rightly, as you are now recording data about engines (in this case which photos they appear in) so you need a new database file called engines and another file called 'engine appears in'. That file has rows with two columns 'engine number' and 'appears in' (which is the book / page number). Even that last item is not unique so you really need another item which identifies in which position, the photo is on the page, when there are more photos on the page in question. Hopefully that gives a flavour of what one has to analyse. Link to post Share on other sites More sharing options...
RMweb Gold Stubby47 Posted February 9, 2017 RMweb Gold Share Posted February 9, 2017 Howard, after your opening line, I was expecting "and here's one I made earlier"... Quite agree with your analysis of the data, the number of sub tables grows exponentially without anyone noticing. Link to post Share on other sites More sharing options...
Clearwater Posted February 9, 2017 Share Posted February 9, 2017 Howard IIRC, database fields can have either a 1:1 or 1: many relationship? If so, your photo is the unique identifier but you have to have a system of saying top left, middle etc. Clearly locos, liveries, locations etc all have 1 to many but it'd be a gargantuan task to build those fields. I'd guesstimate that there's been well over 100k of locos alone operate in the uk and I wouldn't like to try and guess how many carriages or wagons... It'd be interested to know how BR handled its asset database as that is pretty similar to what we're talking about. In my spreadsheet, I think I'd recognised that difficulty so rather than identify specifically, I'd left it slightly vague i.e. You input either the plate number or page plus description. Given I was writing for my own use, I'd reasoned I was most likely to search for a loco or class and then use he photo/page reference to be able to fin it quickly within the book. Not a true database in the proper sense but more a guide to find something quickly David Link to post Share on other sites More sharing options...
martinT Posted February 9, 2017 Share Posted February 9, 2017 A blank, formatted, sheet is now available from http://www.gwr.org.uk/no-photo-database.html Thanks. The only query I've got (sorry about this) is your suggested format for the date is text: 'dd/mm/yyyy - (otherwise something close, e.g. '1934', '1920s')' This doesn't allow sorting by date order. The book I'm indexing provides either full dates, month + year, year only, or no date & I've made an estimate. By reversing the date order at least I can sort into years & months. Thus I've done: 1951-Aug-25 or 1951-Aug or 1951- or 1951 est. I've tried replacing the 3 month letters by 2 digits thus: 1951-08-25 but Excel knows best & does an auto convert to: 25-Aug-48 Link to post Share on other sites More sharing options...
Clearwater Posted February 9, 2017 Share Posted February 9, 2017 Within excel, you can change the date format however it assumes that a two digit year is within a certain range. Currently, on my version, entering 31/12/29 is assumed to be 2029 but 1/1/30 is 1930. Personally, i always use yyyy as that's clear. As an aside, if you type =year (cell with date in as reference - e.g. A1), the formal will give you the year. If you use the dd/mm/yyyy format, it will always sort on date (excel converts dates to serial numbers so one doesn't have to - unlike file saving where your convention is required if you want to manually save in date order as opposed to relying on when the file was last saved). If you have partial data, e.g. 1934, I'd suggest 01/01/1934 or 01/month/1934. To a savvy user, it's probably clear that the picture isn't really taken on this Link to post Share on other sites More sharing options...
RMweb Gold The Fatadder Posted February 9, 2017 Author RMweb Gold Share Posted February 9, 2017 That is precisely the approach that I was thinking Link to post Share on other sites More sharing options...
Miss Prism Posted February 9, 2017 Share Posted February 9, 2017 If you have partial data, e.g. 1934, I'd suggest 01/01/1934 or 01/month/1934. To a savvy user, it's probably clear that the picture isn't really taken on this I am really not in favour of inventing or manipulating dates merely to satisfy the inflexible demands of Excel. We need to rethink this. Link to post Share on other sites More sharing options...
RMweb Gold Stubby47 Posted February 9, 2017 RMweb Gold Share Posted February 9, 2017 Excel will recognise a date when entered ( the language/location setting of the pc is used to determine the default format) and dates can be sorted quite simply - I'm struggling to see the issue here. Maybe any potential issues which appear when data is loaded into the main sheet can be highlighted ? Also, because of the possibility that the contributor's date data might not be in UK format, maybe indicate on the email to Miss P if that is the case, so dates can be re-formatted to suit. Edit: partial dates have the /01 added by excel. If it really is a problem, split the date into 3 columns Link to post Share on other sites More sharing options...
kandc_au Posted February 10, 2017 Share Posted February 10, 2017 Hi, From memory can't you change the form of the date in the excel master sheet? Ithought it could be changed to read either day first month first or year first, but I may also be wrong. Khris Link to post Share on other sites More sharing options...
RMweb Gold The Fatadder Posted February 10, 2017 Author RMweb Gold Share Posted February 10, 2017 Lockett's Great Western Steam in the West Country is now complete, just needs to be adjusted into the slightly different column format of the master sheet and will be ready for upload. Next will be the more interesting (to me) Great Western Steam from 34-49 which contains a lot more useful post war photos..... Link to post Share on other sites More sharing options...
Miss Prism Posted February 10, 2017 Share Posted February 10, 2017 On the thorny date issue: 1 We have a format (dd/mm/yyyy) for precise dates. It is not unreasonable to expect contributors to follow this format where the date reference is precise. 2 We do need to allow and accommodate imprecise dates, and of various levels of imprecision. ("November 1934", "1934", "1934?", "mid-1930s", "1930s?", "?" etc.) Presumably the imprecise forms are best input as a 'General (no specific format)' number? Not being an Excel date expert, I do not know what such mixing of number formats implies for search and sorting purposes, but would this be a reasonable compromise, and a sensible way forward? Link to post Share on other sites More sharing options...
Miss Prism Posted February 10, 2017 Share Posted February 10, 2017 Btw, just in case anyone has sent me a sheet to my e-mail address, I am not back at my 'e-mail address machine' until Sunday, Link to post Share on other sites More sharing options...
RMweb Gold The Fatadder Posted February 10, 2017 Author RMweb Gold Share Posted February 10, 2017 Post 1 now updated with the latest version. I have added two additional columns on the right hand side, covering Tender type and Colour (the latter to indicate if it is a colour photo) Link to post Share on other sites More sharing options...
Miss Prism Posted February 10, 2017 Share Posted February 10, 2017 I have added two additional columns on the right hand side, covering Tender type and Colour (the latter to indicate if it is a colour photo) That seems reasonable. Link to post Share on other sites More sharing options...
jonny777 Posted February 10, 2017 Share Posted February 10, 2017 Some of the dates entered are now completely beyond me. 7921 was photographed on 38941 ? Link to post Share on other sites More sharing options...
RMweb Gold The Fatadder Posted February 10, 2017 Author RMweb Gold Share Posted February 10, 2017 Due to a format change on the date field, I will have a look and fix once I am on the train to Stroud in 20 mins or so Link to post Share on other sites More sharing options...
Clearwater Posted February 10, 2017 Share Posted February 10, 2017 Some of the dates entered are now completely beyond me. 7921 was photographed on 38941 ? That sounds like an excel serial number? Try formatting it by right clicking on the cell, select format cells and then custom. In custom, scroll down until you find dd/mm/yyyy and it will give us a recognisable date! David Link to post Share on other sites More sharing options...
RMweb Gold The Fatadder Posted February 10, 2017 Author RMweb Gold Share Posted February 10, 2017 I have re uploaded the summary with the date field set to "short date" which has corrected it for now. Off hand I am not entirely sure how you change a date to a text field without loosing the details, but will have a look. Post 1 has also been updated with the completed books / items in progress. David, in the first page of your file you listed The Big Four in Colour, but I cant see any photos from this in the details table. Am I missing something? Link to post Share on other sites More sharing options...
Clearwater Posted February 10, 2017 Share Posted February 10, 2017 Rick I think To convert a number to text in excel you use text(ref). Alternatively I often copy and paste as values. As ever with excel, there's lots of different ways to get the same result. On my list, I think I'd just put the big 4 in colour in as I was cataloguing but I didn't actually get around to doing it. Btw, I agree a colour/b&w field would be useful. David Link to post Share on other sites More sharing options...
RMweb Gold The Stationmaster Posted February 10, 2017 RMweb Gold Share Posted February 10, 2017 Line Reference Codes - some pages (!) back I mentioned a system of consistent line reference codes to identify any particular route or places on it and I have now tracked it down. This is the main part of the site which explains the system - http://www.s-r-s.org.uk/railref.php#cross And this is the area specifically covering the GWR (Post Group) - http://www.s-r-s.org.uk/RailRef/ref-gw.php Link to post Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.