Moving from Excel to Access

[austinres]

Hi all,

I am very new to the Access world, and I am hoping to get some help here in how to get started. I have a shared spreadsheet that is saved on our company's server. This spreadsheet houses large amounts of data for individuals. Multiple users (4 to 10) access this spreadsheet simultaneously; they read through the information for each person, then input 2 fields:

(1) Y or N
(2) 1-2 sentences of notes

This spreadsheet has become too large that it has lost functionality within excel. It has become unbearably slow to scroll, sort, or filter, and when they try to save the spreadsheet with their input, it regularly locks them out even though it's shared.

My understanding is that Access is the preferable platform for a spreadsheet of this size (~200 MB). I have never used Access, and right now, it is not intuitive to me.

The basic needs we have are the ability to:

1. filter fields to only show specific data
2. sort fields so that all data is shown in a specific order
3. multiple users to access the same data
4. multiple users to save their input to a central location (meaning that when I open this file, all of the users' input is shown in one place)
5. The ability for me to add new individuals and their data to the file regularly

The excel spreadsheet is set up in this way:

Checker_Decision_Notes_PersonID_FirstName_LastName _Deadline_Notes

Checker: the user's initials are here
Decision: Y/N
Notes: 1-2 sentences


PersonID: the individuals' Person ID - this is a unique identifier of each row
The remaining fields are all information on the Individual (Person ID)

Does this seem like a possible transition to Access? Could Access meet these needs without functioning as slow as excel?

Thank you for any help you can provide.

[jzwp11]

Welcome to the forum!

My understanding is that Access is the preferable platform for a spreadsheet of this size (~200 MB).

Access is not a glorified spreadsheet software. If you were to just import your current spreadsheet as is into Access, you gain nothing. Access is completely different and requires a completely different thought process compared to Excel.

Access is a relational database; in relational databases you essentially put packets of similar data in one location (a table) and relate it to other data. For example, you might have a table that holds all of your basic customer information. You might have another table that holds all of the products you sell. And finally, you would have a third table that holds the products purchased by each customer.

The structure of your tables and how they relate to one another is governed by the rules of normalization. See this site for a basic overview of normalization.

As the database designer, it is your job to analyze your data, put it in packets and then determine the relationship between those packets.

So, in order to help you further, you first need to read up on normalization. Next you will need to provide us with more detail on the specific type of data you are working with and how you use that data in your business.

[rpeare]

What you're asking is exactly what MS Access is used for. You have some very, very basic requirements that, as far as I can see only requires 2 or 3 tables.

Table 1 would be your People Table which would include all the PERSON specifiic information

PersonID
FirstName
LastName

I do not know what you are attempting to track but the two fields you say are related to the person are

Deadline
Notes

These are not really person specific. A person may have multiple deadlines and notes associated with them that may also be for a specific project.

You should read some notes about normalization, here's a start:

http://databases.about.com/od/specif...malization.htm

These two items could comprise a second or table depending on what you are actually doing with them. If a person can only have one deadline at a time and only 1 set of non-cumulative notes and you don't care about keeping a history then they can stay on the person table, otherwise you need to reconsider your structure.

Table 2

Checker
Decision
Notes
PersonID

This really isn't much information, are you carrying a decision date? do you want to?

Normally I would suggest you also keep either a checker table or combine the checkers and the people on the same table (depending on whether or not you want to know their first/last name, since people can have the same initials over time) and just have a checker yes/no flag on that table so you can easily separate the data.

You don't need to carry all the person information on this table, you can look it up using queries on forms and reports.

[alansidman]

This link is also a good read on the differences:

http://forums.aspfree.com/microsoft-...el-349267.html

Alan