Scenario : i have a table
tblOne - SCID , BoxSetNo , BoxesInBoxSet
--------------1----------1------------5
--------------1----------2------------7
--------------1----------3------------9
--------------2----------1------------3
--------------2----------2------------7
so what i want is , in a form i need to have a textbox where the sum of SCID 1 should display(in this case 21), so i can do more calculations with that value
form's record source is a different table!!
i can add the field from other tables, but it only shows one record (SCID 1 and the value is 5 not 21) not the sum of it
pls how to calculate the sum of a field in a textbox??
3 2013
Use this expression as control source for your textbox: - =DSum("BoxesInBoxSet","tblOne","SCID = " & [SCID] & "")
NeoPa 32,569
Recognized Expert Moderator MVP
Generally speaking those who work with databases understand, to a greater or lesser extent, the concepts of Normalisation (See Database Normalisation and Table Structures). As such, we would very rarely recommend updating tables to reflect calculated values.
Immediately you have data that is already stale. Sometimes, very rarely in my experience, it is necessary, or at least helpful/easier, to create data in a table temporarily that can be used - but not stored long-term.
There are many ways to get totals into a Control on a Form. CactusData has illustrated one. Another very simple one is to ensure there is a calculated field in the SQL used to source your Form. Aggregating the source data typically ensures the dataset isn't updatable though so that should always be taken into consideration.
Appending data into a temporary table can be done with an Append query based on an aggregated SELECT section. I'll go into more detail on that if required but please understand I am not recommending this approach. In fact, my advice is to avoid it like the plague if you have any alternative.
Thanks alot Guys!!
Helpful Tips Saved Me
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: DChrist |
last post by:
I am unable to update a table (either by opening it and entering data
directly or through a form). I have set the recordsettype property to
updateable snapshot and have set the permissions to update inside of
the SQL Server 2000 to checked. Unfortunately, these items have not
worked. Any advice is appreciated. Thanks.
Dave Christman
|
by: gj |
last post by:
Hi,
I'm trying to update a sql database from a web form using text boxes.
I'm trying to learn C# on my own so I am at a complete loss. I created
my sql connection, data adapter, dataset and data view in the visual
studio designer. I'm trying to keep a history of the record so instead
of editing the record I insert a new record with my changes. Instead
of the changes, it inserts the orginal record. Below is the part of
the code. Any help...
|
by: DanLezoche |
last post by:
i have a client who's database requires users to enter the beginning
date and ending date for contracts. i would like to create a vba
function that checks the end date w/ the now() function (from what i've
gathered) and updates the status field with "Expired" if the end date
has passed. can i have this function run when the form opens, or a
report is run, so it loops through the entire table and adjusts these
fields as necessary? ...
|
by: Bo Long |
last post by:
I believe the following a valid SQL statement, but MS Access returns
with an error "Operation must be an updateable query".
Any suggestions would be greatly appreciated!
UPDATE FERCPTILoad AS FL
SET FL. = FL./(
select FA.
from FERCPTIArea AS FA
where FA. = FL.);
|
by: SQL Learner |
last post by:
Hi Alex (Kuznetsov) and All,
This is to follow up with my last post, "Link two tables using partial
word match".
How can I UPDATE table using partial word match?
How can I write a SQL statement to do so? (I am using MS-Access.)
The two tables and the expected result are listed below:
Thank you in advance!
| |
by: Ciara9 |
last post by:
I am having problems trying to update a field in a database using a field in a form. I currently have two fields, Today and Tomorrow in a table named Date. The Today field automatically defaults to the current date, and what I am trying to accomplish is if this date changes, then the second field, Tomorrow, will automatically become equivalant to the value of Today +1. Sounds simple enough, however I cannot get the table to update. I am able to...
|
by: slavisa |
last post by:
Im having trouble with updating my 1 table with the information from another!
I have a table with 6 fields.
Code(pk), Name, Title, Address, State, city, zip
the table is called Info.
Now i have a excel file which i imported it in the access db as a table called newinfo
|
by: benchpolo |
last post by:
I have 2 tables
Table 1: Invoice
Table 1 fields: ClientName, PostedPaid (Yes/No)
Table 2: History
Table 2 fields: ClientName, DatePaid
Overview
In form level when the user click SAVE the ClientName is stored in a Global Variable 'gClient'.
|
by: adithi |
last post by:
My Table Structure is:
Table A Table B Table C
colA -PK Col B-PK Col C-PK
Col B-FK Col B-FK
Col C-FK
This relation establish a Concurrent relation where in Cascade Property fails.I can set Cascade property for any two tabnles...but not the third table.
My requirement is :
|
by: zeusads |
last post by:
Hello, I have a little problem, if anyone can help!
I have these 2 tables
Products:
ID
Product
Category
Current
Stock
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |