Using MS Access/Access 2007 Query

Advertisement


Question
QUESTION: Hello and thank you for your time.

I have a question about something i've tried to find a solution to but could not find one. This is because I'm a beginner when it comes to Microsoft Access.

My database is set-up as follows:

There's 5 tables named: Customers, Orders, Products, SupplierProducts, Suppliers.

The customers table contains all the information about customers (name, address) and it's only notable field is CustomerID (which is the primary key).

The Orders table contains the following columns: OrderID (Primary key), CustomerID (Foreign key), ProductID (Foreign key), Amount, Date. This is where orders placed by the customers are handled.

The Products table contains: ProductID (Primary key), Product Information, Price, Stock, Ordered (from the supplier, but not yet delivered by them).

The SupplierProducts table contains: ProductID (Our product ID, this is also the primary key), SupplierID (Also a primary key), SupplierProductID (Their product ID), SupplierProductDescription, Price. This is the table that will be used if I order new products from the supplier for restocking purposes.

The Suppliers table contains the details of the supplier, the only notable field is the SupplierID field, which is the primary key.

Table relations:
Referential integrity is enforced on all relations.

Customers (CustomerID) is linked to Orders (CustomerID) by a One-To-Many link.
Products (ProductID) is linked to Orders (ProductID) by a One-To-Many link.
Products (ProductID) is linked to SupplierProducts (ProductID) by a One-To-Many link.
Suppliers (SupplierID) is linked to SupplierProducts (SupplierID) by a One-To-Many link.

My question is:

When a customer places an order in Orders.
How do i make it consult the Products table and specifically, the 'Stock' column. Because it should not be possible for a customer to order a product that is not in stock. And how do i make it possible so that with every order, the stock in the Products table will drop by the amount specified in the order.

I hope I gave you enough information to help you understand how i built my database and what I'm trying to achieve.

Thanks in advance,

Logan

ANSWER: Sorry for the delay in responding, I thought I had sent an answer to this.

First, You need a Transactions table:

Transactions
TransactionID (PK Autonumber)
OrderID (FK)
ProductID (FK)
Quantity
UnitPrice
TransactionTypeID (FK)

and a TransactionType table:

TransactionTypeID (PK Autonumber)
TransactionType
Direction (either incoming, Outgoing or Neutral)

Your Order table should be changed to eliminate the ProductID and Amount fields, since they will be handled in Transactions.

The next piece is you don't update the Stock field in your Products table. Quantity on Hand (QOH) is calculating by adding all the incoming transactions and subtracting the outgoing ones. The way I do this is with a query like this:

qryAdjQty
SELECT ProductID, Quantity, Direction; IIF(Direction = "N",0,IIF(Direction="O",Quantity*-1,Quantity)) AS AdjQty
INNER JOIN on TransactionType.TransactionTYpeID = Transactions.TransactionTypeID
FROM Transactions, TransactionTypeID;

Then this query feeds another:

SELECT ProductID, Sum(AdjQty) AS QOH
FROM qryAdjQty
GROUP BY ProductID;

This query (call it qryQOH) will show the QOH for each product.

So, if you want to check whether there is enough product to cover an order, use the BeforeUpdate event of the control where you enter order quantity:

If DLookup("[QOH]","qryQOH","[ProductID] = " & Me.ProductID) < Me.Quantity Then
   MsgBox "Insufficent Stock available!"
End If

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Thank you so much!

However, i'm a complete beginner.
I wouldn't even of taken on a project this difficult if i was given a choice.

From what i see, the Transactions table should be placed in between the (adjusted) Orders and Products tables when it comes to relations. The TransactionTypeID seems to be solely linked to the Transactions table. But the direction field, does it have to have any special characteristics? My database only needs to handle orders by customers (who are simulated by me). It's a database that will only be presented to a few people and proceed to never be used in real practice.

So i don't update the Stock field. And I use it for my queries.

You've already helped me so much. But I don't even have any idea where to put a query. Or how to activate them.

Could you explain this to me? Once I understand this, I'll pretty much be able to finish the whole database.

Thank you for your time,

Logan

Answer
1) I used "I", "O" and "N" as values for Direction (Incoming, Outgoing and Neutral). But you can use anything you want. It could be numbers full words, whatever. The point is you need to know the direction to determine the Adjusted quantity.

2) The Transactions table is related to Orders via OrderID and to Products via ProductID. Also to TransactionType via TransactionTypeID.

3) You can't have an app that shows QOH unless you record ALL transactions, incoming and outgoing. Otherwise your QOH will eventually reach zero.  So you need to record incoming quantities as well.

4) you create a query using Query Design mode.


Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.