Building a new database built on a spreadsheet design structure...

[jworden1]

Good evening,

I was hoping that someone could help me with regards to a database design. I run a market research company and currently use very large spreadsheets to store my data. I use one workbook per client, made up of several worksheets.

It has been put to me though that what I am actually creating is a database and that rather than using Excel I would have a greater benefit using Access, particularly when it comes to creating reports.

Below is a list of the different worksheets I use per workbook. I wonder if someone would be kind enough to help me build a relational database to store this information.

WORKSHEET1: EXISTING CUSTOMERS
Name
Contact Details
Location
Company Profile
Description of Sales in Progress
Sales Value
Status of Sales
Interview Data

WORKSHEET2: HISTORIC CUSTOMERS
Name
Contact Details
Company Profile

WORKSHEET3: POTENTIAL CUSTOMERS
Name
Contact Details
Company Profile
Interview Data

WORKSHEET4: MANUFACTURERS
Name
Contact Details
Company Profile
Interview Data

WORKSHEET5: INTERMEDARIES
Name
Contact Details
Company Profile
Interview Data

WORKSHEET6: COMPETITORS
Name
Contact Details
Company Profile
Interview Data

As you can probably appreciate there is a lot of information held within each of the spreadsheets, a lot of the worksheets hold similar type data and by opening up a workbook on a particular client I can look at their customers, competitors, intermediaries, etc.

As a fairly novice user can someone direct me to how I could create a database that holds data about all my clients, so that I may open my database, choose a client and then view their customers, competitors, intermediaries, etc. So in essence I can do the same as I do now, it’s just that all the information will be in the same place.

If anyone could set me off, perhaps with the aid of a simple diagram I would be most appreciative. Note that I do not want to move the actual data from Excel to Access, I just want to start afresh so in truth I'll only be moving the structure (limited as it is) across. I don't know if this is relevant but thought it was worth mentioning.

Kind regards

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BNPMike]

Step 1 is to import all your spreadsheets into Access. As long as your data is in First Normal Form which it probably is, you'll be able to do some useful probing around just using queries, and therefore from that create some useful reports. Your data doesn't look 'normalised' in the normal sense eg competitor contacts should be separate from competitor names. You can however get around to sorting that out later. Think of Access as an SQL Database rather than a relational database. You should find SQL/Query-by-Example easier for solving your questions than Excel.

 

[SkipVought]

Hi,

"I use one workbook per client..."

Your workbook design reflects the typical mistake that many spreadsheet users make regarding the storage of data. By segmenting your data by client, for instance, you destroy its usefulness as a historical repository of determining where your COMPANY has been, and a tool for forecasting where your company could be going.

I only point this out in order that you do not replicate these mistakes in this next step, since you stated that you wanted to proceed, "...So in essence I can do the same as I do now."

PHV has referenced a link to Fundamentals of Relational Database Design. There is no, "simple diagram." If you have no background in data organization and database design, I'd STRONGLY suggest that you hire a reputable professional consultant to aid your conversion process.

This is not the job for a novice, handling valuable corporate information.

Skip,

_[red][/red]

 

[c2rich]

Hi
"I use one workbook per client..."

I would have to agree with Skip, in order to merge all of your information and have the information be useful to you there is a lot of relationships that would need to be set up, additionally, since you have separate files for each clients this does not make for an easy import. If the information you wanted to import was in the same workbook than you may have a chance, but since you are saying you are a novice access user than someone with access experience should help. There is, however, an option (a time consuming one) and i would not recommend this if you don't have people to help you and if you are in a hurry, but you could create your database tables and queries and then enter all the information manually.

-Kerry

Those that know, do. Those that understand, teach. -Aristotle