Calculating Across Tables
Mar 17, 2008
Hi,
I think this should be simple, but I am struggling to work it out
In a relational database I have a stock table containing stock and quantities, there is a customer table with an order table used as the link table also containing quantities.
What I am trying to do is place an order using an order form linked to the order table, automatically reducing the stock table by the amount ordered.
I have tried an update query with no success, also tried using the builder to make a query and linked it to the stock table with no success.
I would be grateful for any ideas, I am sure it is relatively simple
View Replies
ADVERTISEMENT
Apr 23, 2013
I am using calculated field as a data type in access 2010.
They are working fine.
However, I added a new field and now the final calc won't work.
I have Subtotal adding loads of fields together. Works fine.
Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.
The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?
View 2 Replies
View Related
Nov 8, 2012
I have just started using Access 2010. I need to calculate student's ages from their date of birth to today's date. I have a column set up for the date of birth but can't figure out how to get this figure.
View 2 Replies
View Related
Oct 19, 2005
Hey Guys,
It’s been a while but I have been visiting from time to time. Just been busy with a load of web design.
Anyhoo, I have been asked to create a report on total revenue generation for projects and have taken it as far as I can, and now need your help.
I have two queries “qryCommTrialsRevenue_PatientEvents” and “qryCommTrialsRevenue_StudyCharges” that select income via two cost codes and between two dates.
Two further separate queries calculate the total for each project. That’s as far as I can take it at the moment and now I cannot see the wood for the trees…I need to calculate the total for each project ACROSS the tables. Can this be done successfully? Every time I try it the numbers don’t add up.
Cheers,
Rusty
:D
View 2 Replies
View Related
Dec 12, 2007
I need help. I want a field in my table that generates an Log number.
I already have fields:
I.D. number (sequential number)
Project Name (Text field)
Milestone (Number field)
The Log number should be made up of the first two letters from the Project Name, then the Milestone, then the ID number. The three parts of the Log number should be separated by hyphens.
So if:
I.D. number = 01
Project Name = Nice Light
Milestone = 03
Then Log number would = NI_03_01
On my form I have managed to get this to happen using an unbound calculated text box, but I need the log number displayed in the table, and this seems impossible.
Any help? Important things to note are I am not very clever when it comes to computers, or programming.
Cheers
View 12 Replies
View Related
Jul 18, 2015
I have a personal expenditure data base indicating daily expenditure on various heads ( File Attached). I want table to reflect expenditure on various heads on daily basis in COLTOT column. Any subsequent change in any of the field to be dynamically updated.Presently I am exporting file to excel and calculating the total there and then manually updating the COLTOT field. Other alternative I tried was to create a form and make a text field and sum the fields using formula like =nz([milk])+nz([vegetables]). I feel it may be unwieldy for such large number of expenditure heads.
View 14 Replies
View Related
Jul 29, 2015
Currently I have a form with these variables
- Assets
- Shifts
- Machine Offline Date
- Machine Offline Time
- Machine Online Date
- Machine Online Time
In the Asset Table I have recorded on each asset the number of shifts it operates for as well as starting / finishing time of those shift. e.g. 2 shift asset will start at 7:00AM finish at 11:00PM
or 3 shifts asset will start at 7:00AM finish at 6:59AM
Now I have 82 assets in the factory and 20% of those machines run 3 shifts. Each shift is 8 hrs.
What I have already done is allocate shifts per asset e.g.
when I pick Asset (a) in the Asset combo box, in the shift box it will automatically generate 2 or 3 dependant on what I have set.
If an asset runs for 2 shifts, it would mean that, that asset is operational/running from 0700 - 2300 or 7:00am - 11:PM
also if an asset runs for 3 shifts it would mean that, that asset is operational/running from 0700 - 0700 or 7:00AM - 7:00AM
These are the scenarios that is entered through a "Form"
Scenario A: Machine (a) breaks down at 1700/5:00PM on the 10/7/15 and was back online at 12:30 on 11/7/15, This machine runs for 3 shifts which would mean in the "Breakdown Downtime" the result should be 19.5 hrs
Scenario B: Machine (b) breaks down at 1900/7:00PM on the 10/7/15 and was back online at 10:00AM on 12/7/15, this machine runs for 2 shifts which would mean in the "Breakdown Downtime" the result would be 23 hrs.
I would like to make this an automatic calculation, Is this possible?
Please note I am not after a "Query", I need this calculation in the main Database table named "Tbl_MaintenanceDATA".
I understand this is not the norm and many have suggested against this, however for the purpose of what I need, it needs to be in the main table with all the other data.
Here are some snip it
snipit2.png
snipit.png
View 5 Replies
View Related
Apr 28, 2014
Let's say I have a table sort of like this one: [URL] .... (Table 1)
What I want to do is make another table that references the first table: [URL] ... (Table 2)
I want the cells in the Average field in Table 2 to calculate an average of all the values for records in Table 1 with Color fields that correspond to the Color field in Table 2 (this makes a little more sense if you look at the pictures). I could do this in Excel, but then problems would arise whenever I would add a new entry to the database, or re-alphabetized the data, since Excel math is depends entirely on the positions of cells, and I want these averages to be continually calculated correctly and to change whenever I add related records to the database.
View 1 Replies
View Related
Feb 19, 2007
I have a table with 2 coloums with nuberin them. I would like to add those 2 number together and display them in a 3rd coloum. I need to automatically change the 3rd coloums value should any others change. Is this possible?
View 4 Replies
View Related
Jan 19, 2007
age: DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))Dob is stored yyyyddmmI thought this above function looks ok. When I run it, I get a data type mismatch in criteria expression.What am I missing?Any help would be appreciated.Thanks
View 6 Replies
View Related
Sep 20, 2005
hi all,
i have a mainform with a subform.
in the subform i have 2 fields: duur and subtotaal
in duur i enter a number.
in subtotaal a calculation has to occur: =20*([duur]/60)
i tried to do this calculation in VBA but then i got the same value on every record in the subform.
But now the problem:
in the mainform i have a control 'totaal' that needs to do a DSum of subtotaal.
And here i am getting into troubles since subtotaal is not stored in the table but calculated.
The formulla for DSum would be:
Me.Parent!totaal = DSum("subtotaal", "tblFacturenDetail", "[FactuurID] = " & Me.Parent!FactuurID)
but as said, subtotaal is not stored.
How should i proceed?
Thanks!
View 2 Replies
View Related
Jan 6, 2005
If I want to work out how old all my patients will be on the 01 Feb 2005 then why doesn't the expression (in a query)
=(01/02/2005 -[tblPatient]![DateOfBirth]))/365.25 work?
How would I get the answer expressed in months?
View 1 Replies
View Related
Jun 28, 2005
I am designing a new database for our life and disability department and they have several different policies where the base life insurance reduces at certain ages.
Please take a look at the scenarios listed below and give me any suggestions and tips on the best way to get this done. If you have any.
Below are the 12 scenarios:
1.) Reduces 75% at Age 70
2.) Age 65 to 70 the life amount is $9,000 and Age 70 and over the life amount is $6,000
3.) Reduces at age 65 by 92%, age 66 by 84.64%, age 67 by 77.869%, age 68 by 71.639%, age 69 by 65.908% and age 70 and over by 40%.
4.) On the day following the 70th birthday, the life amount will reduce to 70%. On the day following the 75th birthday the life amount will reduce to 40% of the 70th birthday reduced amount.
5.) On the Policy Anniversary Date following the 70th birthday - reduces 50%
6.) On the Premium Due Date following the 70th birthday, the life amount will reduce to 70%. On the Premium Due Date following the 75th birthday, life amount will reduce to 40% of the amount of the 70th birthday reduced amt.
7.) Age 65 life amount reduces to 65%. Age 70 life amount reduces to 35%. Age 75 life amount reduces to 25%.
8.) Terminates on the first of the month following the 65th birthday.
9.) Terminates Age 70
10.) Terminates on date of retirement or 1st or month following 70th birthday
11.) Terminates Age 65
12.) Never Reduces
Thanks for your time, Brian
View 2 Replies
View Related
Nov 5, 2006
I have a database containing values in 16 fields. the fields are filled in over a period of three years. I would like to be able to calculate the average of the last four values entered, regardless of when in the cycle the value is required. I have tryed to use quereies but connot find away to assign the four fields to the expression so that it is the last four values and if four don't exist, avearage what values there are.
View 2 Replies
View Related
Dec 21, 2006
Hi,
I am new to Microsoft Access (2002) and the Access Word Forums. I am in need of a way to calculate timespent on six main topics through an 8 hr. working day. If I worked on Topic "A" from 8:00am to 8:15am, I need it to calculate it to 15 minutes. Then I need to figure a way to calculate how much time was spent doing Topic "A" for the working day, then by the workweek, then by the month.
I'm willing to learn, if someone has the time to teach or can direct me to a link that discusses something similar to what I am looking for. Any and all suggestions welcomed. I am looking forward to doing this in a timesheet format if possible.
Thank you,
Marty6
View 2 Replies
View Related
Jun 15, 2005
Hi Guys,
New to the forum, and its nice to see how active this place is, and upto date.
My question is, and probably simple to correct. ( Please be patient with me, i`m new to this, and my terminology might be incorrect, also please simple answers )
I`ve searched the forums, but as my teminology is wrong I cant find the results im after :)
Anyway, I have a table with many fields in it, but the parts i`m having problems with are as follows.
I have fields with
Price Inc Vat, Deposit Payment, Stage Payment, Balance Payment & Balance Due.,
The part im trying to get to work is the balance due which I want to automatically update, based in the data inputed into price inc vat deposit payment etc.
The calculation i`m after is as followes
Balance Due = ( Total Ammount Inc Vat - Depost Payment - Stage Payment - Balance Payment )
eg, Total Ammount Inc VAt = £2000
Depost paid is £1000
so far balance due would be £1000
Then Final Payment is made 2 weeks later £1000
So balance due would then show £0.00
Is this possible
Thanks in advance
If I have not made myself too clear, please excuse my crapness, and anyhelp Is much appreciated.
If you need any more info, please do not hesitate to contact me on
max.vernon@claddagh-group.com
Thanks in Advance
Max Vernon
[edit]
Just to let you know, I also need these details kept in the table for future reference so I dont think I can use a query to do it.
I`ve attached a copy of the database with some example data in it if this is needed for any assistance.
View 8 Replies
View Related
Jul 17, 2006
I'm suppose to create a list of 20 clients and let 5 accounts be 90 days past due, 5 over 60 days, 5 over 30 days three of each of the accounts have a balance over $75. Then it asks me to create a total of all account balances so that the total amount of recievables can be known. The second part is calculate the number of days each balance has been outstanding.My question is this can you calculate dates in a table or can you only calculate in a query. I'm new at access so have patience with my question(s)
View 3 Replies
View Related
Feb 26, 2008
Hi I'm building database for a car shop. One of my table (Cars) has these two fields in it intModelPrice (which stores factory price of a model) and intPriceWithTax (which value should mathematicaly be intModelPrice+[(intModelPrice)*(18/100)]).
Now, can I set my default value of intPriceWithTax to be calculated by this formula and how? I tried some combinations but I got various error messages.
I red Tips section on your site and saw that default value can't be calculated. I know how to get what I want in the select query but I want this table field (intPriceWithTaxt) to be acctualy filled with number value each time I enter a new record in this table (for a new car model). How do I acomplish this? Thanx.
P.S. - I searched the forum for answer but I had no luck.
View 11 Replies
View Related
Jul 19, 2005
Hello All!
I have data laid out as follows:
Measure MonthYr Value
Fixed Std Jan04 90%
Fixed Std Feb04 95%
-------- ------ ----
-------- ------ ----
Fixed Std Jun05 93%
My situation is two-fold: 1-I'm having difficulty coming up with a query that will calculate the weighted avg. of each quarter in the prior year and current year, 2-what to do about the current year's quarter when only 1 or 2 month's worth of data exists?
Can anyone help - it would be greatly appreciated. Thanks.
View 3 Replies
View Related
Sep 22, 2005
I have a table containing the following fields :
Sex(text), Age(numeric), sodium concentration(numeric).
I am trying to write a query which will return the percentage of male patients have a sodium concentration of a certain range (ex:120-130) out of the TOTAL number of patients.
I am able to count the number of events which fit the criteria but I cannot figure out a way to calculate the percentage part (unless i manually add a field in which I devide the count result by total number of patients).
Any ideas?
Thanks
View 4 Replies
View Related
Nov 16, 2005
I have a question I need help for, below is a very strict detailed explination of the current scenario I am in and I need assistance for it:
1) I have a percentage of Commission I need to calculate, the percentage of commission constitudes of the following information:
a) Date,
b) Customer Name,
c) Company,
d) Name,
e) 5% New,
f) 3% Ext,
g) Rep,
h) Doble Ref (0.025, 0.015),
i) Total Amount, and
j) Total of Commission.
If a person enters the same customer name more than ones, it will give that person's name a Doble ref (0.015) of commission, if on the other hand that customer is new it will give that person a commision of 0.05% new. I hope this is not intending to confuse you, it just gives the general idea of what the percentage of commission is intended to be.
To come back to the point, I have a form created under Access that holds all the names of the person's reference by in that case "Name" letter (d) described above. If I select for example a person name "Fahed" and I click on the button calculate new commission I want to be able to draw all the commissions that Fahed have done and then use the form above to do the approriate calculation.
The above is a general problem, below is the scenario:
I get a form called New Client, the form New Client is a parent form and underneath that form is a sub form which holds estimates, invoices and so on. Ones again I am giving you the picture of what the form looks like, I cannot paste the pictures here because they would take time and would not be clear. The new Client has a text field called Reference By. That text field is where I need to get for the Percentage of Commission form to be entered under the field Name, letter D (described above). The total of amount is extracted from the subform Invoice, total, of the parent form called New Client, and there is a check mark in the new client called "Already existed" that if I click it, that information is then send on a separate form called "PercentageOfCommission" that will determine if it calculates the percentag of 0.05 or 0.025 depending if it is Calculate Commission or calculate existing commission.
I want it to be able then to do the needed calculation which I already have the approriate formula for and then display in a report what is shown above.
I don't know how to get information from different forms, and I don't know how to display for example ALL FAHEDS with information but are related only to FAHED.
Ones again, let me give you an example:
Date Name Info
15-Feb 05 Fahed Stonyx
15-Mar 05 Fahed Commodore
...
18-Sep 05 Ahmed Apple IIc
I want to display only one name Fahed and when I click for example display summary to show ALLLL the name that holds Fahed that have different Info. Any help would be greatly appreciate it.
If you feel what is posted here is TOO confusing and trying to explain it on the messageboard might take time and you wish to assist a poor soul in need, then you can contact me at devastating_battle@hotmail.com so we can talk live in MSN to be able to describe the matter much clearly. Please don't ignore me and leave me hanging in the air dry, thanks in advance.
View 1 Replies
View Related
Feb 6, 2006
hi all,
i need your help on how to calculate non-value item, in this case a textbox fills with course names & description. please refer to attachment for more information.
thanks
View 2 Replies
View Related
Feb 23, 2006
I have a table "main" with 2 colums (main_id,date_out). This table has a link to the table "refill". This table has 3 colums (refill_id, main_id, refill_date).
Example data:
TABLE MAIN
main_id - data_out
01 - 01/01/2006
TABLE REFILL
refill_id - main_id - refill_date
01 - 01 - 10/01/2006
02 - 01 - 16/01/2006
03 - 01 - 21/01/2006
When I make a simple selectquery with the fields main.date_out and refill.refill_date I become the the next result.
01/01/2006 - 10/01/2006
01/01/2006 - 16/01/2006
01/01/2006 - 21/01/2006
Now the problem.
I have to calculate the days between the main and refill dates
01/01/2006 - 10/01/2006 ( is the same as above)
10/01/2006 - 16/01/2006
16/01/2006 - 21/01/2006
suggestions????......thanks
View 2 Replies
View Related
Sep 26, 2006
i'm trying to create a query which looks at the field dateEntered and then returns the previous sunday date of that week.
ex.
dateEntered returnedSundayDate
09/26/2006 09/24/2006
are there built in access functions to figure this out in a query?
please help.
View 2 Replies
View Related
Feb 9, 2007
Hi folks,
I would like to add up all the records in a field to get a grand total. Can I run a query to do this? I've tried the Totals button, but doing this is just summing the individual records, not all of the together.
Thanks for any help you can suggest to this Access virgin.
View 5 Replies
View Related
Feb 23, 2007
HI there,
I have a dataset that contains a date 'dd/mm/yyyy' and a timelag indicator eg; '1' = 1 day after the date specified in the record, 2= 2 days after the date specified in the record etc, etc.
I'm trying to calculate what the 'lag date' would be utlising the original date and the timelag indicator.
Hoping someone can assist.
Many thanks
View 2 Replies
View Related
