Calculating a field for a minus total

[heart01]

Hi all, I am quite new to Access 2007 I have basic skills. I am building a database I have products table ( i am building all fields in the products for for data input as opposed to separating) and related form. In the form I have type, Qty, type out and Qty Out. On the form I have managed to calculate the total from Qty and Qty Out however in the raised query and report I do not see the totals in the field that I have set in the products table as total stock remaining. Could someone please steer me in the right direction...I would really appreciate this as i cannot move forward until I get it right broken down i have

in table

type
Qty

Type out
Qty Out

In form
type
Qty

Type Out
Qty Out have entered =[qty] - [qty out]

total stock remaining

in query and report this total stock remaining field does not show the total remaining amount

[NeoPa]

You talk about some [Stock Remaining] field in the table in your explanation, yet nowhere do you refer to anything alse as stock, nor is this field listed as a field in your table.

It makes it very hard to understand what your question is. We'd like to help, but without a question that makes sense (don't forget we don't see what you see, only what you tell us) it's not possible.

Perhaps you could review your question and add those explanatory comments that would make this a consistent whole.

Welcome to Bytes!

[heart01]

Hi all, I am quite new to Access 2007 I have basic skills. I am building a database I have products table ( i am building all fields in the products for for data input as opposed to separating) and related form. In the form I have type, Qty, type out and Qty Out. On the form I have managed to calculate the total from Qty and Qty Out however in the raised query and report I do not see the totals in the field that I have set in the products table as total stock remaining. Could someone please steer me in the right direction...I would really appreciate this as i cannot move forward until I get it right broken down i have

in table

type
Qty

Type out
Qty Out

In form
type
Qty

Type Out
Qty Out have entered =[qty] - [qty out]

total stock remaining

in query and report this total stock remaining field does not show the total remaining amount

my apologies I wondered if someone could understand what I was on about. I ll try to explain better

In my table I have the following fields.

Type
Qty
Type Out
Qty Out
Stock Remaining


In my form I have exactly the same fields and in the stock remaining field I have placed the code =[Qty]-[Qty Out] to get the total stock remaining per type.

In the query and report that I make I insert the above fields however my stock remaining totals do not show on the query or report. I am wondering how I get this stock remaining total to show on the query and report? I am also wondering if I should put a command in the stock report field in the actual table if so where and what would command would i use? your help would be greatly appreciated.
kind regards
Deb

[MMcCarthy]

my apologies I wondered if someone could understand what I was on about. I ll try to explain better

In my table I have the following fields.

Type
Qty
Type Out
Qty Out
Stock Remaining


In my form I have exactly the same fields and in the stock remaining field I have placed the code =[Qty]-[Qty Out] to get the total stock remaining per type.

In the query and report that I make I insert the above fields however my stock remaining totals do not show on the query or report. I am wondering how I get this stock remaining total to show on the query and report? I am also wondering if I should put a command in the stock report field in the actual table if so where and what would command would i use? your help would be greatly appreciated.
kind regards
Deb

Hi Deb

I think I understand what you are talking about. You have calculated the value on the form but it doesn't get stored anywhere. You need to remove the StockRemaining field from your table and calculate it dynamically in a query. It is not good practice to store a calculated field as it keeps changing and causes data integrity problems.

Try a different approach and make the calculation in a query as follows:

SELECT Type, Qty, [Type Out], [Qty Out], [Qty]-[Qty Out] As StockRemaining
FROM Tablename

Then make that query the record source for your form and report. That way you don't have to calculate it on the form at all.

Mary

[heart01]

Hi Deb

I think I understand what you are talking about. You have calculated the value on the form but it doesn't get stored anywhere. You need to remove the StockRemaining field from your table and calculate it dynamically in a query. It is not good practice to store a calculated field as it keeps changing and causes data integrity problems.

Try a different approach and make the calculation in a query as follows:

SELECT Type, Qty, [Type Out], [Qty Out], [Qty]-[Qty Out] As StockRemaining
FROM Tablename

Then make that query the record source for your form and report. That way you don't have to calculate it on the form at all.

Mary

Wow, Thank you this does work. I do have a further related question to this if I may.

On the report it is not collating the Type into one single group for example i have about ten listings for Allterrain and alongside each list I have the total for each individual listing. Am I able would you know to group all types and have one complete total for that particular group. I think that would then complete this question.
kind regards
Deb

[MMcCarthy]

Wow, Thank you this does work. I do have a further related question to this if I may.

On the report it is not collating the Type into one single group for example i have about ten listings for Allterrain and alongside each list I have the total for each individual listing. Am I able would you know to group all types and have one complete total for that particular group. I think that would then complete this question.
kind regards
Deb

This should give you what you need. It's known as an aggregate query.

SELECT Type, Sum([Qty]-[Qty Out]) As StockRemaining 
FROM Tablename
GROUP BY Type;

You can't show the other fields or it will group on each one of them rather than just type as you wish.

Mary

[heart01]

This should give you what you need. It's known as an aggregate query.

SELECT Type, Sum([Qty]-[Qty Out]) As StockRemaining 
FROM Tablename
GROUP BY Type;

You can't show the other fields or it will group on each one of them rather than just type as you wish.

Mary

thank you so much this has worked. It has come in the report as EXPr as a header but I dont mind and will work out how to correct this....thankin g you once again