Microsoft Excel - How to show pics

[Colin G. 147]

Forgive me saying so, but you sound like a spreadsheet user who is not really au fait with database subtleties? In the above case, you wouldn't keep your sales reference data in your main table, but in a separate related table with COIN-ID links between the two. In your second table you would have a matrix / array / recurring field (call it what you will) with as many repetitions as you think you may need over time (30 say). You then populate from occurrence 1 onwards, and that's your table.

Then in your main table, you define a portal to the second table in either one of your existing layouts (or create a new layout for it), and there you define exactly how many of the field's occurrences you want to display - i.e. from 1 up to the full 30, or expand as you go. So you don't need those 14 extra columns you'd be stuck with in a spreadsheet, you'd only need how many you decide you want to see, and arranged in whatever pattern suits you, unlike the inflexible grid format of the spreadsheet.

I know how to do this in FileMaker, and believe me, Access will allow much the same.

You are quite right that I don't know all the ins and outs of databases (or spreadsheets for that matter), in fact I find computers incredibly depressing things to use as they frequently don't give me the answer I want - usually because I don't have enough in depth knowledge of a program.

All I need is something that I can understand and use easily to provide me with the information I am seeking to collate together with an image of the coin which I can compare with an illustration in another catalogue and so record the new coin within an existing provenance, or I can generate another known example of that type. In the case of the example above I know that its provenance is ex E W Wigan (collection bt by Rollin & Feuardent 1872), H Webb 560, J G Murdoch 194, G Hamilton-Smith (1913) 126, K Vaughan-Morgan 336, V J E Ryan 1307, J R Vincent, J G Brooker 1153, 3 x SNC references, A Morris (from Roddy Richardson) and finally me (from Lloyd Bennett). It is no help to have the details neatly tabulated out of view from my perspective as the list of names immediately tells me which coin it is, just as the auction catalogue will have a list of past owners underneath the description. This is why putting all the names into one box works so well for me. Any system that requires a single field entry for each name dismembers the provenance. Having a link to the image allows me to compare a new catalogue reference with an existing provenance.

I have been giving Sion a few pointers on access, and would be willing to do the same with anyone, the basics are surprisingly easy, and then its a case of tinkering!!!

[Peckris 579]

Forgive me saying so, but you sound like a spreadsheet user who is not really au fait with database subtleties? In the above case, you wouldn't keep your sales reference data in your main table, but in a separate related table with COIN-ID links between the two. In your second table you would have a matrix / array / recurring field (call it what you will) with as many repetitions as you think you may need over time (30 say). You then populate from occurrence 1 onwards, and that's your table.

Then in your main table, you define a portal to the second table in either one of your existing layouts (or create a new layout for it), and there you define exactly how many of the field's occurrences you want to display - i.e. from 1 up to the full 30, or expand as you go. So you don't need those 14 extra columns you'd be stuck with in a spreadsheet, you'd only need how many you decide you want to see, and arranged in whatever pattern suits you, unlike the inflexible grid format of the spreadsheet.

I know how to do this in FileMaker, and believe me, Access will allow much the same.

You are quite right that I don't know all the ins and outs of databases (or spreadsheets for that matter), in fact I find computers incredibly depressing things to use as they frequently don't give me the answer I want - usually because I don't have enough in depth knowledge of a program.

All I need is something that I can understand and use easily to provide me with the information I am seeking to collate together with an image of the coin which I can compare with an illustration in another catalogue and so record the new coin within an existing provenance, or I can generate another known example of that type. In the case of the example above I know that its provenance is ex E W Wigan (collection bt by Rollin & Feuardent 1872), H Webb 560, J G Murdoch 194, G Hamilton-Smith (1913) 126, K Vaughan-Morgan 336, V J E Ryan 1307, J R Vincent, J G Brooker 1153, 3 x SNC references, A Morris (from Roddy Richardson) and finally me (from Lloyd Bennett). It is no help to have the details neatly tabulated out of view from my perspective as the list of names immediately tells me which coin it is, just as the auction catalogue will have a list of past owners underneath the description. This is why putting all the names into one box works so well for me. Any system that requires a single field entry for each name dismembers the provenance. Having a link to the image allows me to compare a new catalogue reference with an existing provenance.

The beauty of databases is that you can create a separate layout just for provenance information, with any other identifying information for each coin that you need to see. Then to see all your provenance for all or any coins, you just switch to that layout and there everything is.

[Rob 3,002]

The beauty of databases is that you can create a separate layout just for provenance information, with any other identifying information for each coin that you need to see. Then to see all your provenance for all or any coins, you just switch to that layout and there everything is.

I'm still not convinced about the wisdom of having the provenance information (which is the raison d'etre for the entire exercise) stored in a separate layout away from the main page (or the concordance in yet another). It also begs the question as to what I would put in the main database. It seems more sensible to have everything under one roof rather than in several databases. The only things I store away from this are the images which are systematically named as the standard commonly used detailed classification (eg. Besly or Morrieson die pairings) together with the image sale reference, and stored in clearly identifiable folders giving the mint and denomination. This also allows me to run down the image folders and files in quick time without entering the database if I want to compare an image with one already stored.

I don't know if the attached will show up adequately, but it illustrates the 6 columns as used with the individual coin provenance forming the link. As the full sheet displays all the examples I have recorded to date, a quick scan down the list (16 in the case of the Exeter C11 crown) will tell me what to look for. For example, Cumberland Clark 95 I know to be the C11 crown with the 5 legged horse. Click on the box with the provenance and the image pops up. It's simple, but quite effective.

Edited February 27, 2011 by Rob

[Peckris 579]

The beauty of databases is that you can create a separate layout just for provenance information, with any other identifying information for each coin that you need to see. Then to see all your provenance for all or any coins, you just switch to that layout and there everything is.

I'm still not convinced about the wisdom of having the provenance information (which is the raison d'etre for the entire exercise) stored in a separate layout away from the main page (or the concordance in yet another). It also begs the question as to what I would put in the main database. It seems more sensible to have everything under one roof rather than in several databases. The only things I store away from this are the images which are systematically named as the standard commonly used detailed classification (eg. Besly or Morrieson die pairings) together with the image sale reference, and stored in clearly identifiable folders giving the mint and denomination. This also allows me to run down the image folders and files in quick time without entering the database if I want to compare an image with one already stored.

I don't know if the attached will show up adequately, but it illustrates the 6 columns as used with the individual coin provenance forming the link. As the full sheet displays all the examples I have recorded to date, a quick scan down the list (16 in the case of the Exeter C11 crown) will tell me what to look for. For example, Cumberland Clark 95 I know to be the C11 crown with the 5 legged horse. Click on the box with the provenance and the image pops up. It's simple, but quite effective.

This is the 'not understanding databases' part : the information isn't STORED separately. It would all be stored in the one database (except pictures, which by virtue of their size are best stored in a separate purpose-built table and simply 'appear' before your eyes in the main database). Layouts aren't tables. Layouts are simply views of your data which you can arrange and design to your heart's content. In each layout you can choose to display as much or as little as you want - you just pull the fields into each layout, and can repeat a field in every single layout if you want.

Here, just to demonstrate. I have a selection of layouts I designed :

Data Entry (most fields are here, arranged so the data for each coin fills one screen)

Values (a list view showing latest Spink values)

Buying (purchase details, with a good text entry to add comments)

Locations

Calculator (to input Spink values)

Spink print out list (to take to library)

and that's just a few. Some fields appear on most layouts, but I can change layouts whenever I want without disturbing the overall database. It's much more flexible than spreadsheets.

Edited February 27, 2011 by Peckris

[Gary 7]

If you can post the (for dummies) version accumulator

Actually it's really easy, just very well hidden for some reason.

This is what you do:

1. Choose the cell that you want to activate the coin pic. when you mouse-over (if you want separate pics for OBV & REV just use two cells, one for each)

2. Right click on that cell then choose 'insert comment' from the drop down menu

3.The text box which pops up may contain some dummy text which you can just delete.

4. Place your mouse pointer over the BORDER of the box, right click and choose 'Format Comment' from the drop down menu. This is the important bit, because if you right click over the centre of the box you won't get the option to add a pic later!

5. In the Format Comment window that pops up, click on the 'Colours and Lines' tab then click on the down arrow next to Fill Colour to bring up the colours and fill effects. Click on the 'Fill Effects' button at the bottom.

6. In the fill effects window which pops up, you will have a 'Picture' tab which allows you to browse and select a picture from your computer.

7. Finally, you may need to close the Fill Effects window and go back to the Format Comment window to set the size and aspect ratio of the pop-up pic. You can also add some text if you like too.

Once you have done the above, a small red triangle will appear in the corner of the cell to show that a pic is available. Very neat!

And that's it. Sounds complicated but it's really not.

Thanks for that Accumulator. Have been using Hyperlinks up to now and am now busy adding comments

[Accumulator 52]

Thanks for that Accumulator. Have been using Hyperlinks up to now and am now busy adding comments

Glad it works for you! I find it a really neat solution. I have sized all my comments to 12cm x 12cm which gives me an image large enough for most purposes. So far I have over 100 coin images and, although it takes a little longer to load the Excel file, the pics are held in RAM and appear immediately without any problems. I will probably split my collection over several files eventually, jut so that file sizes don't get too unwieldy.

[Peckris 579]

If you can post the (for dummies) version accumulator

Actually it's really easy, just very well hidden for some reason.

This is what you do:

1. Choose the cell that you want to activate the coin pic. when you mouse-over (if you want separate pics for OBV & REV just use two cells, one for each)

2. Right click on that cell then choose 'insert comment' from the drop down menu

3.The text box which pops up may contain some dummy text which you can just delete.

4. Place your mouse pointer over the BORDER of the box, right click and choose 'Format Comment' from the drop down menu. This is the important bit, because if you right click over the centre of the box you won't get the option to add a pic later!

5. In the Format Comment window that pops up, click on the 'Colours and Lines' tab then click on the down arrow next to Fill Colour to bring up the colours and fill effects. Click on the 'Fill Effects' button at the bottom.

6. In the fill effects window which pops up, you will have a 'Picture' tab which allows you to browse and select a picture from your computer.

7. Finally, you may need to close the Fill Effects window and go back to the Format Comment window to set the size and aspect ratio of the pop-up pic. You can also add some text if you like too.

Once you have done the above, a small red triangle will appear in the corner of the cell to show that a pic is available. Very neat!

And that's it. Sounds complicated but it's really not.

Thanks for that Accumulator. Have been using Hyperlinks up to now and am now busy adding comments

The problems there are :

1. You have to physically add each and every picture to your spreadsheet

2. You will be physically duplicating all those pictures

A related database table avoids that - the picture you see in your main table is not "there", it's "virtually there".

[Rob 3,002]

The mouse over effect sounds neat, but is of no immediate use to me as I would normally go to half screen images with the unknown image on one side and the pre-existing stored images appearing on the other as each link is clicked in turn. Another advantage of Excel is the effectively unlimited capacity storage facility to the right hand side of the sheet. If I can identify a coin as an ABC123 but don't have an image, I can record the listing for future reference next to those I have imaged in the hope that one day a reference will tie the unknown listing to a specific illustrated coin.

However, I still see n lines of provenance in a single Excel box preferable to n lines of Access fields when calling up the complete provenance. I can't see any difference between 6 column wide Excel data and 6 column wide Access data in terms of ease of use. My collection details are stored in an Access database, but I find the requirement to fill in all the boxes before you can move on a bit of a pain as I may not have all the info to hand. I prefer Access when it comes to sorting columns because Excel doesn't always sort things correctly if I remember correctly leaving you with an irreversible layout.

Sorry, slightly off topic here.

[Peckris 579]

The mouse over effect sounds neat, but is of no immediate use to me as I would normally go to half screen images with the unknown image on one side and the pre-existing stored images appearing on the other as each link is clicked in turn. Another advantage of Excel is the effectively unlimited capacity storage facility to the right hand side of the sheet. If I can identify a coin as an ABC123 but don't have an image, I can record the listing for future reference next to those I have imaged in the hope that one day a reference will tie the unknown listing to a specific illustrated coin.

However, I still see n lines of provenance in a single Excel box preferable to n lines of Access fields when calling up the complete provenance. I can't see any difference between 6 column wide Excel data and 6 column wide Access data in terms of ease of use. My collection details are stored in an Access database, but I find the requirement to fill in all the boxes before you can move on a bit of a pain as I may not have all the info to hand. I prefer Access when it comes to sorting columns because Excel doesn't always sort things correctly if I remember correctly leaving you with an irreversible layout.

Sorry, slightly off topic here.

Now you've got me COMPLETELY baffled! Database fields only have the validation rules you give them, and the default is usually "Put something in here - but if you don't want to it's ok with me". Like an inflatable d... No no! NOT going there!!

[Rob 3,002]

The mouse over effect sounds neat, but is of no immediate use to me as I would normally go to half screen images with the unknown image on one side and the pre-existing stored images appearing on the other as each link is clicked in turn. Another advantage of Excel is the effectively unlimited capacity storage facility to the right hand side of the sheet. If I can identify a coin as an ABC123 but don't have an image, I can record the listing for future reference next to those I have imaged in the hope that one day a reference will tie the unknown listing to a specific illustrated coin.

However, I still see n lines of provenance in a single Excel box preferable to n lines of Access fields when calling up the complete provenance. I can't see any difference between 6 column wide Excel data and 6 column wide Access data in terms of ease of use. My collection details are stored in an Access database, but I find the requirement to fill in all the boxes before you can move on a bit of a pain as I may not have all the info to hand. I prefer Access when it comes to sorting columns because Excel doesn't always sort things correctly if I remember correctly leaving you with an irreversible layout.

Sorry, slightly off topic here.

Now you've got me COMPLETELY baffled! Database fields only have the validation rules you give them, and the default is usually "Put something in here - but if you don't want to it's ok with me". Like an inflatable d... No no! NOT going there!!

I didn't set up the access database for my collection, my offspring did. Unfortunately he's no longer at home, so I live with what I've got. He said it was something to do with sorting the data, but as I don't understand it anyway because my computer programming skills are non-existent, I just do as I was told.

[davidrj 194]

I find I can put all the info I need into the file desciption of my photos - I can view folder containing them in large icon mode

This is folder D:\coins\france\1795-1958 old franc\F126 - F143 10 centimes

[davidrj 194]

moving the vertical bar allows me to see a good image of coin of interest