Using fictional data provided by my instructor, I was tasked with creating relationships between tables, designing and running queries, and generating reports within Microsoft Access.
I started this project by creating three tables: customers, orders, and products. I assigned “CustID” as the primary key of the customers table, “OrderID” as the primary key of the orders table, and “ProdID” as the primary key of the products table. I then established a one to many relationship between “CustID” (primary key, customers table) and “CustID” (orders table). I also established a one to many relationship between “ProdID” (primary key, products table) and “ProdID” (orders table).
I then designed queries to pull specific information out of these tables.
The first query I designed was “My Purchases.” Its purpose was to display all of the orders the customer Dylan Tosti had placed. As it happened, I had only made one order according to this data set.
The next query I designed was “My Total.” It was simply meant to display how much money I had spent.
The last query I designed was “Totals Greater Than Mine.” The purpose of this query was to display all of the customers who had spent more money than me and the total amount they spent.
The final step of this project was to create reports based on these queries. You can view these reports below.