Inventroy totals

[sprtrmp]

Alright Access Gurus... I am trying to iron out the LAST problem with this db I am working on for tracking inventory and issue of equipment through my office. now that I am THIS CLOSE, I seem to be running into the biggest problem of them all with trying to track totals of each item I have in the office.



I have a form (frmAssign) that is related to a query(qryAssign) and a table (tblAssign) sorry for the lack of imagination. the purpose of all of these is to allow me to -you guessed it- ASSIGN equipment to each employee. I have a table called tblProducts that lists the products, etc that I issue to them, as well as the total number of each item that I currently have in stock in the office.

when the equipment is issued, I would enter the item and quantity being issued in a control (quantAssign) on frmAssign, and when I hit the button, the number I have just assigned to them NEEDS to be subtracted -somehow- from the total quantity (on tblProducts) so that when the next dude comes in, I can see at a glance the TRUE quantity that I have in stock.

I understand not wanting to truly SUBTRACT that assigned quantity from the total (especially because for all intents and purposes, the equipment is not "DISAPEARING" as much as it is just being MOVED temporarily... I will most likely get it back at some point and new equipment will be issued).

I light of this, it seems that I need to use some kind of DSum() or something to total each item then subtract from the overall quantity and use the result as my new quantity, but I am absolutely lost as to the best way to do it.

Thanks for the help, and let me apologize in advance for what you are about to get yourself into
Mike

[orange]

Have you reviewed Allen Browne's article on Inventory? It's good starting point and reference.
Good luck.

[sprtrmp]

yes, I have seen it. I have to admit, its a bit beyond my reach and creates more questions than it answers for me at this point. I have read it a couple of times and am still trying to digest it.

[June7]

Conventional approach is to enter transactions of product received and product used. Then do aggregate queries that sum the two types of transactions. Subtract used from received to get net difference.

That's essentially what Allen Browne's article demonstrates.

Are your 'products' consumables or long term assets? You said 'will most likely get it back at some point'. Perhaps a check in/out type of db would suit you - such as demonstrated by MS Lending Library template.

[sprtrmp]

that is essentially where I have been headed: on my form, I have a combo box from which the product being assigned is selected. I am using the value selected in the combo box as criteria for a query that sums the quantity of all the instances of whatever product ID is represented in the combo box, subtract that number from my original total quantity, and will eventually display THAT number as my "Quantity Available".

still working o it though, and still trying to decide if it is better to use the query method or some kind of DSum. (sometimes there is too much information out there).

Basically, everything I am dealing with essentially gets "loaned" to people that work here. Eventually, it will get returned as it gets worn out (might be 6 months, might be 10 years). when it does wear out, I take it back, replace it to the employee, and send the worn out stuff to another location. Theoretically, the stuff never "leaves", it just gets shuffled from one piece of paper to another.

[June7]

Sounds more like check in/out.

[sprtrmp]

I have the db where I want it, it works pretty well for what I need it for, just need the math to work for the quantities

[June7]

Don't really understand issue. Build aggregate queries. Join queries. Do subtraction of fields.

Or build report that uses Sorting & Grouping features with aggregate calcs in group/report header/footer sections.

If you want to provide db for analysis, follow instructions at bottom of my post.

[sprtrmp]

ALSE2 - Copy.zip
here goes: my problem is with frmAssigned. on that form, the user would select the size/description of the item being issued, then input a quantity and click on the "+" button. the new record is added to the list on the subform at the bottom.

above the input textbox for the quantity, there is a label that populates with the quantity from tblProducts once the size/description is selected. this is the "overall quantity" (what I started with), which is not what I really want to show.

What I NEED to show is the quantity after all of the quantity of that item have been subtracted from the overall total... also need help figuring out how to calculate that total assigned. I started messing around with a query (qryQtyRemain), but haven't gotten it where I need it yet.

Db attached, Any help would be appreciated

[June7]

Usually, a form can do data entry/edit for only one table. There is no need for tblProducts and tblProductCategories to be included in the main form RecordSource. For now, just bind form to tbleAssigned.

The subform should be locked against editing because Category and Description textboxes are bound to fields from tblProductCategories and tblProducts. An alternative to subform may be listbox.

So you need to do a sum of products in tbleAssigned and subtract from the Quantity field? How do you want to account for items returned? Why would records in tbleAssigned have 0 QuantAssign? You don't want to record transactions of product received - you will manually adjust the Quantity value when an item is received?


Might want to remove first 'e' from tbleAssigned.
I would change Last, First, Last4 textboxes to flat SpecialEffect so they don't look like input boxes.
I prefer Overlapping Windows to Tabbed Documents so I can see objects side-by-side in design view.

[sprtrmp]

So you need to do a sum of products in tbleAssigned and subtract from the Quantity field? How do you want to account for items returned? Why would records in tbleAssigned have 0 QuantAssign? You don't want to record transactions of product received - you will manually adjust the Quantity value when an item is received?

More exactly, I need to do a sum of whichever item is selected in cmbHRDescription (a sum of the quantity of items that are assigned and selected in cmbHRDescription... if that makes any sense at all), then subtract that number from the quantity on tblProducts for that same item. in other words, I need the user to see the number REMAINING, so they cant issue someone 4 of an item, if there are only 2 left.

as for what is returned, I will eventually add another form for those or add them to an existing table... that is stuff for the future, I need to get the actual assignment part up and running first, then I will go back and add the other part/parts later on.

the "0"s were a result of me closing the form before entering a quantity, I am learning as I go, and trying to concentrate on getting the parts that I feel are the most important up and running first. I would love to spend oodles of time getting the thing perfect, but I need it WORKING first.

all of the rest of your suggestions seem valid, and I will need to go back and take a look at each of them once the db is functional, but for right now, I need the math working first, but I DO appreciate the suggestions.

[June7]

Only approach I can think of involves domain aggregate function (DLookup, DSum, DCount, etc).

Include Quantity field in cmbHRDescription RowSource then modify ColumnCount and ColumnWidths properties.

Rename Quantity textbox to Available. Expression in ControlSource:
=DSum("QuantAssign:, "tbleAssigned", "ProductID_FK=" & [cmbHRDescription]) - [cmbHRDescription].[Column](2)

However, if you want to programmatically alter the Quantity value with the + button, this is what I (and I think Steve also) are trying to advise against.

Saving calculated data, especially aggregate data, is contrary to basic relational database principles. The saved calcs lose connection to the raw data because the saved values are not dynamic - they are not generated by aggregation of the raw 'transaction' records.

[sprtrmp]

It almost works perfectly!! there are 2 issues that I need to work out: first, it seems that if none of the selected item have been issued (ie, there are none of that item in tbleAssignd for the DSum function to subtract from the overall quantity), it does not display a quantity in the "Available" textbox.

second, when the "Available" textbox does update, and shows the quantity remaining, the number is a negative number, in other words, if there are a total of 15 of an item in tblProducts, and the DSum function subtracts 2 that are assigned, it displays -13 on the form. any ideas how to fix that?

SO CLOSE!!!

[orange]

If 15 -2 is showing up as -13, it seems your initial values are reversed--that is 2 -15 are in the wrong order.

[sprtrmp]

AW COME ON, YOU HAVE TO BE KIDDING ME!!! Duh, just forgot how math works for a minute
(how did I not see that? I am so glad this forum is anonymous... )