Stock\inventory Control

[June7]

You do an aggregate (GROUP BY) query to sum Quantity by ProductName. Access Help has guidance on using query designer to build this query.



I am afraid you are missing my point which is would NOT 'subtract the Quantity from the product table'. Trying to maintain an aggregate (net balance) value in a table is problemmatic and loaded with potential for data errors.

[tweety]

thanks for the above post, finally got it to do a sum of the quantity, i tested it (1 customer purchase 2 of all, 2nd 2of all products and 3rd 6 of all so 10 in total) its working.

hm can you explain alittle more what i would go about doing the next bit, i dont understand......

please advice
thanks in advance

[June7]

Your relationship diagram shows table for SalesDetails and table for PurchaseDetails.

Do aggregate query on each that sums quantity by product ID.

Join those two queries on the product ID.

Create a field with expression in that query that subtracts each product quantity sold from each product quantity purchased. This is really a very basic calculation with fields. Simply: Field1 - Field2

[tweety]

I have made to queries one that sums the quantity for purchased orders from suppliers and the second that sums the quantity for the sales to customers. the problem is i dont know how to go about joining the together, do i create a 3rd query? i tried that and the wizard says i cant add them so i tried using the design view

i cam accross this coding on the net "Stock:[Query1].[Quantity]-nz([Query2].[Quantity],0)" from http://bytes.com/topic/access/answer...-access-2003-a

but can not apply to my database due to lack of knowledge.

this coding it that what i need? what query do i put it into or should they be a 3rd query setting up???

thanks for your help so far much appreciated

thanks again

[June7]

Yes, another query. Use query designer. Queries can be joined just as tables can be. That expression looks appropriate. Can be in the query or in a textbox on report.

Building queries by joining tables/queries and calculating with fields is basic Access functionality. Access Help has guidance.

[tweety]

Hm can you please send me a link to follow for some more guidance, i cant get my head around this

thanks

[June7]

Does this help http://office.microsoft.com/en-us/ac...005188027.aspx

[tweety]

I just done that, does it seem right???

[tweety]

is the next bit to use a update query to write the new stock to the products table?

[June7]

Shouldn't you subtract Sold from Purchased?

This query does not need to be an aggregate (GROUP BY). Just join the two queries on ProductName. Do you know how to link the two in query designer? http://www.opengatesw.net/ms-access-...cess-Query.htm

If you want to update products table, yes run update. The main point I have tried to convey is that Is Not Necessary. The product stock balance can be calculated when needed by these queries.

[alcapps]

looks like you need to use the grouping.. in the design ribbon there is a sum symbol.

use group for the first two columns and sum for the third column..

that should get you 14 tubs..

Hope this helps.

sorry didn't see that june7 had answered your post..

[tweety]

If i set it like that i get minus figures?? thats why i changed it lol

i joined the 2 queries using the link provided - testing it explained it

i spotted a problem, while using this this method, there will always be a chance i have left over stock therefore i dont purchase stock, and the query just created shows purchased items and doesnt read the items in stock from the stock table, who do i go about that?

also where and how do i use the update query, do i set it in this query?? if i do that the total row disappers?

[tweety]

looks like you need to use the grouping.. in the design ribbon there is a sum symbol.

use group for the first two columns and sum for the third column..

that should get you 14 tubs..

Hope this helps.

sorry didn't see that june7 had answered your post..

Hm i didnt understand why i need to change the grouping for the first 2 columns and change the 3 columns to sum, i did try but it didnt change the result, can you explain a bit more please

thanks

[June7]

If Purchased - Sold results in negatives, then you sold more than purchased?

I am not sure Al's recommendation is needed if you already did the grouping on the first two queries.

I don't understand the question about stock table.

UPDATE query would be executed from some event, possibly button Click. Again, if you pursue this avenue of saving aggregate calcs you run serious risk of data getting 'out of sync'. There is a chance the UPDATE action could be accidentally repeated or forgotten. I DO NOT recommend saving aggregate calcs. If you do want to maintain an aggregate stock balance value in table then this value should ideally be adjusted EVERY TIME a transaction (purchase or sale) is processed not from this aggregate query. I proposed the aggregate query to get you away from the idea of maintaining a stock balance table.

You might review threads by burrina who also has chosen to save aggregate calcs.

[alcapps]

In query 1 stock.. should be an expression not a group by.

change sumofquanity in both 2 and 3 to sum instead of group by.

you can join the two tables by product name. you can hide the dates by not adding them to query1.

add another query for productname and quantityOnHand. so stock would be qtyOnHand - (qtySold + qtyPurchased)


not sure what the difference is between qtyPurchased and qtySold... to me anything purchased is sold. But hey that for you to know.