About Geoff Expertise I specialise in database analysis and design, SQL and database queries using QBE and VBA. In my work, I use MS Access together with MS SQL Server as ETL (Extraction - Transformation - Loading) tools for migrating data between business ERP systems and data stores. My forte is building bespoke functions and applications.
See my
website for example apps and downloads
Experience I am a chartered engineer with 30 years of engineering and business experience, member of the BCS and have been working specifically in database applications, including SQL Server (v7/8/2000) for the last 9 or so years. I previously taught a course in Database Analysis and Design, but am now a freelance consultant and systems analyst.
Commercial database design and development work undertaken.
Question I am new to this.and im doing my internship in an info management dept.i was given a task to convert a current database using excel 2007 to access 2007.I know how to import and link. but the the data in excel use so many formulas and a lot of data.My question is :
1. How do we convert excel data with formula to access
2. There are a lot of sheet in an excel file,should I divide them into several table?
Answer Shafinaz, hi
1. Normalise the raw Excel data into individual Access tables (see wikipedia about Normalisation if you are unsure)
2. Leave calculated cells out of this process - only 'raw data'
3. Calculations go into queries. A query builds on raw data held in tables, does the set of calculations and 'presents' the results. Queries may be hierarchical in structure. So you can build up a result in several steps.
4. Build reports for management based on one or more final queries. Reports may be nested, so you can have a master report with one or more nested sub reports containd therein. The report is what you save to PDF (for example) and is what is presented to management.
