Question Stock control system

[hayeutino]

Hello there
I am a newbie for Access . I wanted to develop a stoke database in access 2007 .
my table consists of the following :
Receiving ID ,Item , stock in , stock out , Balance .

Receiving ID Item Stock in Stock out Balance
001 Keyboard 1000 0 1000
002 Optical Mouse 1000 800 200
003 Keyboard 0 600 400
004 Keyboard 4000 0 4400
005 Keyboard 0 2000 2400
006 Optical Mouse 1000 0 1200
007 Keyboard 600 0 3000

Please help me how to do this simply

 

[Pat Hartman]

Welcome Aboard
The simplest method is to create a product table that describes each product and a transaction table that records inventory movements - in and out. You would sum the transactions to get the current balance. I use natural signs in my transaction tables so I don't need logic to determine if I am adding or subtracting. I just add.

You can use union queries if you want to create reports to show monthly activity. The first query in the union would sum the activity up to the start of the month and that would be the beginning balance. The second query in the union would select the activity for the given month.

 

[hayeutino]

Pat Hartman Is it possible to have a sample . My problem lies when the item is the same the database should find the last record Balance and Add with stockin or subtract from stockout respectively to find the latest Balance .

 

[Pat Hartman]

I don't have any applications that use that method to manage inventory. My applications use transactions because they can be audited. Direct updates cannot. Take a look at the templates available on the MS site. I don't know if they do it either but they may.

 

[richardwilder]

Does the Northwind demonstration database from Microsoft not give you what you require?

 

[hayeutino]

I just wanted to compare text entered in a form to a record in a table and if it is the to display the result in another text box . Thanks in advance

 

[hayeutino]

@Pat Hartman I just wanted a custom autonumber in access 2007 like this "HQ001" , "HQ002" and so on in a form that increments everytime a user saves a data in the form .
Urgent please .

 

[John Big Booty]

Have a look at the attached sample. It demonstrates how an order number that increments by office and by day. You should be able to turn the principals demonstrated to your own ends

 

[hayeutino]

@John Big Booty
I Will look at it and get back at u . But for now thanks so much .

 

[hayeutino]

I am back again @ John Big Booty What if i wanted to take two letters from the combo box instead of 1 letter .

 

[John Big Booty]

Have a look at the unbound Text box. This is the code that is populating it;

="C" & Left(Combo4.column(1),1) & "-" & Format([OrderDate],"mmyy") & "-" & Format([OrderNum],"00")

You just need to adjust the arguments in the Left() function.

 

[hayeutino]

Thanks for providing me with such a great example . But can I ask another question based on ur sample database u gave me . What if I entered the values I want in the combo box Office Loc instead of selecting the row source from the Table . Thanks in advance

 

[John Big Booty]

I don't believe that change the code provided, but by doing that you would be sliding towards de-nomalising your data, as well as making it harder to update your DB in the long run.