Organizing a Simple Library in Microsoft Access – Pt. 1 : Creating a Database [EN – ID] Mengelola Perpustakaan Sederhana Dengan Microsoft Access – Bagian 1 : Menciptakan Basis Data

in STEEM FOR BETTERLIFE • 8 days ago (edited)

tc002000.png
Created with Adobe Photoshop 2021.

bhs inggris.png

1. Scenario

Let's say you have a sufficient collection of books, and you want to open a public library. You need a database to manage your book collection, members, as well as record every time a rental and return occurs. How will you do it? You can either do it manually in a specific book, or choose to utilise technology such as Microsoft Excel or Microsoft Access.

In this tutorial I will write about using MS. Access for such purposes.

How does the MS. Access database that we are going to create work? We will create 3 data sources, namely:

  1. Book Data, which contains all the books in the collection, including Title, Publisher, Year of Publication, ISBN, Stock. The data is updated every time there is a chnage in the collection;
  2. Member Data, which contains relevant data for each member, e.g. Name, Address, and Phone Number. The data is updated when a member is added; and
  3. Rent (Borrowing) Data, is filled in every time a book is borrowed and returned. Rent data will contain information such as: Rent Date, Due Date, Book ID, Member ID (Borrower).

2. Getting Started With Tables

2.1. Member Table

  1. Open Microsoft Access and create a new database file (Blank database).
    tc002001.png
  2. Specify the file name and storage location. Click [Create]
    tc002002.png
  3. Now the database is available. Access has provided a table by default to start with. We will use this table to create the Members database. For a better table creation experience, right-click on the table name and select [Design View].
    tc002003.png
  4. Set the Table’s name.
    tc002004.png
  5. Fill in the table as shown below. Use the Field Properties (at the bottom) and Property Sheet (right) to make any adjustments you need, but this can always be done in the future if needed.
    tc002005.png
  6. Save.

2.2. Book Table

  1. To start a new table, press [Create] on the menu bar, and select [Table].
    tc002006.png
  2. Open the new table in Design View mode. And create the fields as in the following image.
    tc002007.png
  3. Save Table.

2.3. Rent Table

  1. Create a new table and fill the fields with the data types as shown below.
    tc002008.png
  2. Save.

2.4. Linking The Tables

Once the tables are in place, they need to be linked.

  1. Click [Database Tools] on the menu bar and select [Relationships].
    tc002009.png
  2. Select all the tables, and press [Add Selected Tables].
    tc002010.png
  3. Click on the Book_ID in the Book Table and drag it to the Book_ID in the Rent Table. And do the same for Member_ID in the Member Table to Renter_ID in the Rent Table. Make sure to start dragging from the Book Table and Member Table, not from the Rent Table.
    tc002011.png
  4. In the dialogue box that appears, make sure to tick [Enforce Referential Integrity]. Click [Create].
    tc002012.png

    Why you should tick "Enforce Referential Integrity":
    • Avoiding the existence of ‘Orphan Data’. If Enforce Referential Integrity is not enabled, when the data in table A is deleted, the related data in connected table B will still exist. For example: Data number 5 in the borrower table is deleted, while he has borrowed several books, then the report will show the borrowing without knowing who borrowed.
    • Maintaining data consistency: If Referential Integrity is not enabled, Access will not block any input data that includes a foreign key that is not in the primary key of the input table, and this will cause data asynchrony.
    • Without Referential Integrity, relationships between tables can become unclear, causing data anomalies and errors in queries.
    • And some other reasons.
  5. Save. Now the tables are connected.
    tc002013.png

3. Proceeding with Forms

Once we have the tables we need and connect them, we actually have everything we need to provide a database, but we will be able to work better in entering, editing, displaying, and managing data with the help of Forms that have a more attractive interface than tables, in addition to other features such as the ability to run Macros and VBA (Visual Basic for Applications) code, as well as enhanced security features.

3.1. Book Form

  1. Make sure the Book Table is selected. Then on the menu bar, press [Create] and select [Form].
    tc002014.png
  2. By default, the Form will open in Layout View mode. Make the necessary adjustments such as layout, width and height of boxes (cells), and set the properties of the cells as deemed necessary.
    tc002015.png

    You can switch to Design View mode if you wish. There are several ways to do this, one of which is: right-click on the Form tab and select [Design View].
    tc002016.png
  3. The form in Form View mode after adjustments have been made is shown in the image below. It can be seen that under the Book Form that has been created, there is a Rent Table, because we have connected the Book Table to the Rent Table.
    tc002017.png
  4. Now we are ready to record new data for Books using the Book Form. Let's try by entering the first entry. Once done, save it.
    tc002018.png
  5. Furthermore, when we open the Book Table, we can see an entry there.
    tc002019.png

3.2. Members Form

In the same way, we created the Member Form. The result, after making adjustments, looks like the image below.

tc002020.png

The form is ready to be used for Member data recording. Save the form.

3.3. Rent Form

The Rent Form looks like the image below.

tc002021.png

3.4. Form Operations

At the bottom of each Form there are operational buttons as shown in the following figure.

tc002022.png

The buttons have the functions of navigation and new data addition, as well as searching. There is also information on the total data held.

4. Practice Example

I already have some members and books in my database, and I also have a record of past and current loans.

4.1. Book Form and Table

Here is a screenshot of the book data with ID 8 in the Book Form, where the top of the Form shows the book details, and the bottom conveys the borrowing record information of the book under that ID. Every time someone borrows the book, this borrowing (rent) table will add an entry. Every time the book is returned and the status is changed to ‘Returned’ in the Borrowing Form, the Borrowing Table will also update the data to ‘Returned’.

tc002023.png

The table below the Form can also be used to add new data related to the ID in the Form above it. So, it works both ways, very flexible, very convenient!
The image below shows the Table View of the books that I have in the Book Table.
tc002023a.png

4.2. Member Form and Table

See the following screenshot. It contains information related to the Member with ID 5. We can see the details of personal information and borrowing information.

tc002024.png

The Member Table I have so far is shown in the image below.
tc002024a.png

4.3. Rent Form and Table

And, the Rent Form, as shown in the image below, does not have any tables under it, because in the Relationship that we created, the Rent table becomes the destination of two other tables (Members and Books). So, the Borrowing Form only serves to record new data and edit existing data.

tc002025.png

The Rent Table is shown in the image below.
tc002025a.png

5. Conclusion

  • Microsoft Access is used to build databases for various needs;
  • Various tables that each manage specific data need to be linked to create connectivity between different types of data;
  • Forms and tables can be used to add new data or edit existing data;

6. Closing

We have come to the end of the first part of this article. The next part will be about how to make a report. Thank you for reading. Please leave your feedback to correct or add information. Also very much thanks to the #techclub mentors: @kafio, @mohammadfaisal, @alejos7ven, for the corrections and guidance.

kr-steemkiss25a.png

My Introductory Post | Telegram | Discord | X

Picture created by @aneukpineung78


Thanks for stopping by.

bhs indonesia.png

1. Skenario

Katakan, Anda memiliki koleksi buku dalam jumlah yang cukup, dan Anda ingin membuka sebuah perpustakaan untuk umum. Anda memerlukan suatu basis data untuk mengelola koleksi buku, anggota, sekaligus mencatat setiap kali terjadi peminjaman dan pengembalian. Bagaimana Anda akan melakukannya? Anda bisa saja melakukannya dengan melakukan pencatatan manual di dalam sebuah buku khusus, atau memilih untuk memanfaatkan teknologi seperti Microsoft Excel atau Microsoft Access.

Dalam tutorial ini saya akan menulis tentang penggunaan MS. Access untuk keperluan seperti itu.

Bagaimana cara kerja basis data MS. Access yang akan kita buat? Kita akan membuat 3 sumber data, yaitu:

  1. Data Buku, di mana terdapat semua koleksi buku yang dimiliki, termasuk Judul, Penerbit, Tahun Terbit, ISBN, Stok. Data diperbaharui setiap ada penambahan buku di dalam koleksi;
  2. Data Anggota, berisi data-data relevan setiap orang yang bisa meminjam buku, misal Nama, Alamat, dan Nomer Telepon. Data diperbarui ketika terjadi penambahan Anggota; dan
  3. Data Peminjaman, diisi setiap terjadi peminjaman dan pengembalian buku. Data Peminjaman akan berisi informasi-informasi seperti: Tanggal Peminjaman, Tanggal Jatuh Tempo, ID Buku, ID Anggota (Peminjam).

2. Memulai Dengan Tabel

2.1. Tabel Anggota

  1. Buka Microsoft Access dan buat file basis data baru (Blank database).
    tc002001.png
  2. Tentukan nama file dan lokasi penyimpanan. Klik [Create]
    tc002002.png
  3. Basis data telah tersedia. Secara bawaan Access telah menyediaan sebuah tabel untuk memulai. Kita akan menggunakan tabel ini untuk membuat database Anggota. Untuk pengalaman penciptaan tabel yang lebih baik, klik kanan pada nama tabel lalu pilih [Design View].
    tc002003.png
  4. Beri nama untuk tabel.
    tc002004.png
  5. Isi tabel seperti dalam gambar berikut. Pergunakan Field Properties (pada bagian bawah) dan Property Sheet (sebelah kanan) untuk melakukan penyesuaian yang Anda butuhkan, tapi ini bisa selalu dilakukan di masa depan jika dibutuhkan.
    tc002005.png
  6. Simpan.

2.2. Tabel Buku

  1. Untuk memulai tabel baru, tekan [Create] pada menu bar, dan pilih [Table].
    tc002006.png
  2. Buka tabel baru dalam mode Design View. Dan buat field-field sebagaimana dalam gambar berikut.
    tc002007.png
  3. Simpan Tabel.

2.3. Tabel Peminjaman

  1. Buat tabel baru dan isi field-field dengan jenis data seperti dalam gambar berikut.
    tc002008.png
  2. Simpan.

2.4. Menghubungkan Tabel-Tabel

Setelah tabel-tabel tersedia, tabel-tabel tersebut perlu dihubungkan.

  1. Klik [Database Tools] pada menu bar dan pilih [Relationships].
    tc002009.png
  2. Pilih semua tabel, dan tekan [Add Selected Tables].
    tc002010.png
  3. Klik pada Book-ID di tabel Book lalu seret ke Book_ID di tabel Rent. Dan lakukan hal serupa pada Member_ID di tabel Member ke Renter_ID di tabel Rent. Pastikan untuk memulai menyeret dari tabel Book dan tabel Member, bukan dari tabel Rent.
    tc002011.png
  4. Pada kotak dialog yang muncul, pastikan mencentang [Enforce Referential Integrity]. Klik [Create].
    tc002012.png

    Mengapa Anda harus mencentang “Enforce Referential Integrity”:
    • Menghindari adanya 'Data Orphan'. Jika Enforce Referential Integrity tidak diaktifkan, ketika data di tabel A dihapus, tabel B yang terhubung masih ada. Sebagai contoh: Data nomor 5 pada tabel peminjam dihapus, sementara ia telah meminjam beberapa buku, maka laporan akan menampilkan peminjaman tanpa mengetahui siapa yang meminjam.
    • Menjaga konsistensi data: Jika Referential Integrity tidak diaktifkan, Access tidak akan memblokir setiap data input yang menyertakan foreign key yang bukan merupakan primary key dari tabel input, dan hal ini akan menyebabkan data tidak sinkron.
    • Tanpa Referential Integrity, hubungan antar tabel dapat menjadi tidak jelas, menyebabkan anomali data dan kesalahan dalam kueri.
    • Dan beberapa alasan lainnya.
  5. Simpan. Sekarang hubungan antar tabel telah diciptakan.
    tc002013.png

3. Melanjutkan Dengan Form

Setelah memiliki tabel-tabel yang kita perlukan dan menghubungkannya, sebenarnya kita sudah memiliki semua yang dibutuhkan dalam menyediakan basis data, tetapi kita akan bisa bekerja dengan lebih baik dalam memasukkan data, mengedit, menampilkan, dan mengelola dengan bantuan Form yang memiliki antar muka yang lebih menarik dari sekedar tabel-tabel, selain fitur-fitur lain seperti kemampuan menjalankan Macro dan kode VBA (Visual Basic for Applications), serta fitur keamanan yang ditingkatkan.

3.1. Form Buku

  1. Pastikan Tabel Buku terpilih. Lalu pada menu bar, tekan [Create] dan pilih [Form].
    tc002014.png
  2. Secara bawaan, Form akan terbuka dalam mode Layout View. Lakukan penyesuaian yang dibutuhkan seperti tata letak, lebar dan tinggi kotak (sel), dan mengatur properties sel-sel yang dianggap perlu.
    tc002015.png

    Anda bisa beralih ke mode Design View jika menginginkannya. Ada beberapa cara untuk melakukan ini, salah satunya: klik kanan pada tab Form lalu pilih [Design View].
    tc002016.png
  3. Form View setelah saya melakukan penyesuaian pada Form terlihat pada gambar di bawah. Dapat dilihat bahwa di bawah Form Buku yang kita bikin, terdapat Tabel Peminjaman, karena Tabel Buku telah kita hubungkan ke Tabel Peminjaman.
    tc002017.png
  4. Sekarang kita siap untuk merekam data baru untuk Buku menggunakan Form Buku. Mari kita coba dengan memasukkan entri pertama. Setelah selesai, simpan.
    tc002018.png
  5. Selanjutnya ketika kita membuka Tabel Buku, maka telah ada entri di sana.
    tc002019.png

3.2. Form Anggota

Dengan cara yang sama, kita menciptakan Form Anggota. Hasilnya, setelah melakukan penyesuaian, terlihat seperti pada gambar di bawah.

tc002020.png

Form siap digunakan untuk pendataan Anggota. Simpan Form.

3.3. Form Peminjaman

Form Peminjaman terlihat seperti pada gambar di bawah.

tc002021.png

3.4. Operasi Pada Form

Pada bagian bawah setiap Form terdapat tombol-tombol operasional sebagaimana terlihat pada gambar berikut.

tc002022.png

Tombol-tombol tersebut memiliki fungsi navigasi dan penambahan data baru, serta pencarian. Di sana juga terdapat informasi total data yang dimiliki.

4. Contoh Praktik

Saya telah memiliki beberapa anggota dan buku di dalam basis data saya, dan juga saya telah memiliki catatan peminjaman yang pernah dan sedang terjadi.

4.1. Form dan Tabel Buku

Berikut adalah tangkapan layar ketika saya melihat data buku dengan ID 8 di Form Buku, yang mana pada bagian atas Form terlihat detil buku, dan bagian bawah menyampaikan informasi catatan peminjaman buku dalam ID tersebut. Setiap kali seseorang meminjam buku tersebut, tabel peminjaman ini akan menambah entri-nya. Setiap kali buku dipulangkan dan di Form Peminjaman status dirubah menjadi “Returned”, maka Tabel Peminjaman juga akan memperbaharui data menjadi “Returned”.

tc002023.png

Tabel di bawah Form juga bisa dipergunakan untuk menambah data baru terkait ID pada Form di atasnya. Jadi, ini bekerja dua arah, sangat fleksibel, sangat memudahkan!
Gambar di bawah memperlihatkan Table View dari data buku yang saya miliki dalam Tabel Buku.
tc002023a.png

4.2. Form dan Tabel Anggota

Lihat tangkapan layar berikut. Ini mengandung informasi terkait Anggota dengan ID 5. Kita bisa melihat detil informasi pribadi dan informasi peminjaman.

tc002024.png

Tabel Anggota yang saya miliki sejauh ini ditunjukkan pada gambar di bawah ini.
tc002024a.png

4.3. Form Peminjaman

Sementara, Form Peminjaman, sebagaimana terlihat pada gambar di bawah, tidak memiliki tabel apapun di bawahnya, sebab dalam Relationship yang kita buat, tabel Rent menjadi tujuan dari dua tabel lain (Anggota dan Buku). Jadi, Form Peminjaman hanya berfungsi untuk mencatat data baru dan mengedit data yang ada.

tc002025.png

Tabel Peminjaman terlihat dalam gambar berikut.
tc002025a.png

5. Kesimpulan

  • Microsoft Access dipergunakan untuk membangun basis data bagi berbagai macam kebutuhan;
  • Berbagai tabel yang masing-masing mengelola data spesifik perlu dihubungkan untuk menciptakan konektifitas antara berbagai jenis data;
  • Form dan Tabel bisa dipergunakan untuk menambah data baru atau mengedit data yang telah ada;

6. Penutup

Demikianlah bagian pertama dari tulisan ini. Pada bagian selanjutnya tulisan akan berisi tentang bagaimana membuat laporan. Terima kasih telah membaca. Silahkan tinggalkan tanggapan Anda untuk mengkoreksi atau menambah informasi.

kr-steemkiss25a-id.png

Artikel Perkenalan Saya | Telegram | Discord | X

Picture created by @aneukpineung78


Thanks for stopping by.

#techclub #techclub-s23w5 #indonesia #learnwithsteem #tutorial
8 days ago in STEEM FOR BETTERLIFE by
$36.34
  • Past Payouts $36.34
  • - Author $18.27
  • - Curators $18.07
189 votes
Reply 9
Sort:  
  • Trending
    • [-]
     8 days ago 

    This library database project is well-organized with clear steps! I like how you create three important tables - Book, Member, and Rent. The design shows good planning.

    Good parts:

    • Simple data structure that makes sense for a library
    • Clear step-by-step guide with pictures
    • Good explanation of relationships between tables
    • Nice forms that make data entry easier than typing in tables
    • Example data shows how real library information would look

    Advice:

    • Maybe add search function to find books by title or author
    • Could add reports to show overdue books
    • Add field for book categories to help organize collection
    • Could explain more about why "Enforce Referential Integrity" is important
    • Consider adding barcode support for easier checkout

    Your screenshots help understand the process very well. The project is good for small libraries and can be upgraded as needed. Good job on making a useful database.

    $4.32
    • Past Payouts $4.32
    • - Author $2.16
    • - Curators $2.16
    2 votes
    Reply
    [-]
     7 days ago (edited)
    • Search and other functionalities. There are some functionalities can be added to Form, and I'm not talking about that in this post because in this first part, as the title suggests, I'm focusing on how to create a database. And regarding Search, Microsoft Access has provided a default search feature in every form that we create, and that I briefly explained in the article. See the example below, when I type in a keyword in the Search box of the Book Form, the Form automatically displays the results that match the characters I typed, and below that, the table also automatically shows the borrowing history of the book displayed in the form.
      tc002026.png

      However, while Access provided this functionality, users can also decorate each form with similar or even more functionalities, especially for aesthetic reasons. And this could also be another topic.
    • Report. As I mentioned in the post, I will discuss it in the next section. This post would be very long if it covered all the functionalities and other things in one post.
    • Field for book categories. In the Book Form, there is a category section, and I divided it into fiction and non-fiction. But if your intention is to allow users to see a list of books in each category, and related information (e.g. borrowing history), then that can be done by creating a report query (which I plan to cover in the next part of this series). But really, you've shown me what I can do to develop this database further.
    • Some reasons why you should tick "Enforce Referential Integrity" (added to the post):
      • Avoiding the existence of ‘Orphan Data’. If Enforce Referential Integrity is not enabled, when the data in table A is deleted, the related data in the connected table B still exists. For example: Data number 5 in the borrower table is deleted, while he has borrowed several books, then the report will show the borrowing without knowing who borrowed.
      • Maintaining data consistency: If Referential Integrity is not enabled, Access will not block any input data that includes a foreign key that is not in the primary key of the input table, and this will cause data asynchrony.
      • Without Referential Integrity, relationships between tables can become unclear, causing data anomalies and errors in queries.
      • And some other reasons.
    • Barcode. Barcodes or QR codes are a very interesting idea that will definitely help users like you mentioned, maybe I will write about this in the next part.

    As the title suggests, this post is about organising a simple library (including the database I started to create and the process I am showing in this post is basic and limited to providing a simple database infrastructure). Upgrades can always be made at any time. Many possibilities for development, creativity might be the only limit.

    Thank you for your feedback, @kafio. I really appreciate this.

    $4.32
    • Past Payouts $4.32
    • - Author $2.16
    • - Curators $2.16
    3 votes
    Reply
    [-]
     4 days ago 

    Keep doing the good job!

    $0.00
      Reply
      [-]
       8 days ago 

      image.png
      Click image to see on X.

      $0.00
        Reply
        [-]
         8 days ago 

        Informatif dan edukatif, Pekerjaan bagus dan ini menjadi referensi bagi siapa saja yang ingin belajar Microsoft Access

        $0.00
          Reply
          [-]
           7 days ago 

          Terima kasih, bang irawandedy, atas tanggapan Anda. Betul sekali, apa yang saya perlihatkan dalam artikel ini adalah sesuatu yang sangat mendasar, semua orang yang memiliki ketertarikan pasti akan mampu mengikutinya. Batasannya adalah kemauan, kegigihan dan kreatifitas.

          $0.00
            Reply
            [-]
             8 days ago 

            Your post has been rewarded by the Seven Team.

            Meet our work

            Delegate Steem Power to @steem-sevenFollow curation trails using Steem Ways

            Support partner witnesses

            @seven.wit
            @cotina
            @xpilar.witness

            We are the hope!

            Untitled design.png

            First_Memecoin_On_Steemit_Platform.png

            $0.00
              Reply
              [-]
               7 days ago 

              Thanks. Good to see a complete example!

              $0.00
                Reply
                [-]
                 6 days ago 

                It's part 1. I will do the sequel this week.

                Thanks for dropping by and leaving the comment, @jeff-kubitz.

                $0.00
                  Reply

                  New to Steemit?