473,320 Members | 1,612 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,320 software developers and data experts.

How to save calculated value from query in to the table

10
this is my query and if i run the query it will be save the record in the query it self not in the table that i created
im using ms access 2016

Expand|Select|Wrap|Line Numbers
  1. SELECT ledger.ID, ledger.[Expense:(type)], 
  2.     ledger.[Fund allotment], ledger.Amount, 
  3.     (Select Amount From ledger 
  4.         Where ledger.ID = (Select Min(ID) From Ledger)) - 
  5.         Nz((Select Sum(T.Amount) From ledger As T 
  6.             Where T.ID > (Select Min(ID) From Ledger) And 
  7.             T.ID <= ledger.ID),0) AS Balance 
  8. FROM ledger;


Attached Images
File Type: jpg Untitledqwe.jpg (35.2 KB, 775 views)
File Type: png Untitled.png (6.3 KB, 704 views)
Jun 19 '18 #1
7 2817
PhilOfWalton
1,430 Expert 1GB
I think you need to read up about database normalisation.
One of the "Do Nots" is that you don't hold calculated values in a table. The obvious reason is that on your second image, what happens if either the Amount or Balance gets changed or a record gets deleted? The whole thing becomes nonsense.

Your Query appears to be giving you exactly what you want. However it would be simpler still, if the FundAllotment column was not used, and the amount entered as a positive number for receiving funds, and negative numbers for the expenses.

Phil
Jun 19 '18 #2
twinnyfo
3,653 Expert Mod 2GB
junne,

Phil is spot on with the "Do Not" of saving a calculated value. Balances should always be a static, calculated value based upon the current data.

As a side note, it is also a good practice to have a better standard naming convention for your fields. You should only include letters, numbers and maybe an underscore. Although Access and SQL can handle spaces easily, it requires square braces ("[]") around your field names. Additionally, special characters, such colons or parentheses can ultimately cause problems, so you should avoid them altogether.

Hope this hepps!
Jun 19 '18 #3
Nauticalgent
100 64KB
Phil is spot on with the "Do Not" of saving a calculated value. Balances should always be a static, calculated value based upon the current data.
Actually, I have come across an exception to this rule: Historical values such as the price of goods. If you want to know what the price of something that was sold last year or the year before, you would need to know that at the time of sale.

Since I am not in the business of retail, I myself do not have to worry about this but I figured I would toss that out there...
Jun 19 '18 #4
twinnyfo
3,653 Expert Mod 2GB
However, an historical value is not necessarily a calculated one. If one have the date and the price per pound of bananas with how many pounds purchased, there is no need to calculate the total sale.
Jun 19 '18 #5
Nauticalgent
100 64KB
Quite right. For some reason I got fixated on lookup tables and whether or not to store those values in a transaction table. Big difference...
Jun 19 '18 #6
junne
10
thanks for the tips :)
Jun 20 '18 #7
junne
10
and thank you for your time giving hint to my problem it really helps :)
Jun 20 '18 #8

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

Similar topics

1
by: staeri | last post by:
I have a gridview which calculates a value in a templatecolumn. I want to save the calculated value into the database when all the other data is saved. How can I do that? As it is now I don't...
3
lwwhite
by: lwwhite | last post by:
Access 2003. I have a form (form view) with a subform (datasheet view). The form has a map_id field and each record on the subform also has a map_id field. I want the fields on the form and subform...
3
by: Mamunul | last post by:
Hi all, I need your help i.e. I want to save some data to the respective table which is calculation in the FORM.
1
by: Prashantsd | last post by:
Hi, I have problem storing the calculated value in table. I have a text box(Ranking) which display the sum of 4 combo box. this is wat I hav done in control source of tex box = + + + I...
94
by: mlcampeau | last post by:
I have a report (JobVacanciesOnly) that has a subreport (JobVacanciesOnlySR) that are based on two separate queries. MY - JobVacancyJobs SELECT Job.Code, Job.Title, Job.Grade, Grade.Minimum,...
5
by: =?ISO-8859-1?Q?Gear=F3id?= | last post by:
Hey, This may sound odd, but is there anyway to catch the current or just run query from inside a trigger? Kinda like how profiler displays the query just as you've run it, along with all the...
16
by: ayinky | last post by:
Hi I am developing an On Leave app. in which trying to calculate employees days on leave. I have created a Form consists of this fields (from a table): (1). OnLeaveID (used as for the...
2
by: latifju | last post by:
I am new in access. Now I am using MS Access 2010. Problem: I have table named customer with 6fields as follow 1. ID 2. Name 3. Received 4. Paid 5. Balance 6. Date
1
by: michael3426 | last post by:
I managed to figure out how to pull 3 fields from a query into a combo box. However, I want to save those results into a separate table. For instance, I have a query that pulls together the...
2
by: djgeverson | last post by:
On my form I have a calculated value text box which is the sum of 5 text boxes. I have three error messages which I want to appear "high risk" "low risk" and "medium risk" depending on the value in...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
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: 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...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
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.