Fog Creek Software
Discussion Board




Migrating from Excel to Access

Hi,

This is the scenario: This company runs about 50 boats/ships.  Each boat has equipment, which is leased to it. This equipment has different calendar times of expiry. The chief engineer of the ship is responsible for updating the information (make, serial no, expiry date, remarks) about the hired equipment. The updated list (currently in excel) is copied to a floppy and given to the main office, where the main spreadsheet (this has information about all the ship and current status of equipment). It is then manually updated in the main spreadsheet. This spreadsheet is going out of proportion.

What I am supposed to do are:

1. Convert the main excels spreadsheet to Access. (I tried the get external data... to import the excel sheet to access, but the spreadsheet is too complicated). At the same time, I have to keep 1 worksheet, which gives information at a glance about up coming expiry dates of equipments from all ships.

2. I have to make sure the chief engineers of the ship (they are not that computer literate), use the same excel sheet they have been using. When they give this spreadsheet to the main office I have to come up with an automatic way of updating the access database.

Can access and excel share a common database? Is there any way to do this?

Any links, info, help would be appreciated. Thanks for reading.

Prakash S
Wednesday, May 29, 2002

Hi,

Yes,
But you can also end-up with an Access database that will also "grow out of proportion" if proper planning is not done...

I suggest you read any book on Access (and Excel...) to understand the possibilities, and the limitation of these tools.

Good luck,

Al
Wednesday, May 29, 2002

1st step idenitfy the objects (tables)  you seem to have started on this;

As I see it you have

craft (ship/boat)
equipmenttype
equipment assignment

These are your tables.  Now create a query that looks as similar to your excel spreadsheet as possible.  THis, and not your original tables, is what you want to connect to from excell.

DO this, and leave the manual step in for now.  Help the head office get their bookking in shape.  You could probably port from the existing spreadsheets to the access DB, but you'll find that you'll probably be able to simplify the spreadsheets quickly.  You may even find that you want to crete a small access app that the captains use, not the spread sheet.


Email me a sample spreadsheet and I can probably give you more info.

adam
Wednesday, May 29, 2002

You can find alot of info on these two sites:

http://www.experts-exchange.com/
http://www.mvps.org/access/index.html

Al
Wednesday, May 29, 2002

Why bother with excel at all? You need a relational database with constraints, though its a simple one, and a little VB app for entering reports.

Eric Debois
Wednesday, May 29, 2002

Prakash clearly stated that the chief engineers are not very computer-literate and prefer to use the original spreadsheet.

Marat Zborovskiy
Wednesday, May 29, 2002

I'm surprised nobody here has mentioned using Filemaker. It should do the excel->database stuff pretty easily. The newer versions also have web capabilities.

-james

James Wann
Wednesday, May 29, 2002

You can use ADO recordsets in Excel macros, and I think you can use Excel as an ODBC record source.    So it shouldn't be too hard to exchange data between the two applications using VBA Macros.

anon
Wednesday, May 29, 2002

(sarcasm follows)

Use Linux and MySQL and OpenOffice and Tomcat. 
Die $atan. 

Make sure you budget for about 3 months development time, $200k development fees, and $40k annual maintenance fees, but other than that, this is the best solution

Bella
Wednesday, May 29, 2002

Before you try to do anything with Access make sure you understand the basics of the relational database model. It should take you about a day to figure out and then everything will make complete sense :)

The relational model is the biggest difference between Access and Excel.

That said -- Excel has pretty decent multi-user and auditing capabilities, so maybe you're better off sticking with Excel and just using more of its advanced features rather than leaping into something new.

Joel Spolsky
Wednesday, May 29, 2002

Depending on how big this gets Access may not be the solution. MySQL could be it, but then you have to read some howtos. Anyway, I just used a perl module to parse excel spreadsheets into the way I wanted. Spreadsheet::ParseExcel

Hello
Wednesday, May 29, 2002

Before thinking about how to do it in Access, Excel or anything else you need to analyse the spreadsheet.

What principle areas of the spreadsheet are there?

What formulae does it contain?

What macros does it contain?

Of the principle areas you will probably have two types of areas:

Rows of data that are all the same.  This will relate to an access table.

Blocks of formulae that display results based on the rows of data.  These will probably relate to the underlying queries that your forms and reports will be based upon.

Things become more complex when the rows of data contain formulae.

If these formulae refer to set fields outside your rows (usually fixed using $s) then these are no problem.  These will be calculated fields in your table based upon constants.

If these formulae are relative, refering to other rows then you need to do more analysis.  What you do with them depends upon what they are doing.  For example, if they are keeping a running total, then this will have to be removed and placed into one of the reports.

If you want, contact me direct.  You can do this be clicking on my name below.

Ged Byrne
Thursday, May 30, 2002

For all questions Access-related, try the comp.databases.ms-access newsgroup.

Martha
Thursday, May 30, 2002

"Check out www.itp-life.com for a start."

It has "The End of Sex, Golf, and The Ultimate Athlete" as keywords ...
maybe I'll get that ATL programming book afterall...

Al
Thursday, May 30, 2002

Oups!!! wrong topic!

Al
Thursday, May 30, 2002

Excel File Format

http://chicago.sourceforge.net/devel/docs/excel/

In case you have to get down to "bare metal" and write an Excel to X translator.

Link Fairy
Friday, May 31, 2002

thanks ALL.

Prakash S
Tuesday, June 04, 2002

Hi all experts

am interested to know if you had a solution to Prakash's excel to access problem already?

I am encountering a similar problem. I have designed an access database with header and detail records for my publications. each publication have header details like dimensions, type of cover, etc. each publication can have more than 1 detail record since there could be more than 1 author.

For easy entry into excel, each row in excel will have header and detail record. Ie. if there are more than 1 detail record, the header information will be repeated on the next row.

I notice the export is only possible if only 1 database table is in question. How is it possible to export these data into access database header and detail tables?

thanking all in advance

tanky
Sunday, March 30, 2003

*  Recent Topics

*  Fog Creek Home