All I know is that it’s a “database management system”, and I don’t even know what that means! I have tried fooling around with Access and have gotten nowhere. Where do I start and what is the purpose of this?
In: 4
If you have 100 customers and they each have 10 orders, and each order has 5 items, you can store this in 3 tables, Customers, Orders, Items. Each table will have a key value for each row. There is a CustomerID, OrderID, ItemID. To relate things together, you use the ID keys. So there is a way to put 5 ItemID into 5 rows of an Order to make a single order and each row has the OrderID. Then you relate the CustomerID to the Order by using the CustomerID and OrderID. You could add a new table called CustomerOrders that has the CustomerID and the OrderID. Now you can relate customers, orders, and items.
So let’s say you want to know all the customers who ordered ItemID 35, which is a green widget. So you write a query, something like this.
Select distinct c.CustomerID from CustomerOrders c, Order d where c.orderID = d.OrderID and d.itemID = 35;
That gives a list of customerID who have ever ordered green widgets. Of course, you want customer names and addresses so you would probably store that in the Customers table or if they move around you might put those customer properties in a related table of CustAddress which is related to customers by the customerID. With queries you can report on the data as finely as your database allows. You can count the number of green widets ordered by each customer, find the average, or count how many were ever ordered by every customer combined.
Access also has VBA so you can write functions to return values that would be cumbersome to put into a query and you can write code to update values in the database. For example, you can write a job that runs on a schedule to automatically send out invoices and update a value in a table to show when the invoice was sent.
Latest Answers