Totals query

[nicwnacw]

I have very limited knowledge and I need to extract data from sales transactions so that I can update stock levels. I can do the extraction bit with StockID , stocktransactionid and quantity in a query. This results in a list of all sales, some stock lines have been sold on more than once.

What I can't seem to do is get a query that will add all the sales for each stock line, once I get this I can update stock levels. Any suggestions? preferably with an explanation so that I learn as well. Thank you in advance.

 

[Rich]

Controlling stock levels in this way isn't recommended, see if this helps
http://users.bigpond.net.au/abrowne1/AppInventory.html

 

[nicwnacw]

stock calculations

Thanks I will study that (when the rug rats are asleep), the problem is that I am doing this db for a builders merchants and they need to know what stock (in the yard) is available when customers ask. This will save them going to the yard to look! thus saving lots of time, they also have corporate customers phoning large orders in and need to know if stock is avaliable. So stock needs to be updated after every transaction.

I think I can do this with macros etc, I am going to try to embed the stock transactions form into a stock levels form and link stock id and see if that works.

I would rather do this on excel - it's easier but the customer wants it on access

 

[Rich]

Unless you gain very advanced coding skills I suggest you use the method suggested.
Using Macros to do this is a recipe for disaster

 

[neileg]

The reference supplied by Rich is spot on. It succinctly sets out exactly why you should not do what you say you intend.

Your previous experience in Excel gets you off to a bad start in Access. In Excel, the data storage is mostly identical to the data display. In Access the data storage is mostly distinct from the display.

Your requirement to show the current stock is not in conflict with the methods outlined.

If you want to build a simpler application, consider holding stock inwards and stock outwards transactions in the same table, but as negatives and positives. Run a totals query that sums these transactions, and you get your stock value. But to get a proper, robust application, this won't be enough.

Beginners in database programming seem to think that inventory control is an easy place to start. It's not. Once you've written an inventory control database, you'll have learned an awful lot of programming!

 

[nicwnacw]

totals queries

I am finding that out the hard way - still it's good to learn. That ref is very complex and as you might have guessed I have no VB or SQL skills at all, do I just copy it out ? changing field/table etc names?

 

[neileg]

Do a search on this forum for inventory or stock control. The subject comes up frequently. Read as much as you can.

You may be short on SQL and VBA skills now, but you won't be by the time you are finished.

There is a wizard with Access that will build you an inventory control system at the click of a button. This might give you some ideas.

 

[nicwnacw]

TOTALS QUERY

I have been using both access templates (order and inventory) extensively, but not that easy to take apart if you don't 'do' code!

Have got all working so far - sales, invoice etc.

Thanks for the help and advice, one more small thing this has to be networked will it all be Ok if 2 people use it at once?

 

[neileg]

Yes, Access is built to be used in a multiuser environment. It won't support large numbers of users, but two is no problem.

 

[nicwnacw]

totals query

Phew - that's a relief Thanks again.