Using MS Access/Transaction Tables


I found your instructions on inventory transaction tables and had a question on how this table is actually populated.  It seems you need to use the Purchase Order and Sales Order details for the Incoming and Outgoing transactions, but I do not quite understand the mechanics of it all.  If you wouldn't mind elaborating on this portion of the process, I would greatly appreciate it!   Thank you!!

ANSWER: The way I generally do it is with ONE transactions table. This table replaces both the Purchase and Sales details tables so you have one table that records ALL movement of stock.

You could use separate details tables if you want and use a Union query to pull them together do do the AdjQty query that I speak about.

Does that help?


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

QUESTION: Ahh, so how to you populate the Sales Order Detail Sections and PO Detail sections?  Does your transaction table include both the PO ID and the SO ID fields?  Oh and how to you handle Physical Inventories?  I have read where some developers use the Physical Inventory quantity as a starting point for calculations.

Thank you so much for your help!!

I thought I had answered this.

The way I do it is with one parent ID foreign key. So it will store either the PO or SO ID. I then use the TransactionTypeID to filter depending on what parent I'm joining to.

As for physical inventory, I will store that with the Item record along with an effective date. So I filter out transactions prior to the effective date of the last physical inventory.

Hope this helps,

Using MS Access

All Answers

Answers by Expert:

Ask Experts




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


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

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

Brooklyn College BA

©2017 All rights reserved.