Microsoft Office Access 2003

[SionGilbey 1]

I'm thinking of converting my huge OpenOffice Calc (essentially Excel - it saves as an excel file and opens just the same) database into Access 2003 (more recent Access versions are useless!).

The advantages are that I can search for individual coins, see how many coins of a denomination or year or country I have and I can attach images.

However, I'd have to run both alongside each other as Excel is my preferred and is easily printed.

Has anyone got any experience with managing a collection on Access?

[azda 979]

I'm thinking of converting my huge OpenOffice Calc (essentially Excel - it saves as an excel file and opens just the same) database into Access 2003 (more recent Access versions are useless!).

The advantages are that I can search for individual coins, see how many coins of a denomination or year or country I have and I can attach images.

However, I'd have to run both alongside each other as Excel is my preferred and is easily printed.

Has anyone got any experience with managing a collection on Access?

ColinG is the man for Access, he actually created an excellent database for his collection and sent me a blank to play with, unfortunately my knowledge of Access is zero and struggled trying to work with it. Colin uploaded a screenshot of his database in coin aquisition of the week a few months back, so take a scroll through some of the pages and see if you can find it, it look really good but it lost me unfortunately

Edited January 23, 2011 by azda

[Peckris 579]

I'm thinking of converting my huge OpenOffice Calc (essentially Excel - it saves as an excel file and opens just the same) database into Access 2003 (more recent Access versions are useless!).

The advantages are that I can search for individual coins, see how many coins of a denomination or year or country I have and I can attach images.

However, I'd have to run both alongside each other as Excel is my preferred and is easily printed.

Has anyone got any experience with managing a collection on Access?

I've got experience of Filemaker Pro, which is a similar type of app.

The first and best advice I can give you is this : PLAN YOUR DATABASE in advance! This means :

1. Work out how many different tables (files) you need : for example, now I don't deal anymore, I use three - one for the coins themselves, another for the pictures, and a third for all the Spink prices in various grades going back years (you may not want or need all those)

2. Work out the relationship(s) between the different tables : for example I have a unique Coin ID that links to its picture and its list of prices; that ID is defined as a field in all tables

Setting up and defining your database :

3. In your main table (which will be the coins themselves) define a master layout with all fields you want : DENOMINATION / DATE / VARIETY / DATE PURCHASED / AMOUNT PAID / CURRENT VALUE / CONDITION / WHERE PURCHASED / COMMENTS will probably be a minimum, but don't worry : database managers like Access allow you to add, delete, or change field definitions over time. So too with layouts; you can add more as you need them. I have about twenty!

4. Once you have tables and relationships and fields all defined, you're ready to go! You will need to input your data, though if you already have Excel it will comparatively easy - Access will have an Import dialogue where you define the Excel spreadsheet you want to import and how you want to map Excel fields to Access fields. Then Access will do it for you.

That's a start. It sounds daunting, but if you've worked a lot with spreadsheets, it isn't really. Think of a database as a spreadsheet made of putty that can be pummelled and shaped into the configurations you want. Good luck!

[Peter 615]

Gypsies...I can't handle windows 7 or facebook...retire me or I will stick to an excell spreadsheet.

[Peter 615]

BTW facebook is causing a few issues and is worse than friends reunited....phone or polite letter from now on...I met my wife at 24 but before I wasn't as pure as driven snow...sometimes memories should be just that.Plus her EX'S after their failed relationships try to hit on her....what do you do?...coins for me now and trying to keep Mrs Peter happy.... I love her thruppeny bits and hope she likes my nice hammered.

[Colin G. 147]

I'm thinking of converting my huge OpenOffice Calc (essentially Excel - it saves as an excel file and opens just the same) database into Access 2003 (more recent Access versions are useless!).

The advantages are that I can search for individual coins, see how many coins of a denomination or year or country I have and I can attach images.

However, I'd have to run both alongside each other as Excel is my preferred and is easily printed.

Has anyone got any experience with managing a collection on Access?

As Azda has said I use Acess for my collection, because it suits my needs, and is a programme I used wuite a bit a work. Agree wholheartedly about the change from 2003, mine is on 2002 version

It has been set up around farthings and my methods of numbering etc, but could be easily adapted. One thing to watch out for is the image files, they can soon tip access over the edge, so I store them as linked files, but get them to display in the forms. If you want a blank copy I can try and sort it out. It is not the finished article yet, like a lot of my DIY projects

[SionGilbey 1]

I loaded it up as a linked table, copy and pasted it.

It works a treat!

With some simple button presses I can now view a denomination and instantly find missing years.

I can find each country's coinage if I have it in my database!

Highly recommended to all.

[Peckris 579]

I loaded it up as a linked table, copy and pasted it.

It works a treat!

With some simple button presses I can now view a denomination and instantly find missing years.

I can find each country's coinage if I have it in my database!

Highly recommended to all.

Sorry, was that a reply to me Sion, or haven't you found time yet to read through my War & Peace of advice?

[SionGilbey 1]

I loaded it up as a linked table, copy and pasted it.

It works a treat!

With some simple button presses I can now view a denomination and instantly find missing years.

I can find each country's coinage if I have it in my database!

Highly recommended to all.

Sorry, was that a reply to me Sion, or haven't you found time yet to read through my War & Peace of advice?

Yes, I did use your advice and it was very useful. I have a nice layout already very similar to yours (although a bit extra because of World coins).

I originally imported the data by loading it up as a linked table which is the easiest method but then decided I really liked Access, and unlinked it by copy and pasting the linked table to make a linked table (which is a shortcut) and a copy of the linked table which is an actual access table.

I do have a lot of experience with both Excel and Access but I was wondering if one was better for coin specific needs and which one you lot used.

I also have a coin ID which I also use instead of tickets with information on as I have 3 memory sticks loaded individually with my coin database in little pouches in the front of my folders (I'm a bit forgetful so if I broke my computer I would have a backup but I couldn't trust myself just to have one backup so I had 3!).

I was also thinking about buying a barcode scanner and printer (relatively cheap these days, also compatible with database programs such as Access and Excel) and using the USB barcode scanner and barcodes as digital tickets so one beep could find the info on one coin... a project for a wet weekend perhaps!

[Peckris 579]

I loaded it up as a linked table, copy and pasted it.

It works a treat!

With some simple button presses I can now view a denomination and instantly find missing years.

I can find each country's coinage if I have it in my database!

Highly recommended to all.

Sorry, was that a reply to me Sion, or haven't you found time yet to read through my War & Peace of advice?

Yes, I did use your advice and it was very useful. I have a nice layout already very similar to yours (although a bit extra because of World coins).

I originally imported the data by loading it up as a linked table which is the easiest method but then decided I really liked Access, and unlinked it by copy and pasting the linked table to make a linked table (which is a shortcut) and a copy of the linked table which is an actual access table.

I do have a lot of experience with both Excel and Access but I was wondering if one was better for coin specific needs and which one you lot used.

I also have a coin ID which I also use instead of tickets with information on as I have 3 memory sticks loaded individually with my coin database in little pouches in the front of my folders (I'm a bit forgetful so if I broke my computer I would have a backup but I couldn't trust myself just to have one backup so I had 3!).

I was also thinking about buying a barcode scanner and printer (relatively cheap these days, also compatible with database programs such as Access and Excel) and using the USB barcode scanner and barcodes as digital tickets so one beep could find the info on one coin... a project for a wet weekend perhaps!

I think you've already got to the answer - a database manager like Access is much more expandable and flexible than a spreadsheet, as you can treat each main table as an 'app' (with maybe scripts or 'mini apps' within it); however in the future you could then come up with a need for another app that requires creating a whole new table (such as my Seaby values table) that links relationally to tables/apps you already have.

Good luck with the barcode project - that's one technology too far for me!

[SionGilbey 1]

Actually Peck the tech is pretty simple... the barcode scanner reads the barcode and inputs it as text, like a keyboard.

So if it beeps over a coin ID barcode with 0078 embedded in the barcode it would just input 0078 as if you had typed it.

It saves around 10 seconds but as I'm replacing my handwritten tickets anyway (ghastly unreadable writing!) I thought I may as well go for this.

The scanner is in the post to me now.

The main hassle is printing out each barcode!

[azda 979]

I'm flippin useless with Access, i must get my head around this thread and try and work a little with Colins original table he sent me, so go easy if i ask many many questions in the coming weeks (time allowing of course)

Maybe someone (Sion, Colin, Peckris) could start me off on a beginners course (for dummies). Where do i start making this table lol

I suppose i'd better find out which Access i have first eh

Edited January 28, 2011 by azda

[Peckris 579]

I'm flippin useless with Access, i must get my head around this thread and try and work a little with Colins original table he sent me, so go easy if i ask many many questions in the coming weeks (time allowing of course)

Maybe someone (Sion, Colin, Peckris) could start me off on a beginners course (for dummies). Where do i start making this table lol

I suppose i'd better find out which Access i have first eh

Ok, this isn't a "table" in the sense of a spreadsheet "worksheet" or a Word "table". When you think of a database table, it's really another word for a file, i.e. a collection of records with data arranged in "fields", like a spreadsheet. Unlike a spreadsheet, you can arrange the fields wherever you want on the page, and format each field differently from each other; you're not limited to rows and columns.

Think of it this way :

In a spreadsheet, each column is one field, and each row is one record. That's why it's quite easy to import a spreadsheet to a database.

A database where you have defined all the fields ("column names") is an empty file waiting to be filled. As soon as you add one record ("row") you have your table (file), and can add as many records as you want.

When you're working with Access - if it's a new file (File menu), the first thing you will be asked to do is define the fields you want (and whether each is to be text, or numeric, or a date, or a picture, or a calculation, or a function, etc), and you can always add to these as you go along. Once you have a basic set of fields defined, the next thing you need to do is New Record or Add Record (File Menu again? not sure with Access, but have a look in the menus). That will then let you input data into the relevant fields.

It's like a prettier spreadsheet really - with those you define what each column is to be, and what formatting, rules, etc will be applied, then you start entering data into each row. A database is no different, it works on the same principle.

[SionGilbey 1]

I've set up all the software for my barcode scanner now.

The next big step - fitting barcodes to each coin...

I think I'm going to have to do it in short bursts, say every now and again doing a few pages of coins in an album then waiting another month or so to do it again.

How do you think I should get it in though? Stick it to coin tickets?

They can't be tiny or else the scanner wont read them.