Best tool for the job?

[davidsmith123]

Hi,



I'm looking for a suggestion on what tool to use. I'm a very comfortable Excel user, but have never used databases before. I've been tasked with creating a dataset that has the following properties:

50,000 company names
100 different company attributes
10 different company locations

So I have a dataset with 50,000,000+ rows (each company needs all 100 attributes at all 10 locations). Obviously this is beyond what excel can handle. So ?s:

1. Is Access the best tool to use for this job? Or should I use some other database program?
2. I'll need to do some calculations (a vlookup against an excel spreadsheet) to create some of the company attributes data, this shouldn't be an issue, right?
3. If Access is the way to go, any suggestions on best sites I can use to get up and running and working within Access?

At this point, I'm looking for the most straightforward path for me to create this data, as it will probably end up in a SQL database, but for now is Access the best tool? Or should I learn something more powerful to create it? MS SQL Server perhaps?


Thanks a lot!

David

[June7]

Access or any relational db is better than Excel for this. Beyond that, personal preference and learning curve and budget will have to guide.

I suggest you get an introductory tutorial book on Access that will provide a basic explanation of relational db concepts as well as Access functionality. Spend a solid week studying.

[CJ_London]

1. Is Access the best tool to use for this job? Or should I use some other database program?

your data volumes are quite small for access and certainly for the enterprise scale rdbms's so should be fine

2. I'll need to do some calculations (a vlookup against an excel spreadsheet) to create some of the company attributes data, this shouldn't be an issue, right?

no - you would use a query but there is a function called dlookup which is similar to vlookup, but is significantly slower than a query

3. If Access is the way to go, any suggestions on best sites I can use to get up and running and working within Access?

as June says, get a book, or plenty of 'how to's on the web. Concentrate on understanding normalisation, it's concepts are a long way from Excel and normalisation applies to all databases, not just access.

be aware that access comes in two parts, a front end development environment and a backend to get you going. In reality the front end can be connected to pretty much any backend rdbms although 90% of the time the access backend meets most requirements.

backend - where the data is stored in tables
front end - forms, reports and code to enable the user to interact with the data. Think of the front end as being a bit like a web site but client based run in the access environment rather than a browser environment

differences from excel
access stores data in tables that are 'tall and narrow', excel stores data 'short and wide'
access uses forms and reports for presentation, excel stores and presents in the same way

Also be aware that rdbms's are just that, the back end. They do not provide a means for a user to directly interact with the data. For that you need to use access or develop something that will run in a browser. Rule of thumb is anything developed in access will take 10 time longer to develop for a browser - hence June's comment about learning curves and budgets

[ssanfu]

Here are a few starting places that /should will help you:
One Rogers Access Library. Take the time to work through each of the tutorials (30 - 45 minutes each)
Another is Access Basics by Crystal.
For info about Normalization, see What Is Normalization, Part I

[orange]

There are several related/relevant articles here re Database Planning and Design and more.
Good luck with your project.

[davidsmith123]

Thank you all! I'll definitely check out some of these resources as I begin my project.