Access 2007 Queries

[MonteCristo]

Hello Team,

I am very very new to SQL queries and to Access in general, so I desperately need your help. I have decided to keep track of my company in Access and I would like to also be able to find my data fast. I have 3 tables, Customers-Orders-Inventory with fields, respectively:

customerID, name, address, amountPurchases

customerID, itemID, quantity

itemID, description, price, quantity.

The table ​

​

​

​

Customers identifies each customer by ID number and gives, in addition to the name and address, the total amount of purchases during the current year prior to today. The table Inventory identifies each product in stock by an ID number and gives its description, price (per unit) and the quantity in stock at the beginning of the day. The table called Orders gives the orders received today. Suppose it is now the end of the day.​

I would like the following:

1) Display the items that are out of stock and therefore must be reordered. Of course under the Orders table some items appear multiple times, depending on when the order was placed. So I need to add them up and then compare to the quantity in inventory.

2)Display bills for all customers who ordered during the day. The bill should indicate if an item is currently out of stock. Meaning I need to see how much each customer ordered in dollars and if I met his/her quota or not.

3) Display the total purchases broken out by Customer. So I need to add what was ordered today to the amountPurchases of the year.

Can all this be done in SQL/Access 2007 with a few queries?

Thank you so much.

 

[SteveSchapel]

Monte,

You will certainly be able to achieve your purpose by using Access.

However, at this stage there is a small problem with your table design.

The table Inventory identifies each product in stock by an ID number and gives its description, price (per unit) and the quantity in stock at the beginning of the day.

This is not a good way to handle inventory. Inventory is basically a calculated quantity, and in versions of Access prior to Access 2010 should not be stored in a table.

It would be beneficial to read this article:
http://allenbrowne.com/AppInventory.html

 

[MonteCristo]

Thank you. But this is how I got it when I bought everything. Until I redesign the database, can I solve my issues? Temporarily?