update another table afterinsert

[Jyan]

Hi, relative newbie question:

I've got a datasheet form which I'm using for stock in/out recordin
which adds a record for each stock transaction in a stock table (numbe
of stock in and out etc.) , I also have a total stock level field on th
products table - I want to add\subtract from the stock total in th
products table when I add a record to this stock transactions table i
the datasheet - what is the best method to do this? I was thinking do
sql update in code on the Form_AfterInsert event - am I doing the righ
thing

 

[Arvin Meyer [MVP]]

Rather than using a calculated field, which is not normalized database
behavior and prone to error as well. Why don't you use a query with a
calculated column:

Calculation: DSum("StockIn", "TableName") - DSum("StockOut", "TableName")

 

[Jyan]

Do you mean run an update query to update the stock total column in th
products table using the calculation shown?

;597682']Rather than using a calculated field, which is not normalize
database
behavior and prone to error as well. Why don't you use a query with a
calculated column:

Calculation: DSum("StockIn", "TableName") - DSum("StockOut"
"TableName")
--
Arvin Meyer, MCP, MVP
'DataStrat - Data Strategies - The DataStrat Solution
(http://www.datastrat.com)
'The Access Web - Welcome' (http://www.mvps.org/access)
'Access MVPs' (http://www.accessmvp.com)

Hi, relative newbie question:

I've got a datasheet form which I'm using for stock in/out recording
which adds a record for each stock transaction in a stock tabl (number
of stock in and out etc.) , I also have a total stock level field o the
products table - I want to add\subtract from the stock total in the
products table when I add a record to this stock transactions tabl in
the datasheet - what is the best method to do this? I was thinking d a
sql update in code on the Form_AfterInsert event - am I doing th right

------------------------------------------------------------------------
Jyan's Profile: 557
View this thread:
'update another table afterinsert - The Code Cage Forums (http://www.thecodecage.com/forumz/showthread.php?t=165626)

'Microsoft Office Help' ("http://www.thecodecage.com&quot

 

[Arvin Meyer [MVP]]

No, you do not need to store the stock total, ever. You can always calculate
it in a query, form, or report.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Do you mean run an update query to update the stock total column in the
products table using the calculation shown?

;597682']Rather than using a calculated field, which is not normalized
database
behavior and prone to error as well. Why don't you use a query with a
calculated column:

Calculation: DSum("StockIn", "TableName") - DSum("StockOut",
"TableName")
--
Arvin Meyer, MCP, MVP
'DataStrat - Data Strategies - The DataStrat Solution'
(http://www.datastrat.com)
'The Access Web - Welcome' (http://www.mvps.org/access)
'Access MVPs' (http://www.accessmvp.com)

Hi, relative newbie question:

I've got a datasheet form which I'm using for stock in/out recording
which adds a record for each stock transaction in a stock table (number
of stock in and out etc.) , I also have a total stock level field on the
products table - I want to add\subtract from the stock total in the
products table when I add a record to this stock transactions table in
the datasheet - what is the best method to do this? I was thinking do a
sql update in code on the Form_AfterInsert event - am I doing the right
------------------------------------------------------------------------
Jyan's Profile: 557
View this thread:
'update another table afterinsert - The Code Cage Forums' (http://www.thecodecage.com/forumz/showthread.php?t=165626)

'Microsoft Office Help' ("http://www.thecodecage.com&quot

--
Jyan
------------------------------------------------------------------------
Jyan's Profile: 557
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=165626

Microsoft Office Help

 

[Jyan]

Ah yes now I see! Many thanks for the advice.

;597777']No, you do not need to store the stock total, ever. You can
always calculate
it in a query, form, or report.
--
Arvin Meyer, MCP, MVP
'DataStrat - Data Strategies - The DataStrat Solution'
(http://www.datastrat.com)
'The Access Web - Welcome' (http://www.mvps.org/access)
'Access MVPs' (http://www.accessmvp.com)

Do you mean run an update query to update the stock total column in the
products table using the calculation shown?

;597682']Rather than using a calculated field, which is not normalized
database
behavior and prone to error as well. Why don't you use a query with a
calculated column:

Calculation: DSum("StockIn", "TableName") - DSum("StockOut",
"TableName")
--
Arvin Meyer, MCP, MVP
'DataStrat - Data Strategies - The DataStrat Solution'
('DataStrat - Data Strategies - The DataStrat Solution' (http://www.datastrat.com))
'The Access Web - Welcome' ('The Access Web - Welcome' (http://www.mvps.org/access))
'Access MVPs' ('Access MVPs' (http://www.accessmvp.com))



Hi, relative newbie question:

I've got a datasheet form which I'm using for stock in/out recording
which adds a record for each stock transaction in a stock table
(number
of stock in and out etc.) , I also have a total stock level field on
the
products table - I want to add\subtract from the stock total in the
products table when I add a record to this stock transactions table
in
the datasheet - what is the best method to do this? I was thinking do
a
sql update in code on the Form_AfterInsert event - am I doing the
right
thing?


--
Jyan

------------------------------------------------------------------------
Jyan's Profile: 557
View this thread:
'update another table afterinsert - The Code Cage Forums'
('update another table afterinsert - The Code Cage Forums' (http://www.thecodecage.com/forumz/showthread.php?t=165626))

'Microsoft Office Help' ("http://www.thecodecage.com&quot

------------------------------------------------------------------------
Jyan's Profile: 557
View this thread:
'update another table afterinsert - The Code Cage Forums' (http://www.thecodecage.com/forumz/showthread.php?t=165626)

'Microsoft Office Help' ("http://www.thecodecage.com&quot

 

[Jyan]

Any thoughts on my last post anyone

 

[Larry Linson]

Your last post read: "Ah yes now I see! Many thanks for the advice."

What kind of thoughts were you expecting anyone to have about that? I
thought it was polite and courteous.

If you are asking for a detail answer to some earlier technical question,
you need to be more explicit.

Larry Linson
Microsoft Office Access MVP

 

[didier lavaud]

merci a tous meme les inconnus

 

[Jyan]

Sorry? What am I supposed to have done wrong?

 

[Jyan]

Actually if everyone looks carefully in the thread you see the followin
which was what I asking if anyone had ideas on.... !


-OK I've got the following query which adds all the Stock In pe
product: SELECT DISTINCT DSum("[Quantity]","[Stock Levels]","[Product]
" & [Product]) AS [Total Stock In], [Stock Levels].Product
FROM Products INNER JOIN [Stock Levels] ON Products.[Product ID]
[Stock Levels].Product
WHERE ((([Stock Levels].[Stock In \ Stock Out])="In"));

I can't now figure out how to subtract the sum of the records in th
same query [Stock In \ Stock Out]="Out" and what to do if there is n
record (ie make it a zero for the sum to work). Also would it be bette
to have a separate Stock In and Stock Out column?-

Jya:

Its just that we are also a little confused as to whether there ar
any
outstanding points on which you need advice. You asked for any view
on your
'last post', but the post previous to that simply expressed thank
(which was
good of you) but didn't ask any further questions.

I tried to address the issue in general terms, but we are not sur
whether
you need anything further from us or not.

Ken Sheridan
Stafford, England

Sorry? What am I supposed to have done wrong?

Your last post read: "Ah yes now I see! Many thanks for th advice."

d text clipped - 9 lines Wrote:

Any thoughts on my last post anyone

--
Jya
-----------------------------------------------------------------------
Jyan's Profile: 55
View this thread: 'ProgressBar Control - The Code Cage Forums (http://www.thecodecage.com/forumz/showthread.php?t=16562)

Microsoft Offic

Help[/url

"http://www.thecodecage.com"

"http://www.thecodecage.com"