Using Microsoft Excel as a database

Netaddict

MS Excel is a lot more user friendly and easier to use then Access, but can it be used like a data base? I'm thinking of using it as a check out, check in database for a library. So it will have a list of patrons and a list of books then will be able to record that patron X has book Y. The only issue is how to automatically be able to extract data without the use of the "find" menu item or the filters.

renata · Posted: Thu Mar 17, 2011 5:00 am Post subject: database

I think you'll definitely need a database...

To get the details for person X in Excel, you would need to sort the whole spreadsheet at least once every time somebody borrowed a book (in order to find that borrower). And again if you wanted to find a particular book. You would need to have at least 3 spreadsheets (person, book, loan) - unless you want to duplicate huge amounts of information. You would need to be doing manual work in Excel every time a book was borrowed.

The room for error is great, as you are dealing with the raw data. A database form can be set up to do checking (such as "Are you sure you want to delete this entry?" or "this person already has 356 books out - are you sure?").

In a spreadsheet it would also be hard to do things like finding lists of all the overdue books. Over time, the spreadsheet would grow very big and become unwieldy.

A database could show you only the information you need at the time and allow you to update the data in a carefully thought out way with a simple screen, where all the hard work is done automatically.

Databases allow information to be linked in ways which make the information much more useable. They offer endless options for reporting. But they are harder to learn.

If you really want to go the Excel way, you could use some database techniques, such as:
Give each book a unique code plus a copy number, and have a separate spreadsheet linking that code/copy number to a more detailed book description.

Similiarly, give each borrower a code, and have a separate spreadsheet which provides the borrower details associated with that code.

This means that if you want to add/change details of an individual borrower or book, then you can do it without affecting your main spreadsheet. And also the borrower/book details are not duplicated every time. But it does mean three spreadsheets.

Personally, I think it would definitely be worth trying to use a database!

Auieos · Posted: Thu Mar 17, 2011 4:46 pm Post subject:

I think Renata makes great points.

The setup is hard but Access will be a more suitable program to use, and easier for people who are uncomfortable with computers to run and use the database.

Netaddict · Posted: Fri Mar 18, 2011 1:50 am Post subject:

Thank you renata and Auieos for your insightful replies. I thought I'd use the lookup function to extract data from the Excel sheets but have tried it and it turned out to be slow and error prone.

Looks like I'll have to learn Access..

shaunwood12 · Posted: Wed May 04, 2011 10:24 am Post subject:

yes off course MS Excel is very useful and easy database software. you can build hotel database, clinic database or school database in excel

renata · Posted: Wed May 04, 2011 2:05 pm Post subject:

MS Access is a database, MS Excel is a "spreadsheet". But I guess they can work together.

adrianasimon · Posted: Thu May 12, 2011 1:00 am Post subject:

MS Access and MS excel are different from each other........access is typically for data base.........we cant get the same work spreedsheets like from access for the database

Copyright © 2003-2016. PhotoshopForums.com, iFroggy Network. All Rights Reserved. Powered by phpBB © phpBB Group. phpBB SEO. Privacy Policy. We are in no way affiliated with Adobe. Photoshop, Adobe and related marks are registered trademarks of Adobe.