Using MS Access/Stock Control Questions

Advertisement


Question
QUESTION: I have to do a stock control database for school.
So far I have done tables and relationships.
The tables I have right now are

tblSuppliers
tblProducts
tblProductCategory
tblPurchases
tblPurchasesDetails
tblSales
tblSalesDetails

From what I've read i will need an additional transactions table along with a transactions type table and link it with the purchases and sales table?
Other than that am i missing anything?

The business is a retailer so i would like a way to have an itemized bill printed when a sale is made. How would i do that? Do i need to create a bill table? If so could you explain how i can do that?

Thank you.

ANSWER: No, you don't need an ADDITIONAL Transactions table, you need to replace your tblPurchasesDetails and tblSalesDetails with a transactions table. Purchases and Sales are transactions so your transactions table will record ALL movement of stock, either in or out. The transaction type will define in which direction the stock is moving.

Your tblSales table can be used to generate bills. Just make sure it has fields that you would need on the the bill. You would also need a Customers table (though you could use one table for customers and suppliers and just have a flag to indicate what they are).

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: Okay, I replaced those with a transactions table and put a transactions type to indicate whether its purchase or sales.

I'm not sure on how to do the bill part though. I need to have total, amount paid by the customer and the balance. I don't know where I should put these fields. Will it be in the sales table? The total and balance have to be calculated so how would I put that in a field?

ANSWER: Since Total and balance are calculations they should not be stored in a table. Your bill will be a query that joins the Sales record with the transactions records for that sale. If you ay get multiple payments for a sale, then you should have a separate Payments table.

So, on your Report, you would have a Control that sums the values of all items in that sale. You would have another control that sums all payments for that sale (I would use a DSum since its a separate table). Then you have controls, that subtract the total of payments from the total sale to get the balance due. Again these would be calculated on the fly.

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: How do I get the transaction type to automatically change to either purchase or sale based on what it is?
I have a purchase form with the transaction table as a subform and a sale form like that too.

Answer
You can use the Before Insert event of the subform to check the Parent form. Something like this:

If Me.Parent = "frmSales" Then
   Me.TransactionType = x
ElseIf Me.Parent = "frmPurchase" Then
   Me.TransactionType = y
Else
   Me.transactionType = z
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

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.