Is Excel going to be able to handle this or should i learn Access?
Is Excel going to be able to handle this or should i learn Access?
(OP)
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.
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.





RE: Is Excel going to be able to handle this or should i learn Access?
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.
RE: Is Excel going to be able to handle this or should i learn 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?
RE: Is Excel going to be able to handle this or should i learn Access?
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.
RE: Is Excel going to be able to handle this or should i learn Access?
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.
RE: Is Excel going to be able to handle this or should i learn Access?
"...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
RE: Is Excel going to be able to handle this or should i learn Access?
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
RE: Is Excel going to be able to handle this or should i learn Access?
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?
RE: Is Excel going to be able to handle this or should i learn 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.
RE: Is Excel going to be able to handle this or should i learn Access?
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
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
RE: Is Excel going to be able to handle this or should i learn Access?