Part II: How to Create Database and tables in Microsoft Access
By Sergey Skudaev
Let us continue learning how to create Microsoft Access database and tables.To plan what kind database tables we need to create in Microsoft Access database we have to consider the following questions.
What kind data we want to trace?
1. Information about credit card. Credit card name (like Bank One or CHASE ), credit card type (MasterCard, Visa etc) number (the last 4 digit), expiration date, phone, address, city, state, zip. notes
2. Information about transactions: balance, minimum payment, due date, paid date, paid amount, check number maybe something else. Be creative. Since, one credit card has many transactions we have to store transactions in the separate table. (If we stored them in the same table then the credit card info would be repeated as many time as many transactions it has. We do not want to waste computer resources.) Primary key is a field in a table that holds a unique value for an entity. For credit card unique value is credit card number, but also we can use auto incrementing number that is generated by system automatically. One credit card has many transactions. One transaction can be related only to one credit card. Relations between credit card table and transactions table is one to many.It means that we have to place credit card id (primary key in credit card table) in transactions table where it plays a role of the foreign key. You will see and understand later.
Open MS Access.
Select the Blank Access database radio button and click OK.
Enter file name credit.mdb and click the Create button.
Database will be created. Select Tables from Objects list and double click Create table in design view.
Table displays in design view.
Enter the first field: cardid and select Autonumber from Data type
Select No duplicates at the bottom. Enter the rest fields selecting appropriate data types Save table and name it credit_cards. It is good habit to use one word to name tables.
You will be prompt to create primary key:
Answer Yes. Close the credit_cards table.
To create transaction table we will use the data definition language:
create table transactions(
transid int not null,
cardid int,balance currency,
,# int means integer. Not NULL means NULL is not allowed
# varchar(20) -means text 20 characters long. 255 allowed
Select Query in the objects panel and double click the Create query in Design view.
Show table dialog displays:
Click close button. Select View, SQL view in main menu:
Select Query window displays. Copy the script and paste in the window. Click Run ! button
Save query: Enter query name and click OK
Close the SQL view window. Select Tables from object panel. Select transactions table and open in design view.
Change transid data type to Autonumber.Close and Save the table. Enter dummy data in the tables: at least two credit cards and four transactions.
Select tools, relationships from main menu:
Show table window displays. Select tables (one at a time) and click the Add button. Click Close button. Select cardid from credit_cards table and drag it to cardid in transactions table. The Edit Relationships window displays.
Click the Create button.
A link appears between tables. Click X in the right upper corner of the relationships window. Answer Yes to save changes. You can create relationships in create table script. If you what to try, select transaction table and delete it. Answer yes to all prompts. Select Queries, Create query in Design View. Open View SQL and paste the following script.
create table transactions(
transid int not null PRIMARY KEY,
FOREIGN KEY (cardid)
Please rate the tutorial
I have MS Access 2007 at it looks totally different from yours. I really want to learn all about websites, SQL (whatever that is) and everything else. You should do some video tutorials. Thanks for this website....I will continue to study it until it "clicks" in my brain what I am suppose to do. lol.
Thank for comment. Even if it looks differently, the idea behind all relational databases is the same. Understanding how a database organizes tables help you to understand any database. For example, I have to work with oracle at my work and general knowledge about database helps me a lot. For web site you have to learn MySQL and SQL. I have tutorial about that too.
- Batch Files
- Java Properties
- Form Validation
- Display Image PHP
- Upload File PHP
- Environment Variables
- Delete Trojan horse
- Database Design
- Password Keeper
- MySQL and Excel
- Learn MySQL
- Learn SQL
- Learn PHP
- Learn C++
- Learn Java
- Learn Visual Basic
- Web Site Tips
- Virtual Server
- User_Auth. Demo
- My Best CSS Layout
- CSS Tables
- PHP + Oracle
- Using Twitter
- Set Shopping Cart
- Advertise here
- Site map