473,322 Members | 1,806 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Calculating a field for a minus total

22
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
Jun 10 '10 #1

✓ answered by MMcCarthy

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

Expand|Select|Wrap|Line Numbers
  1. SELECT Type, Sum([Qty]-[Qty Out]) As StockRemaining 
  2. FROM Tablename
  3. 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

6 2643
NeoPa
32,556 Expert Mod 16PB
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!
Jun 10 '10 #2
heart01
22
@heart01
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
Jun 10 '10 #3
MMcCarthy
14,534 Expert Mod 8TB
@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:

Expand|Select|Wrap|Line Numbers
  1. SELECT Type, Qty, [Type Out], [Qty Out], [Qty]-[Qty Out] As StockRemaining
  2. 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
Jun 11 '10 #4
heart01
22
@msquared
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
Jun 11 '10 #5
MMcCarthy
14,534 Expert Mod 8TB
@heart01
This should give you what you need. It's known as an aggregate query.

Expand|Select|Wrap|Line Numbers
  1. SELECT Type, Sum([Qty]-[Qty Out]) As StockRemaining 
  2. FROM Tablename
  3. 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
Jun 11 '10 #6
heart01
22
@msquared
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....thanking you once again
Jun 11 '10 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

13
by: | last post by:
I have an Access database used to track donor pledges. In it, there is a table that contains three fields for each donor: Gift_Amount, Gift_Per_Year, and Matching_Gift_Ratio. The following...
4
by: John | last post by:
hey all..... alright, I am frusterated to the point of throwing my machine out the window (this board went down, trying to find stuff on google, this has been a nightmare) so I hope you guys can...
2
by: John Baker | last post by:
Hi: Hi: I have a form which is based on a query, and is being used to update a table. It all works well, except for one thing. I have a number of unbound calculation fields (total value,...
2
by: hhathome | last post by:
In my report I have a calculated DateDiff Field, I also have a calculated count field. I'm trying to get a total of the DateDiff field and dividing it by the count field and I'm having problems --...
3
by: luscus | last post by:
Thanks for all the responses on my first question. Unfortunately the answers I was given were too complicated for my small brain , and neophite condition to understand. So if you could talk down to...
1
by: john_liu | last post by:
I have a field called AMOUNT in MS Access with values of 10, 20, 30, 40, and I want to create another field called TOTAL like this: if the value in field AMOUNT=20, then TOTAL=AMOUNT*3, else...
10
by: Lisa | last post by:
In translating the formula for calculating lottery odds for various conditions into a Visual Basic Program, I have apparently missed something in that I get errors in the part of the calculation...
1
by: HuntTheWumpus | last post by:
Has anyone found a fool proof solution to sorting a calculating field in a report in Access 2003? Grouping and sorting works fine in a field that pulling directly from a query but when you try...
0
parshupooja
by: parshupooja | last post by:
Hi All, I have gridview with following columns date--morningin--morningout--afetrnoonin--afetrnoonout I want to create another 6th coulmn which shd display total...
4
by: sumit kale | last post by:
Hi, Can somebody help me resolve my problem ? I am getting error when calculating total using unbound textfiled in subform. I have a main form called purchase_register_master and a subform...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.