Windows 7 64bit 
Access 2013
Hello everyone!
I am new to Access so please forgive my ignorance.I am in the process of creating a database using Access 2013 for a friend of mine who started a business selling products. Currently the only way she sells these products is going to an Event, setting up a table and selling to customers who walk by. At some point in the future she will be setting up a website where a person can order a Pack, and this pack will contain different products. That customer will sell those products, then return any remaining product and the money made. I have created a table for the products she currently has on hand, a table for the products being sold at an Event, a table for the products going into the Packs, a table for the products being returned from the Packs and a table for purchasing new inventory. Herein lies my problem, I would like the inventory totals to update to give current inventory on hand after sales and new inventory. So basically what I'm looking for is this:
Current Product on Hand - Product Sold at Event - Product going out in Packs + Product being returned from Packs + Purchase Orders Received = Current Product on Hand.
My understanding is I need to create an update Query then create a form. I have no problems creating queries and forms, my problem is the calculations. I created a query that counts the current product in stock, the qty of product sold, the qty of product going out in the packs and the formula to calculate the remaining products in stock and even got it to run correctly returning the correct answer. But, when I try to add to this query the products received from purchases, and products returned from the Packs...to make a long story short, it doesn't work!
Instead of creating an update query to update my inventory, can I just as easily use calculated fields and macros in my forms to update in the inventory totals as new records are entered into the database? Or do I need both?
I am not a programmer, so I've been using the expression builder to help me with the calculations. I am just not sure what I'm missing here. Can someone please give me a suggestion on how to proceed with this so that I can keep a current running total of the inventory on hand?
After hours and hours of internet research and reading books and watching tutorials on database design and queries, I have yet to come across an example of anything coming close to what I need. I have been headbutting my monitor for about 2 weeks now trying to figure this out, and I'm just not getting it! I know once the answer is presented to me, I'll slap my forehead and say "duh".
Thank you very much for your time!
Reply With Quote
