table expressions

[topgun43031]

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. [sig][/sig]

 

[Robertd]

Hi topgun43031,

Why!

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.

HTH
Robert
[sig][/sig]

 

[topgun43031]

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,
topgun [sig][/sig]

 

[Robertd]

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

X

' 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

X
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.

HTH
Robert [sig][/sig]