Access vs. Excel

[jsimard]

Hey there,
Not sure how to proceed with this request. I was asked to create an inventory of all materials available to order within our health department. Fields would include vendor name, vendor code, brand name, item desc, etc. Right now this list has been created in Excel. The end result would have users being able to open the inventory list and drill down to find specific products, see the info they need and proceed with ordering. I guess my question is, with using the filters in Excel would that not be the easiest way to proceed? I was approached to create an access database for users to use. Just looking for opinions about excel vs. access since the inventory list is already in excel. There are approx 16,000 items in the excel spreadsheet. Are there issues with the spreadsheet becoming too big and crashing?
Thanks for any help.

[June7]

The Excel file could get big and slow but that would probably take a long time. 16,000 rows is not much.

What is easiest or best is subjective. If this is only to allow users to find item and not tracking inventory levels or orders, Excel may be adequate. Excel can be programmed to use form object as user interface but if you want that, might as well do it in Access where it's easier.

The Access db might be 3 tables.

tblVendors

tblProducts

tblProductVendor

[orange]

I agree with June7 -- best/easiest etc are all subjective terms. How many potential users would be involved? What will their involvement be?What experience with database do you have/is available to you? Does this info tie-in to another system -- finance etc?
"within the Health department..." seems to imply multi-user (or potential for multi-user) and also that the actual ordering and assets management systems may not have been included in the post.

For more info on database design:
These are quite good for learning by Watching/Listening rather than reading.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

Here are some other links that may be helpful to you.
Database Design Principles http://forums.aspfree.com/attachment...2&d=1201055452
Entity Relationship Diagramming http://www.rogersaccesslibrary.com/T...lationship.zip

Good luck.

[jsimard]

Thanks for the replies. This is strictly a listing with read only access / protected worksheet used to look up details of products. No connection to any other file or system. The file will be located on our internal intranet system and any user will have access.

[June7]

Unless you want to build the Access version as a learning exercise, whether or not you implement, I don't see much benefit compared with the time and effort it would involve. If you are finding issues with inconsistent vendor and product names, that can be dealt with in the Excel with dropdown lists - for new rows but of course won't fix naming issues in existing rows.