×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Eporting Excel to a Database Application

Eporting Excel to a Database Application

Eporting Excel to a Database Application

(OP)
A few years ago I started to create a small data base in excel. Part numbers in column A then specific information in columns B through G.

This worked well with the limited number of parts we dealt with. Now with time the list has grown to over 5800 items.

I feel it is now more efficient to use a true database application to access this information.

Does anyone know of a database application which can easily import data structured for Excel at a reasonable cost (less than $200.00), and give the ability to modify the data depending on external conditions (take the cost from the database and mark up differently based on who is purchasing).

I have limited experience with databases so the obvious answers may not be obvious to me.

RE: Eporting Excel to a Database Application

Access will do it seemlessly, providing you don't have any "crazy" formulae in your Excel sheet.

RE: Eporting Excel to a Database Application

(OP)
Please define "crazy".

RE: Eporting Excel to a Database Application

Why don't you define your formulae?

RE: Eporting Excel to a Database Application

(OP)
Do you mean define the formulae by giving them a function name in excel or do you want me to describe them to you so you can tell me if they are "crazy"?

RE: Eporting Excel to a Database Application

Describe them.   Are you looking is 15 diffent workbooks, for input variables that feed into a huge lookup table that changes based on a seperate input?  OR, are they more like, Column A * interger / integer + integer?

RE: Eporting Excel to a Database Application

(OP)
The best situation would be that I type part numbers and the client name into Access which then gets part information from workbook "A" and client information from workbook "B". Access would then multiply the cost from workbook "A" by the markup from workbook "B".

If we win the job then I would type a PO number into access which would then get information from workbook "C" regarding how to make the items ordered.

The workbooks "A", "B" and "C" do not reference each other in any way. I currently use workbook "D" which references "A", "B" and "C" with vlookup. Workbook "D" contains exteremly complex formulae and macros to manipulate the data from "A", "B" and "C".

Workbooks "A", "B" and "C" contain simple math formulae to fill certain cells.

I would like to replace workbook "D" with Access if it has the ability to perform mathematical functions on data derived from the other workbooks.

RE: Eporting Excel to a Database Application

bpeirson,

As mentioned, Access will easily import your data into a database that has tables representing proper normalization of the system data. You may need to pick up a good book on Access to appreciate this, but in the process of pulling your data into Access, it is best to pull in the entity specific infomation only. In other words, it is generally not good practice to store calculated data as well. Calculation can be performed on the data, as required, in the database. You can then use Access' built in functions to do simple stuff or define you own using fundamental VBA. Of course, Access is very powerful from a programmers standpoint, but much of its power is accessible by regular users as well.

The decision to use a real database is a good start. Now make sure you grab a good book or research the concept of setting up relational tables, and data normalization before you plunge into importing stuff. Once you have the foundation laid, you can then build a nice database.

I hope this is helpful!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources