What is the correct way to build an Access Database

[rosscortb]

Hello,



Can someone tell me what is the best practice when I put together an access database?

Do I build my Tables, then Queries, then Forms then Reports?

Also, at what point of the building process should I split the database?

Thanks

Ross

[ranman256]

You cant have queries nor reports without tables. Build tables, then queries, forms, then reports.
As a helpful naming practice, start all tables with t, queries with q,
select queries, qs
append queries ,qa
update queries, qu
...etc

split the db last. After everything is working. It helps.

search this board for links to making databases.

[JoeM]

I think the most important thing to know before building a database is to understand Relational Databases and the Rules of Normalization. If you do not know these concepts, you will want to learn them before deciding on how to build your tables. Well-designed tables will save you much heartache down the line (trust me, I learned the hard way)!

Here is a good link on that: http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf

[orange]

I agree with the others --tables are important as are Database Design and Normalization. Here is a tutorial that will help with design.
One of the key pieces of database is to really know your "business", since your database is intended to support some "business or processes".

Here is an approach from databaseanswers.org

You can have a look at this Page to see how this Approach applies to the design of a  Database for an HR Department. 
The Approach defined here is aimed at beginners and experienced practitioners. 
It makes some recommendations to simplify basic design decisions on key structures.   
These are the Steps in a Top-Down Approach :-  

Define the Scope as the Area of Interest,(e.g. the HR Department in an organization).
Define the "Things of Interest",(e.g. Employees), in the Area of Interest.
Analyze the Things of Interest and identify the corresponding Tables.
Consider cases of 'Inheritance', where there are general Entities and Specific Entities. 
For example, a Customer is a General Entity, and Commercial Customer and Personal Customer would be Specific Entities. If you are just starting out, I suggest that you postpone this level of analysis.
At this point, you can produce a List of Things of Interest.
Establish the relationships between the Tables.  
For example, "A Customer can place many Orders", and "A Product can be purchased many times and appear in many Orders."
Determine the characteristics of each Table,(e.g. an Employee has a Date-of-Birth).
Identify the Static and Reference Data, such as Country Codes or Customer Types.
Obtain a small set of Sample Data,  
e.g. "John Doe is a Maintenance Engineer and was born on 1st. August, 1965 and lives at 22 Woodland Street, New Haven. 
"He is currently assigned to maintenance of the Air-Conditioning and becomes available in 4 weeks time"
Review Code or Type Data which is (more or less) constant, which can be classified as Reference Data. 
For example, Currency or Country Codes. Where possible, use standard values, such as ISO Codes.
Look for 'has a' relationships. These can become Foreign Keys, or 'Parent-Child' relationships.
You need to define a Primary Key for all Tables.  
For Reference Tables, use the'Code' as the Key, often with only one other field, which is the Description field. 
I recommend that names of Reference Data Tables all start with 'REF_'.  
For all non-Reference Data Tables, I suggest that you simply assign an Auto-increment Integer for each Primary Key. 
This has some benefits, for example, it provides flexibility, and  it's really the only choice for a Database supporting a Web Site. 
However, it complicates life for developers, which have to use the natural key to join on, as well as the 'surrogate' key.   
It also makes it possible to postpone a thorough analysis of what the actual Primary Key should be.  Which means, of course, that it often never gets done.
Confirm the first draft of the Database design against the Sample Data.
Review the Business Rules with Users,(if you can find any Users).
Obtain from the Users some representative enquiries for the Database,  
e.g. "How many Maintenance Engineers do we have on staff coming available in the next 4 weeks ?"
Review the Results of Steps 1) to 9) with appropriate people, such as Users, Managers,  
Development staff, etc. and repeat until the final Database design is reached.
Define User Scenarios and step through them with some sample  data to check that that Database supports the required functionality.
   

[ButtonMoon]

Ross,

If you are new to database development then I suggest you don't try to design an entire database or application in one go. Focus on one piece of functionality at a time; design the database to support that piece and then build the queries, forms etc on top. Deliver the application in small increments and, crucially, get feedback from the users at every stage. So my answer to your question about what comes first is: everything, then repeat.

Also, at what point of the building process should I split the database?

From the beginning. Start with the application components (forms, queries reports) in one file and the data in another then you won't need to worry about splitting them at a later stage. There's no real excuse for mixing data and application together.