[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

inventory table with a running total

Posted on 1999-11-04
9
Medium Priority
?
357 Views
Last Modified: 2008-03-17
I have 2 tables.  One table is an inventory table and has two fields
"tblItem name" and "tblItem quantity".  The other table which is called "tblInventory Out" is a record of all inventory items that are given out to users and has 7 fields which are "Description", "Quantity", "Date Out", "Date In", "Dept", "Signed Out By", "Signed In By" you probably don't need all these fields but they are  there if you do.  

What I need to do is when an entry is made in the Inventory Out form in the  field name description  say "Printer" and in the quantity field  "4" I want to be able to automatically deduct 4 from the quantity field in the Inventory table and keep a running total of that item in the Inventory Out form. So when a user makes an entry, the information is read back to them immediately. Such as if there were 20 printers now there would be 16 listed.  As an added attraction it would be nice if the user was notified if there were not enough of an item or the item requested is gettin low in stock.

Thanks  

Charlie
0
Comment
Question by:charliebcc
  • 5
  • 4
9 Comments
 

Author Comment

by:charliebcc
ID: 2184552
Adjusted points to 100
0
 
LVL 7

Expert Comment

by:JimMorgan
ID: 2184741
Hi Charlie:

This looks fairly simple to do.  For your inventory out form, make the records behind the form a query which joins the inventory table and the inventory out table by common field.

At the top of the form put the total quantity available, which is the quantity field from the inventory table.

(I would also redesign the structure of these tables but that is not covered by the question.)

If you had another field in inventory table which indicated the low stock level, then when the item is selected, you can test against the low stock field and turn on a hidden label which says "Low Stock".

In the After Update of the quantity to take out, calculate the new quantity in the inventory quantity based on the out value.  

  With [TblItem Quantity]
    .Text = [TblItem Quantity] - InvOutQty.
    If .Text < 0 Then
       lblNotEnoughStock.Visible = True
       .Value = .OldValue
       InvOutQty = 0
    End if
    If .Text < QtyLow Then
       lblLowStock.Visible = True
    End if
  End With

This is just a rough cut and will need some tweaking but since I don't know your DB, this should get you started.

Jim
0
 

Author Comment

by:charliebcc
ID: 2199223
Hi Jim:
Sorry for the delayed reply but I was on vacation and could not get to my email.  Could you be more specific about changing the structure of the tables.  Nothing is wriiten in stone and I can change them if it would help.
I am also a little foggy on what kind of common field to use for the query.

Any suggestions would be appreciated.

Charlie
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 7

Expert Comment

by:JimMorgan
ID: 2199559
Was it a nice vacation?

Essentially a PartID is an example of a common field to pull all of this together.  Dealing with parts and inventories, to flesh out the basics that you need from a structure standpoint is something like this:  (only a suggestion)

    Table        Key Field     Comments

    Parts        PartID        Static information describing a part.

    Transaction  TransID       Also has a PartID to link back to the parts table.
                               Dynamic information regarding parts removed from inventory.

    Inventory    PartID        Dynamic quantities on hand (and on order, etc.)

Parts: the PartID is the key to link this information to other tables.  PartID is not important to the user.  Strictly an internal identifier.  Set it up as an AutoNumber field and let Access assign the numbers.

Transactions: The TransID makes each transaction unique.  Helps in indexing.  Should be an autonumber field.  The PartID field, Long number, links to which part and inventory record the transaction belongs.

Inventory:  The PartID is a unique key field.  It is just a Long number.  This could be put into the Parts table but it probably is better to keep it separate.

From a relational viewpoint, a part can have multiple transactions but only one inventory record.

Inventory Table                        Parts Table                     Transaction Table
   PartID <1-------------------------1>  PartID  <1------------------Many>   PartID
          Enforce Referential Integrity         Enforce Referential Integrity
          Cascade Update                        Cascade Update
          Cascade Delete                        Cascade Delete

In the underlying query for the form where you add tranactions, you would use all three tables in order to link the transaction to the inventory quantity.  This will allow you to see the PartName, PartNo, Description, etc.  Make the entry and then update the inventory quantity.

When you enter parts in the Parts Table, Access will automatically create an entry in the inventory table.  I'd do this with a form and query as well so that you could enter the initial inventory levels.

Thats quite a bit to digest tonight.  Look at it, think about it, and come back with more questions, if you have any.

Jim

0
 

Author Comment

by:charliebcc
ID: 2201699
Dear Jim:

Just read your message and you are right.  I will get back to you after I digest these well written instructions.

Thanks

Charlie


0
 
LVL 7

Accepted Solution

by:
JimMorgan earned 400 total points
ID: 2201944
Charlie:

Looking forward to it.

Jim
0
 

Author Comment

by:charliebcc
ID: 2205469
mission accomplished.

ready for the next part.

thanks

charlie
0
 
LVL 7

Expert Comment

by:JimMorgan
ID: 2205642
I assumed that 'mission accomplished' meant that this question was answered.  However it was unclear with the next remark 'ready for the next part'.

So rather than answer I just gave this comment.  Any time that you feel that I have given you enought to solve your question, you can always make any of my comments as an answer.  There is a click point on the right top of any 'Comment' title bar.

Jim
0
 

Author Comment

by:charliebcc
ID: 2205686
I got it.  I wasn't sure if there was a next part or not.  I think I have enough to do what I need.  If not I'll be back.  Thanks very much for your help.

Charlie

0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

640 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question