Eporting Excel to a Database Application

[bpeirson]

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.

 

[melone]

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

 

[bpeirson]

Please define "crazy".

 

[melone]

Why don't you define your formulae?

 

[bpeirson]

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"?

 

[melone]

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?

 

[bpeirson]

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.

 

[RGCook]

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!