Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

table expressions

table expressions

table expressions

i am trying to have two hearers in one table be responsive to each other...ex:
iam putting together a inventory program and in the same table i need theSUM OF PARTSOUT subtract from the SUM of RECEIVED.

RE: table expressions

Hi topgun43031,


are "SUM OF PARTSOUT" and  "SUM of RECEIVED" fields if so they can be calculated in a query based on the table, the query can then be the data source for a form and/or a report.

it's not good practice to store calculated fields in a table (you have to sometimes)

if "SUM OF PARTSOUT" and  "SUM of RECEIVED" are the result of a query you could use the query to update the current stock level field in the table.


RE: table expressions

Hi Robertd
what i am designing is a perpetual inventory program, and on one entry form i
have a box for Received and a box for parts out.what i want it to performe is when
i enter a number into parts out ,i want it to subtract from the receive sum.
does that make sence? let me know, thanks,

RE: table expressions

Hi Topgun,

OK,it's just "Sum of xyz" is the default data item name for "totals" columns given by access when you use a groupby query.   

now doing this on a from has some pitfalls the basic method isn't hard in the afterupdate event for the parts out data item add some code like this


' this will depend some stock can be sold
' by fractions ie lengths of hose or wire
' if these are only "whole Items"
' use a "long" data type instead of a double

dim tempPartsRec as double
dim tempPartsOut as double
' get the data items
tempPartsRec = me.[SUM of RECEIVED]
tempPartsOut = me.[SUM OF PARTSOUT]
' apply new total to [SUM of RECEIVED]
me.[SUM of RECEIVED] = tempPartsOut - tempPartsRec

now this works fine until the user modifies the value then uses the "Esc" key which un-does the change to the field item but not the calculated change.

also this doesn't make it a pepetual record of parts in vs parts out.

I'm thinking there might be a beter way of doing this.
i take it "Sum of Received" is the inwards parts say from your supplier's invoiced deleveries and "Sum of PartsOut" is your sales invoiced out items. no doubt you have a "Stock" table listing the Items you have in stock.

if so and from what i understand the stock qty should indicate the balance ie.

Stock.Qty = Sum([SuppInv].[Qty]) - Sum([CustInv].[Qty])

This uses three tables one for Stock, one for SuppInvoiceItems and one for CustInvoiceItems
(actually a "minimum" of 5 tables Parent Supplier and Customer Invoice tables also)
this way the Stock.Qty can be batch processed using an update query after the "inwards" Supplier invoice has been processed and then again after the "outwards" Customer invoices are processed. this gives you a pepetual record of Stock items in and Stock items out against the Customer and Supplier invoices and a current balance.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close