transfering from excel to access

[algram]

Hi bytes experts:

My boss just told me I have to transfer a huge Budget estimation tool (done in excel) to access in order for it to be more customer friendly. Unfortunately I am not even sure where to beginn, seeing as the excel document has 24 pages (tabs) with interlapping calculations and am not that familiar with access. I am using microsoft office 2003.

thank you for your time and help and please contact me at <address removed> if you have any suggestions

[Stewart Ross]

Hi algram. I have removed your e-mail address for your own protection; our site rules do not allow the open publication of e-mail addresses as it can lead to all sorts of messages arriving in your in-tray - and the deluge of spam is bad enough as is.

It is possible to import Excel worksheets directly into Access, or better still to use the File, Get External Data and Link Tables options to link the worksheets to Access instead of importing them. Linked tables can be queried and updated just as other Access tables can, with the advantage if you go this route that you retain the Excel workbook structure intact.

However, with a 24-page workbook you may well find that the data has anomalies that make it difficult to transfer to Access without resolving these first. This assumes that the 24 worksheets are tabular in structure, and that their content and design mirrors a relational database approach of developing a set of normalised tables.

Excel is not a relational database, so it has no tools for enforcing relationships between tables (or even encouraging their use). It would be somewhat surprising if the Excel data concerned was truly related (in the relational database sense) or normalised.

Once you have the data in Access you then have to build a set of queries linking the tables together, a set of forms to provide a user interface, and a set of reports to provide published results.

If your boss thinks that this should be done because it will be easier for users I would doubt that he or she has a realistic view of the scale of the undertaking, and its complexities, but it can indeed be done if care is taken to ensure that the tables concerned are imported and converted if need be into true relational tables. Trying to use a relational database on non-relational data - where there are repeating groups or merged entities for instance - is at best inefficient and at worst impossible.

-Stewart

[NeoPa]

Hi bytes experts:

My boss just told me I have to transfer a huge Budget estimation tool (done in excel) to access in order for it to be more customer friendly. Unfortunately I am not even sure where to beginn, seeing as the excel document has 24 pages (tabs) with interlapping calculations and am not that familiar with access. I am using microsoft office 2003.

thank you for your time and help and please contact me at <address removed> if you have any suggestions

Does your boss drool, or are they a normal looking idiot?

I expect this request came along with no Access training just for added humour content.

[algram]

well i have 4 months to train myself and complete this, where should i start? any book suggestions? i have some basic access experience, thanx

[NeoPa]

I wouldn't even consider a job of this size and complexity for anyone unless they were already quite experienced.

If you intend to go ahead with this I'd look to get some professional training in first. If you hadn't already guessed from what's already been said, this is no trivial task.

I Googled access programming tutorial and found a bunch of useful results. I suggest you use the same or similar terms and look through what you find for something you feel suits you.

[NeoPa]

On the bright side - You are likely to learn a great deal in the process.

If you do go ahead with this then we too, may be able to be of some help, though we do not encourage you to think we will be doing it for you.

Think of us as someone walking with you who can lend a hand when you stumble, rather than anyone to carry you. It will still be a tough road.