Big Job - Need Help

[Mart_Jos]

Hi All,

Im new here, i have not used Microsoft Access since i was in college 14 years ago and have forgoten most of it.

I have an excel sheet which the company i work for uses to track serial no records and sales, warranty details etc, it is get a bit extreme now. It currently has about 700 sheets and each sheet having about 2000 row or more in each with multiple formula's and is a getting to be almost 100mb and becoming extremely slow.

I am looking to see if it would a good idea to create a access databse and how much of a job it would be.

Current Excel Databse Example -



Here is a breif description and example of the excel workbook we currently use.

We have a Main Index Sheet which shows all the Mfr's that we purchase items from. Each name has a hyperlink which directs to an individual index for that specific supplier. Example the Metcalfe Logo directs to the Metcalfe Index Sheet and displays a full list of each product type and model supplied by Metcalfe.
Each model name then has a hyperlink which directs to a Serail No. Sheet i.e. (NS220 Serial No. Sheet). This sheet contains details on every single machine of this particular model that comes in and out of our business. Each product then has a sheet which details information on it i.e Mfr, Mfr Model, Voltage etc and external hyperlink to parts diagrams and operation manuals. Finally there is a note sheet just for joting down information.

Hope this explains the workbook out enough.

Main Index Sheet



Metcalfe Index Sheet



NS220 Serial No. Sheet



NS220 Product Information Sheet



NS220 Note's Sheet



So im looking to turn this excel workbook into a working Access database where users can login in to view the information and search, but only myself and one other can edit and add information to it.

If anyone could comment on how much work and how hard this is going to be (A LOT I ASSUME) that would be much appriciated

Also i am looking at to set this up myself NOT pay someone to do this for me, i want to learn/remember how to do this.

I have set up the basic tables, i just help putting it all together.

Thanks to you all in advance

MartJos

[RuralGuy]

"how much work and how hard this is going to be" is really up to you and your skill level. I see nothing that Access cannot do and we'll all be here to assist if you need us.

[June7]

As RuralGuy says, all depends.

Our office spent over 3 years converting a 20-year old dBaseIV app to Access using internal staff. I inherited the db 50% completed and had a contracted one-on-one tutor I could call on for a few months. I learned as I developed the db. I have since built numerous very small databases, none took anywhere near that much time, at most a few weeks.

If this is a multi-user database and users need to use simultaneously, better split the db. Each user runs their own copy of the frontend.

When you encounter specific issue post a question.

[Mart_Jos]

Hi Both,

Thanks for your comments, can i ask am i correct in saying that a table i access is the same as one sheet in an excel workbook?

[RuralGuy]

That is a reasonable description though there is no real good equate.

[orange]

Here is a link to database planning and design info that may help with preparing and setting up a database.

As RuralGuy said, yes your statement that "a table in access is the same as one sheet in an excel workbook" is reasonable. But it is not a rule, nor guiding principle. Sheets(excel) tend to be wide and short; tables(access) tend to be narrow and long. Tables in access contain info about 1 subject/entity. Tables are related to one another based on facts within your business (eg A Customer may have 1 one many Orders..)

I recommend you review the planning and design info and then draft a data model of the business. Don't be too quick to jump into physical Access. Make a model, test it with some sample data. Make sure the model supports the business requirements. Adjust as necessary and when there is agreement on the model meeting the requirements-- you have a blueprint for your database structure. The next big step will be how to move data from Excel to Access.

Good luck with your project.

[Mart_Jos]

Here is a link to database planning and design info that may help with preparing and setting up a database.

As RuralGuy said, yes your statement that "a table in access is the same as one sheet in an excel workbook" is reasonable. But it is not a rule, nor guiding principle. Sheets(excel) tend to be wide and short; tables(access) tend to be narrow and long. Tables in access contain info about 1 subject/entity. Tables are related to one another based on facts within your business (eg A Customer may have 1 one many Orders..)

I recommend you review the planning and design info and then draft a data model of the business. Don't be too quick to jump into physical Access. Make a model, test it with some sample data. Make sure the model supports the business requirements. Adjust as necessary and when there is agreement on the model meeting the requirements-- you have a blueprint for your database structure. The next big step will be how to move data from Excel to Access.

Good luck with your project.

Hi,

Thanks for this, i will look at it tonight.

Yes my idea is to create a sample db first before moving on to the actual one, as there is a lot of information to transfer over.

I will update this as i go along

Thanks

[rpeare]

This looks like a fun project....