Design process for a beginner (1 Viewer)

[accessnewbie2009]

I’m a complete Access beginner with some rudimentary questions. I have done quite a bit of reading online and some basic tutorials, and am a bit overwhelmed with information. Any pointers to set me off in the right direction would be much appreciated.

What I aim to achieve with Access is, I think, relatively complicated. I understand that concept design is key to creating an effective DB, so I would like to outline my status and goals in the hope of receiving some ideas that will help me avoid obvious mistakes at this early stage.

BACKGROUND: HR DATABASE
My company uses Excel to collect employee data from international subsidiaries. This is mostly qualitative data such as name, work location, job title, language ability, training programs attended, future potential, etc. There is also a little quantitative data, like # of subordinates, years of service. In total there are/will be around 35 fields. We get this from each company separately as we do not have an international intranet or shared server.

My impression is that Access will be difficult to use via e-mail due to the likely file size. So I would like to find a way to consolidate all these Excel tables into Access to enable better searching/filtering/reporting, etc. The data in the Excel sheets would be updated 1/yr, and the new data will need to be synchronized into Access.

Data will be used for tracking purposes. For example, to generate reports on things such as employee responsibilities (e.g. # subordinates; P/L accountability), employee development (i.e. promotion through the ranks), turnover (e.g. who left the company, when and why), employee performance and future potential, etc.

BASIC INITIAL QUESTIONS
1. The data we want going forward is a bit different to what we have collected up to now. I guess we should not import any current data, and should instead design new Excel tables and the Access DB based around what we want for the future. Correct?

2. What problems might I encounter with importing data from multiple Excel files? Would there be additional issues if I want to break the data from one Excel sheet into several different tables in Access? (e.g. have personal details in one table, language skills in another, etc.)

3. The big one: Given the rough outline above, what might be the best way to approach the design/structure of the Access DB?
Any other tips would also be appreciated.

Many thanks.

 

[HiTechCoach]

I’m a complete Access beginner with some rudimentary questions. I have done quite a bit of reading online and some basic tutorials, and am a bit overwhelmed with information. Any pointers to set me off in the right direction would be much appreciated.

What I aim to achieve with Access is, I think, relatively complicated. I understand that concept design is key to creating an effective DB, so I would like to outline my status and goals in the hope of receiving some ideas that will help me avoid obvious mistakes at this early stage.

BACKGROUND: HR DATABASE
My company uses Excel to collect employee data from international subsidiaries. This is mostly qualitative data such as name, work location, job title, language ability, training programs attended, future potential, etc. There is also a little quantitative data, like # of subordinates, years of service. In total there are/will be around 35 fields. We get this from each company separately as we do not have an international intranet or shared server.

My impression is that Access will be difficult to use via e-mail due to the likely file size. So I would like to find a way to consolidate all these Excel tables into Access to enable better searching/filtering/reporting, etc. The data in the Excel sheets would be updated 1/yr, and the new data will need to be synchronized into Access.

Data will be used for tracking purposes. For example, to generate reports on things such as employee responsibilities (e.g. # subordinates; P/L accountability), employee development (i.e. promotion through the ranks), turnover (e.g. who left the company, when and why), employee performance and future potential, etc.

BASIC INITIAL QUESTIONS
1. The data we want going forward is a bit different to what we have collected up to now. I guess we should not import any current data, and should instead design new Excel tables and the Access DB based around what we want for the future. Correct?

2. What problems might I encounter with importing data from multiple Excel files? Would there be additional issues if I want to break the data from one Excel sheet into several different tables in Access? (e.g. have personal details in one table, language skills in another, etc.)

3. The big one: Given the rough outline above, what might be the best way to approach the design/structure of the Access DB?
Any other tips would also be appreciated.

Many thanks.

1. - I would agree.

2. If it were me, I would use one of there options:

a) Create Access applications for everything. I would make a version to run at the remote office that would create export files that can be sent in the main office to be imported.

or
b)If all the site have internet access, then you might be able to use a SQL server to collect the data from the remote sites.

or
c) Another option is to use Remote Access/Terminal Services so that everyone works on the same databases.

3. The best way to approach the design/structure of the any database is based on the details of what is needed and the business processes that need to be handed. The first step is to get as much of the details as possible before you worry anything about the database design. One you have a very good understansing iof excatly what nned to be done, then you can start the databes design.

 

[KenHigg]

I like to start with a use case diagram:

http://en.wikipedia.org/wiki/Use_case_diagram

 

[The_Doc_Man]

Let's put this in the right order.

First and foremost, you need to design everything you can up-front as to what is expected of you. You need to know goals. These goals will drive your subsequent actions. Remember that a DB is defined by its data and the data is defined by the process you are trying to perform. So start from the end point and work backwards to what you have to do to make that happen. If you do it the other way, you are using the database to drive business policy. I'm sure your upper management won't appreciate you taking over their jobs that way.

In this design and discovery process, you need to remember a couple of the "Old Programmer's Rules" that help you make such decisions.

Rule #1: Access doesn't tell you anything you didn't tell it first.
Rule #2: If you can't do it on paper, you can't do it in Access.

#1 has two interpretations. You can tell Access how a report looks by entering the report through the report design wizard or directly in raw design mode. But #1 has another meaning, more subtle and obvious at the same time. If you want to see a count of XYZ widgets in your report, you need a source of data that allows you to count XYZ widgets. If you want to see a date when something was first entered, you need to capture the data when it was first entered, and so on. If you want to see something, you have to know how to get something.

#2 has one and only one meaning: If you don't know enough about your goal to do it on paper with the data presented to you then you don't know enough to begin implelmenting it in Access. You must first understand the problem. You must understand it so well that you almost feel married to it. Then and ONLY then can you work towards the solution.

The only way to know the goals (applies to #1 and #2) is to question the people who wanted this thing. Press them for what they really need to know and how they need to know it. Without a good set of requirements, you are not going to implement what someone wanted. (No, not being a pessimist and not doubting your ability. That's just the way it works.)

When I was in private industry, I always created a "goals" document and got the boss the review it before I starting coding. That "goals" document is your get-out-of-jail-free card if you implement something with as many layers as an onion and the boss says, "But I like parfait." Of course, if you know your movies, you know who says that line, and can avoid the mental image... or not. Just try not to smirk when the boss says that.

Now, as to a design methodology... My personal favorite is to find a big dry erase board. Get multi-colored markers and a case of sticky-note pads. Your business can use the leftovers later.

You will analyze your problem to determine the business entities you are trying to model. (Watch this nomenclature, you might not be used to thinking in this way.) When you model something, you are capturing data essential to your model. Since you are modeling a part of your business, even if only for report generation, you really want the map to match up to the territory before you start drawing that map, right? Well, here is where your goals document becomes a tool.

Before you define even ONE TABLE, read up on database normalization until that concept starts overflowing your brain and spills out on the table. Start with two places that I always recommend: First, the Access Help Files for topic "Normalization" and second, Wikipedia.ORG for "database normalization." From there, use your favorite search engine for "database normalization" and start reading. Only bother with articles from two sources: Colleges or universities you recognize from the .EDU domain, and vendors of databases you recognize from the .COM domain. Read the articles until they all start to sound alike. After two or three articles where you no longer learn anything new, you have learned enough to get started.

Look through your goals. You will identify "entities" that you are modeling in some way. You are abstracting data about those entities down to the level that you need to reach the goals. Draw a header for each entity on your white board. Populate the tables with sticky notes representing records. Draw lines showing how these things are related.

You mentioned that one of your concerns is multiple sites. That makes a "site" an entity to be modeled. You are talking about people who have some supervisory attributes. That makes "people" an entity. (More specifically, the entity is "employees" ... but you get the idea.) One of the issues is the "skills" these people have as reflected in their linguistic ability and training programs. This could go on for a long time, and you have the problem definition in front of you. I only have the synopsis you gave us, so I'll stop here and step to the next part.

Now start defining your entities, shooting for what is called a 3rd-normal form as a minimum. Access can go beyond 3rd-normal forms, but most simple models rarely need to do so. When you figure out the relationships in the business sense, these relationships might or might not translate directly to Access relationships. Hard to predict, but usually there is some way to express the relationship.

When you identify entities, you will have to consider how you identify specific entities. This is the subject of KEYS. You'll have Primary Keys (PK) for each table with a few exceptions, and the exceptions will probably have Foreign Keys (FK) leading back to the PKs of tables that have them. An example: An employee works at site #1. Site #1 has a site number as its PK. The employee has that same site number as an FK. That is a "relationship" from the Access sense of the word.

Once you have started on your model, you will have questions. Don't hesitate to come back to this forum with specifics. But I've given you enough to chew on that you will be busy for a while.

 

[accessnewbie2009]

Thanks for these replies, guys.

The case diagram will be helpful to visualize some aspects.

The_Doc_Man: You're right, the business needs should be clear. I am talking with people about this to make sure the DB meets their needs, not the other way around.

The steps you have outlined are really helpful. I'll work through these and probably be back with more questions once I have made some more progress.

 

[dsigner1]

Everything so far sounds like good advice. let me add a little. You will learn about Access by doing it and inevitably you will change your mind on some things. Don't try and do this in one pass. Identify a mini-database which does a small part of what you want to do and make that work sweetly. Then go back to the full problem and think again.

I would consider looking at your ovcerall setup like this. Consider the Excel purely as dataentry forms which are being sent in to you. You can link each spreadsheet as it comes in to your Access database and use update queries to transfer the data to your Access tables. This is a very easy to understand approach and does not involve getting remote sites to do anything new.

 

[The_Doc_Man]

I second the suggestion of dsigner1 regarding "just import the spreadsheet" and don't ask users to do anything new. Old dogs... new tricks... mismatch!