Is Excel going to be able to handle this or should i learn Access?

[awomack]

I have been given an assignment to create an all encompassing Voltage Drop calculator that will also create a material list and battery calculation for a low voltage system.

The problem i find myself running into is the amount of data i need. it appears i would need a unique file for each combination of different brands of products we use. Then each of those products can vary on volume and brightness which each have different voltage drop values but are selectable, so they are the all same products. The test file i have now works ok but it seems like there is a lot of repetitive information.

any help or suggestions are greatly appreciated.

 

[francesca]

Excel combined with VBA is very powerful. On the other hand, Access in the right hands is very very powerful.

Do you really need different files? Or different sheets in one file? The analogy between Excel and Access is that Excel sheets are Access Tables. Access allows repetitive info to be dropped.

I haven't used later versions of Excel in quite as much anger as you seem to be wanting to, but earlier versions became quite unstable as they got larger. Access, on the other hand, is stable, and has the added bonus that multiple people can have read/write access at the same time, and permissions can be set hiding certain parts of the database from others. For instance, some users may only be able to see Reports, while others might be able to edit the queries that generate reports.

Remember, too, that Excel is limited by how many lines of data you can import, while Access isn't.

 

[Ashereng]

If more than a few people will be using it, I'd go with Access.

I tend think of Excel as my big note book and scribbles - good for my couple of colleagues, but not much more.

"Do not worry about your problems with mathematics, I assure you mine are far greater."
Albert Einstein
Have you read FAQ731-376 to make the best use of Eng-Tips Forums?

 

[cummings54]

Excel is an good tool for flat database applications. These are databases where you save information in rows and columns. For example each column could be a different field and then each row a different record in the database. This type of structure is called a table. Excel workbooks can be shared among several users but after 5 or 6 simultaneous users it is tricky to manage

Access is a better tool when you have a relational database application and multiple users who will be modifying data at the same time. A relational database saves information in multiple tables which can be related to each other. A good indication that a relational database is more appropriate for you is your statement "it seems like there is a lot of repetitive information". You can organize data into tables and minimize repetiion.

 

[awomack]

Thank you everyone for your replies.

The repitition I come across is mostly from the different formats for the same information in different worksheets. on my voltage drop I need certain info, on the material list other info but some is the same for the exact same items. I should probably reformat my sheets to share the same info better.

i started to check out Access. I think i will continue fine tuning my current Excel workbook but really look into making Access work.

If anyone uses Access to draw up material lists I would be very interested in hearing how you set up that format. Especially if you also use the database for a secondary purpose.

Once again thank you for reading and/or responding.

 

[ACtrafficengr]

I've been tempted to order the "Video Professor" CD on Access. Anyone willing to provide a review?

"...students of traffic are beginning to realize the false economy of mechanically controlled traffic, and hand work by trained officers will again prevail." - Wm. Phelps Eno, ca. 1928

"I'm searching for the questions, so my answers will make sense." - Stephen Brust

 

[zdas04]

I used to teach this stuff, and I always stressed the difference between "sources of data" and "uses of data". In over 20 years of doing this stuff I have never found a case where a spreadsheet was the best data source. Every single time I've seen someone try it (hundreds of examples), they reach a point where deleting a record causes useful information to disappear, or they need a report that causes them to duplicate a data set that never again is in sync with the original.

On the other hand, a database (and Access is an excellent example) can enforce a concept called "referential integrity" that lets you put header information in one table, detailed records in other tables, and provide reports that includes both the headers and the details. Then if you delete a widget you don't have to delete the manufacturer. To present the data differently is a simple task that doesn't duplicate any of the underlying data.

Excel is an excellent "use of data". For example I often need a running cumulative, I've never found a way to do this in Access but in Excel it is trivial. When I need a cum I export the data (including the key fields), calculate the cum, then import the data back into Access and throw the Excel file away.

I've used an Access database for bills of materials for many years. I built a "nomenclature" table that has all of the catalog info (e.g., full description, manufacturer, dimensions, weight, etc.) and then I have a "drawing" table that has drawing number, tag number, catalog reference, size and quantity. Joining them together for an individual drawing BOM or a total-project list to give purchasing is trivial.

In this case you are trying to chose beteen a bad tool (for the job) that you know something about and an outstanding tool that is a mystery to you. Take the class and go with Access.

David

 

[bpeirson]

zdas04, I have developed a database in Excel using 4 workbooks with multiple sheets in each book. I have known for quite some time that this would be better implemented in Access due to multiple users and redundant information.

Could you point me to a reliable tutorial on how to migrate information and relationships from Excel to Access.

Are the tutorials/help files included with Access of any great value?

 

[francesca]

Open a new Database in Access.

File --> Get external data --> switch file type to Excel and import your sheets one at a time.

The Access help files are very useful. They'll guide you through user permissions, etc.

 

[zdas04]

It really is that easy.

Once you've created your tables from the Excel, go into the Table Design and assign keys. Keys have to be unique for each row so you need to think hard about this one. In my BOM database (for example) the nomenclature table has a key of "item" (like "Tee", "Valve", etc. since I'm the only user the words work fine, if it was for multiple users I'd make up a code) and an "index" number (e.g., for a tee index #1 is "Straight, API 5L, PEBFW") and then a description field. In the project database the key is Project Number, Drawing Number, and Drawing Item, then there is non-key data elements for "item", "index" and "size". If Project "666" has drawing number "1" and item number "1" is a "Tee", "1", 6 inch, I can print a bill of materials for a 6-inch straight weld-tee with a simple join.

Access really works well, but it does not free you of thinking about your data. It has a feature that will allow it to assign an arbitrary number as the record key. I've been using Access since the original Beta release (and was a big user of IBM's relational SQL/DS database before that) and I've only used that option one time in hundreds of databases. Don't use it until you understand your data REALLY well.

David

David Simpson, PE
MuleShoe Engineering

http://www.muleshoe-eng.com

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips Fora.

The harder I work, the luckier I seem

 

[bpeirson]

Thanks, I am going to start a new thread with new questions after I check the FAQ's.