Hi, hope you don't think me foolish, but my boss wants me to try and build a database to capture all the work we do in house. I am in HR.
I have looked around the internet and see how to do everything within Access except the beginning of the design. Do i build the tables first, the forms, the reports, the queries? Just think I am looking for something conceptually on how to design it, not the details of the build.
Brian
FIRST DATABASE
-
Rudi
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: FIRST DATABASE
Hi Brian,
A database is pretty much useless without the tables that store the data. You cannot use forms or queries without the underlying table of information, and you certainly cannot generate reports without the tables or queries...so the ONLY place to start is with tables.
As a general rule of thumb, you would build a DB in this order:
1. The TABLE is the base building block for the DB. It's purpose is to store the information.
2. Next, (optionally) create forms for the primary tables. The purpose of forms are to provide a data entry point to feed info into the tables.
3. Queries are the workhorse of the DB. They are used to query the information in the DB and get stats/info for reports. They also can calculate and manipulate info.
4. Reports are created to present data in a readable and organised way. You would create reports mostly from queries, but can also report on a table directly.
When you build up tables you need to plan how you want to store the data. You will limit the functionality of the DB if you store ALL info in a single table. Usually you will build several tables that relate to each other and join them in the relationships window with one -to-many joins. For example: You could have a table holding customer information. You could have a table holding order information. Each table should have a primary key (unique) ID type value - like a customer ID and an Oder ID. Then, you would join the Customer Table to the Orders table using Customer ID (to a duplicate Customer ID in Orders). Logically, one customer will have many orders, so the join runs that way. You would not typically have one order belonging to many customers...
Without going into technicalities, you will ideally need to plan the data you want to store, sketch up the tables with their ideal fields and then think of the joins. Once you have a good plan in place, open and save a new DB in Access, then draw up ALL the tables in your plan. Join the necessary tables in the relationships window and then start entering some sample data. If the tables or functioning OK, start to create forms for the primary tables. Later, you can start setting up queries to ask questions of the data, and last will be the reports to draw up from those queries to present the data in reports.
To get you started, see the details in some of these links, re: Creating your first tables (or DB):
http://notebooks.com/2011/07/13/how-to- ... -database/
http://www.smallbusinesscomputing.com/b ... Access.htm
Info and designing relational tables:
http://databases.about.com/cs/tutorials ... etmenu.htm
http://www.dummies.com/how-to/content/h ... -2010.html
A database is pretty much useless without the tables that store the data. You cannot use forms or queries without the underlying table of information, and you certainly cannot generate reports without the tables or queries...so the ONLY place to start is with tables.
As a general rule of thumb, you would build a DB in this order:
1. The TABLE is the base building block for the DB. It's purpose is to store the information.
2. Next, (optionally) create forms for the primary tables. The purpose of forms are to provide a data entry point to feed info into the tables.
3. Queries are the workhorse of the DB. They are used to query the information in the DB and get stats/info for reports. They also can calculate and manipulate info.
4. Reports are created to present data in a readable and organised way. You would create reports mostly from queries, but can also report on a table directly.
When you build up tables you need to plan how you want to store the data. You will limit the functionality of the DB if you store ALL info in a single table. Usually you will build several tables that relate to each other and join them in the relationships window with one -to-many joins. For example: You could have a table holding customer information. You could have a table holding order information. Each table should have a primary key (unique) ID type value - like a customer ID and an Oder ID. Then, you would join the Customer Table to the Orders table using Customer ID (to a duplicate Customer ID in Orders). Logically, one customer will have many orders, so the join runs that way. You would not typically have one order belonging to many customers...
Without going into technicalities, you will ideally need to plan the data you want to store, sketch up the tables with their ideal fields and then think of the joins. Once you have a good plan in place, open and save a new DB in Access, then draw up ALL the tables in your plan. Join the necessary tables in the relationships window and then start entering some sample data. If the tables or functioning OK, start to create forms for the primary tables. Later, you can start setting up queries to ask questions of the data, and last will be the reports to draw up from those queries to present the data in reports.
To get you started, see the details in some of these links, re: Creating your first tables (or DB):
http://notebooks.com/2011/07/13/how-to- ... -database/
http://www.smallbusinesscomputing.com/b ... Access.htm
Info and designing relational tables:
http://databases.about.com/cs/tutorials ... etmenu.htm
http://www.dummies.com/how-to/content/h ... -2010.html
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
HansV
- Administrator
- Posts: 78655
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: FIRST DATABASE
I'd like to stress Rudi's recommendation to design the table structure before doing anything in Access. Time spent thinking about a good setup for your data will repay itself later on.
Only when the structure is clear, start Access and create the database.
Only when the structure is clear, start Access and create the database.
Best wishes,
Hans
Hans
-
bawhorton
- NewLounger
- Posts: 8
- Joined: 26 Oct 2010, 12:34
Re: FIRST DATABASE
Great recommendations, thank you very much. Brian