Thread: Stock Controlling Data base

Dear friends, i want make simple stock controlling data base on access.i have try to make that in excel ,but that's not matching with this work.I'm very new to access.so i need your help to do this with access.don't think I'm giving my work to u all.here i have attached my excel sheet.pls make simple sample sheet in access for me.thanks in advance..

edit -
I have attached my access database also.(zip file)

I don't think that anyone in the forum will be willing to spend the time to build a database for you as that appears to what you are asking. Suggest you learn the basics of relational databases and make an attempt and post back with specific issues. In the meantime, here is a link to MS templates and you might find an out of the box solution or one you can modify to meet your needs.

http://office.microsoft.com/en-us/te...001018458.aspx

I would start with this white paper on relational databases.

http://forums.aspfree.com/microsoft-...es-208217.html



Alan

I completely agree, and to be honest, I doubt someone building you a database would do you any good for very long. Requirements and needs for a database often change, and if you don't know how it works you'll constantly be needing other people's help. I even find that I'm often more inclined to build a new database than to try to fix/modify someone elses, particularily if that database was not very well built.

thanks a lot for your both replays.i have make small data base & attached here...my requirement is ..
01.after selecting item code in transaction table item name should auto file.(that name can take from item database)..
02.if i enter data to transaction table , that data should auto update item database..(base on item code).

actually what i want to do is one place i need to enter the data & that transactions should save in one place (properly item wise).

I need your ideas & suggestions to improve my stock control system.pls help me..
my doc.
http://www.mediafire.com/?x500clw7b70859c

Attach files to the actual post

Unable to open and read the database. Please make it backward compatible to 2007 or earlier to get full benefit of the forum.

I have looked at your database and have a couple of suggestions as a start.
In your database table, based upon what I have seen, I would only have three fields (unless there is more pertinent data associated with the product).

Database Table
---------------
ItemID (PK)
ItemCode
ItemName

In your transactions table, you would record all the ins and outs but not repeat any of the information in the Database Table as this would result in a non-normalized database.

Transactions Table
------------------
TranID (PK)
ItemID (FK)
TranDate*
Description
Received
Issued

*note here that the word "DATE" is a reserved name in Access and should not be used as a field name.

Because balance is a calculation, it should not be done in a table and only performed in queries and forms. Therefore you should then build a query joining the two tables on the ItemID to perform balance calculations.

Additionally, I found no form in your database. You should create a form for data input. You should not let users enter data directly into tables. I would do it with a subform for the transactions and use the query to give you a balance if you need to see it at data entry.

If you go to this link and scroll down to sub-forms, there are two good tutorials on this.

http://www.datapigtechnologies.com/AccessMain.htm

This should get you started in the right direction. Post back with additional specific questions as you move along. Good luck and welcome to the world of Access.

Alan

Originally Posted by alansidman

I have looked at your database and have a couple of suggestions as a start.
In your database table, based upon what I have seen, I would only have three fields (unless there is more pertinent data associated with the product).

Alan

thanks for your advice.i have make form & now i have another question.new data i want to enter base on item code.but that item code i have make as primary key.so it will not allow to duplicate.in this case what i have to do.pls check my attachment.

Please do not quote whole posts unless necessary!

In your transactions table you made the Item ID your primary key and not your foreign key. Add a primary key in your transactions table (make it an autonumber) and then use your Item ID in that table as a foreign key. In your form, have the transactions a subform of the Items as shown in the video.

Alan

Originally Posted by alansidman

In your transactions table you made the Item ID your primary key and not your foreign key. Add a primary key in your transactions table (make it an autonumber) and then use your Item ID in that table as a foreign key. In your form, have the transactions a subform of the Items as shown in the video.

Alan

I think now it's ok.final question is making a summary to get currant balance.pls help me to do that.thanks a lot for your all helps..

First of all, I would create an opening balance in the transactions table as Receipts and eliminate the quantity in the Items table. Next I would create an aggregate query to sum the receipts and issues by part number. I would then create a second query to add the sum of receipts less the sum of issues. Then when you want the on hand you will only have to run the second query. See the attached.

Alan

Here are four sites that you should use to help you learn about Access and queries.

http://www.databasedev.co.uk/general.html
http://www.bluemoosetech.com/index.php
http://www.datawright.com.au/
http://www.datapigtechnologies.com/AccessMain.htm

Alan