Automatic update queries

[tyrannitar]

I am trying to build an Inventory database. One query, an inventory list, will contain the items that have been received as sorted by name as the primary key, along with quantity, unit price and extension price. Another query, a receiving query, will then be used to update it. When I update this other query with an ItemID, name of the item, the quantity, the unit price, and the extension price, I want this quantity and extension price to be added to the quantity and extension price fields within the original query. The original query will then calculate the unit price, which should be simple enough (extension price/quantity in the original table). Another query, a delivery query, will do the opposite of this. Instead of adding quantity and extension price when the record is added to this table, the quantity and extension price will be subtracted.

For instance, if I buy 1 pen, I’ll enter the name, quantity, and extension price on the receiving query. A record on the inventory list query will then be updated with this information. Next, if I buy 2 pens for $3.00 each, with a total price of $6.00, then the inventory list will update to show that I now have 3 pens, with a total extension price of $11.00 and a unit price of approximately $3.67. Now, if I were to update the delivery query with a new record, where I take away 1 pen that costs $3.67, the quantity of the Pens will be reduced to 2, their total extension price to $7.36 and the unit price to $3.68. I hope that’s a clear example; it’s hard to describe it well without diagrams.

Is something like this even possible? How could I go about doing this? Would I have to use VBA/SQL? I really don’t know what to refer to in trying to find self-help for this situation. Any help on the matter would be greatly appreciated.

 

[Nicolette]

I'm sort of new to access but I have been developing an inventory/sales/order tracking etc database and I use forms to enter information into my tables. My understanding of access is as follows this may not be completely correct but...

Tables - Store data
Queries - manipulate data from tables for use, perform calculations and are also used as sources for forms and reports
Forms - allow for data input and other such things
Reports - display the data

Then there are macros and modules to automate processes

like I said I'm sort of new but that is how I have been keeping all the objects and their uses straight in my head.

I hope this helps some!
Nicolette

 

[tyrannitar]

Yes, those are useful tips. What I'm trying to accomplish goes a bit beyond that, though. :/ Thanks anyways.