I am setting up a sales and inventory management database, but being a novice at using Access I've encountered the following problem:
I have three tables, Categories, Items, and ItemSales.
Categories is comprised of CategoryID and CategoryName
Items has a number of fields relevant to the item to be sold, including CategoryID and the number of items originally in stock
ItemSales basically stores items that have been sold, price, number of items sold, customer, etc.
I also have a queries that sum the number of items sold in each and determine how many items are left in stock.
My problem is this: I want to display a form that allows me to selet the category of merchandise I want to sell, then shows what items are in that category that have not been sold out, and then, after selecting which item(s) I want to sell, allows me to fill in the selling price and number of items sold. Ideally, the form would allow multiple rows for purchase of multiple items, would total each row and total the sale. Upon hitting a submit button, the results would be added to the ItemSales table and the number of items in stock in the Items table would be updated. At the same time, the submit button would open an iinvoice for printing.
If this could happen all on one form, that would be great. If not I'm open to suggestions as I've been able to put together pieces of the puzzle, but not the whole thing.