Basic info and pointers requested

[George J]

I have never used Access before, but my work has decided that they need a database and that i'm the person to make it. They want it to be in Access rather than anything else as they already have the required licences etc.

I am quite comfortable using Excel and VBA, but as i have never used Access before am unsure where to begin with this.

I know that i should work backwards and once i know what i need out of the system, then i can start to design it, but can i use an Excel front-end and an Access back-end or is it better that it is all done in Access?

Work have given me 1 days training, so i know a little (very) about relationships and queries.

My query is:
* can anyone provide some pointers as to whether it would be better to use Excel, as i at least have knowledge of this, or to do it all in Access?
* Also they do not want the data to change as soon as a value is amended on a userform, but for the user to press a save button - i queried this and was told i would need to do something myself as the data will change on the database as soon as the field is changed.
* Is there anything i should or should not be doing during the design stage before working on what will be a large project? I have heard that it can be difficult to add parts on at the end if anything is overlooked.

I realise that this is very basic stuff, but any advice would be most welcome.

 

[jackd]

This is a scary rationale to undertake database

my work has decided that they need a database and that i'm the person to make it.

Typically you have a business need that requires data collection, data storage, reports,and some user interaction. It isn't like going to the supermarket and selecting some software---hmmm Access or Excel?

Access is NOT Excel; they are quite different in what they do well.

To appreciate database and to get some understanding of fundamentals, you'll be well advised to research Normal Forms and Normalization.

I recommend the first few topics, at least, at this site
http://www.rogersaccesslibrary.com/forum/topic238.html


There is a set of online tutorials at http://www.learnaccessnow.com/

Good luck.

 

[George J]

Thanks for the reply jackd

I'm sure my bosses have identified a business need for this - they purchased a commercial database a long time ago, but can't get the data out in the format required and some of the data isn't showing up any any of the reports.

I will definately read through the links you provided and research how to break this all down.

Again, many thanks
George

 

[alansidman]

You might find the following helpful

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Alan

 

[jackd]

There is a free video tutorial here on Normalization that may be of use.
http://www.youtube.com/watch?v=uO80f642LCY&feature=related

 

[HiTechCoach]

... know that i should work backwards and once i know what i need out of the system, then i can start to design it,

That is the correct method with any software development project.

The first three steps in delevolping software are: Plan, Plan, Plan. Unfortunately it is to often use these steps: Code, deliver, OK fine we do some Planning.

... but can i use an Excel front-end and an Access back-end or is it better that it is all done in Access?

I think it is really better to do it the other way around. Access has very powerful tools for dealing with data entry. Bound forms and sub forms make working with a relational data very easy.

Access is also a very powerful report write. Excel has limited printing capabilities when working with relational data.

Excel is great for analyzing data from Access.

* can anyone provide some pointers as to whether it would be better to use Excel, as i at least have knowledge of this, or to do it all in Access?

Access is used for:
Storing relational data
Maintaining data/ Data entry
Reports
etc.

Excel is good for:
Summarizing data
Analyzing data

Excel is a great companion for Access.

* Also they do not want the data to change as soon as a value is amended on a userform, but for the user to press a save button - i queried this and was told i would need to do something myself as the data will change on the database as soon as the field is changed.

That is incorrect.

Access saves the data back to the table when you change records. Not when you edit a single field.

Access forms' have a before update event that can be used to ask the user to confirm the save.

* Is there anything i should or should not be doing during the design stage before working on what will be a large project? I have heard that it can be difficult to add parts on at the end if anything is overlooked.

Not exactly. I find that to be a lot more true for spreadsheets that databases.

I rarely see any database application that does not need to have features added. I spend over 80% of my time adding new features to applciations.

If you properly normalize your table design then adding new feature is very easy.

It is the poorly designed database applications that are hard to maintain or add new features. This is not limted to just Access. It is true with any database platform.

I believe the most critical part to the start of any database is in the proper design of the tables. This means having a very good understanding of database normalization.


When I am training Excel user to work in Access the hardest thing for them is to forget everything they new about how to design a good spreadsheet.


To help get you started here are some links:

• Database planning tutorial Part I
• Database planning tutorial Part II
• argeedblu's FAQ on Design Strategies
• Noah's FAQ on Normalization
• Jerry's FAQ on Normalization
• GroverParkGeorge's PDF on Normalization
• A Discussion on Storing Calculated Values and Audit Trails
• Jerry's FAQ on Fully Qualified Naming
• Jerry's FAQ on Glossary
• Danny's FAQ on Reserved Words
• Autonumbers - What They Are / Are Not
• Primary Keys - Natural vs Surrogate
• Naming Conventions - Hungarian Notation
• Crystal's ACCESS BASICS
• MSKB 283878: Description of the database normalization basics
• The Evils of Lookup Fields in Tables
• Sharing/Splitting a Database

 

[Norie]

I agree with everything Boyd posted, especially the planning part.

 

[George J]

Many thanks guys - lots to read up on, but i need all the help i can get. Will probably be back with more questions later, but you've given me a place to start and some tips on what to keep an eye on.

Thanks again.
George

 

[HiTechCoach]

George,

Good luck with your project.

We will be here when you need further assistance.