Stock\inventory Control

[tweety]

Hi
i have a database that hold sales details, i would like to add stock\inventory control to it.

I have tables set up that hold the products (this has a quantity field)
and a form that allows the user to add the amount sold or purchased.
what is the best way to up the product table after the user enters amount sold or purchased

what is the best way around it, or what is the theory, i have had a look online and tried to look at other database, but didnt get anywhere

I have attached images of the table, form and how the tables are links, if needs be i can upload the database too

Thanks in advance

[June7]

See if this offers ideas http://office.microsoft.com/en-us/te...010206882.aspx

[tweety]

i tried to look at that thanks, but cant understand that, the database iv designed is for a project, can you walk me through the theory please

thanks

[June7]

What do you mean 'designed for a project'? I am using Access 2010 and can't download that database now.

In theory, with a relational database, transactions of incoming and outgoing product are summarized and subtracted to determine net balance.

[tweety]

sorry i meant the dabase iam working on is a project that needs to go live in 3 months! iam using access 2007. sorry

[June7]

Inventory type database is common topic in forum. Search forum or google.

What don't you understand about the template database from link?

Basically, do a query that calculates sum of product purchased, another query that calculates sum of product sold. Build a query that joins those two and subtract the sum of sold from sum of purchased.

[alcapps]

I agree with june7

One thing that you can think about is to create a history of product sales table. At some point your sales table and purchase tables will get huge and calculations will slower and slower. You can have a process that summarizes product sales and purchases on daily or monthly table. There are issues with reconciliation. But it is never to early to start thinking in those terms.

[tweety]

i didnt understand the database as i couldnt see any queries in place or coding to add or minus stock etc, from the above post ill try to do what you mentioned

thanks

[alcapps]

june7 has given you the answer in an earlier post.

Basically, do a query that calculates sum of product purchased, another query that calculates sum of product sold. Build a query that joins those two and subtract the sum of sold from sum of purchased.

do you understand how to build these queries?

[June7]

I explored that Inventory database a little. The objects are organized by 'Inventory Navigation' and the tables and queries are under 'Supporting Objects'. You can change this display by selecting alternative from the Navigation Pane dropdown list.

Look at the query Inventory Transactions Extended, the field Actual Quantity is an expression that sets a transaction value as positive or negative. Then the aggregate query Inventory Stock Levels sums that field. This shows one way to calculate net balance when all transactions are in a single table. In your case, transactions are in separate tables - SalesDetails and PurchaseDetails. Build an aggregate query on each table, join those queries on ProductID, subtract product sold from product purchased.

[tweety]

when does the quries run? and whats it based on? will it run once i enter all the sales in the form? (On form exit??)

Thanks

[June7]

Are you talking about the sample database queries? Open query in design view to examine structure and see what it's based on. I expect it is used as the RecordSource for a report. Open report and query 'runs'.

Or are you talking about your queries yet to be built? The summation query would be RecordSource for report. Open report and query 'runs'.

[tweety]

hm, just so we on the same page, i should look at the sample database (posted on 27.1.13 @8:30pm) and see how the query is set up, then apply that to mine?

my database has a report (1 for sales and other for purchase orders) that display the total amount sold\purchased after selecting a date. so i need one like that, so it can add all items sold (in my example sweets) once the query gets that information, do i use a update query (or some thing) to update the stock?

thanks in advance

[June7]

The query in the sample db won't be exactly what you need to do because your transactions are in two tables. As already stated, you need to do a summation query for purchases and a summation query for sales. Join the two queries and calculate difference of purchases and sales. With this approach, there is no 'update of stock'. The stock on hand is the calculated difference. This is unrelated to reporting the purchases and sales occurring after a specified date.

[tweety]

i looked into this more and i cant use the report query as that displays a break down of what each shop has purchased or what purchases were made from each supplier
so i have made a new query with (see attachment please)


when i ran that it displayed


how do i count the total of all the tubs so the query shows 14 tubs???

there this is the next step to update these number and subtract the Quantity from the product table?

thanks