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.
I'm trying to create something like an online banking view that shows the running balance as each transaction occurs. I have tblTransactions with fields AccountNumber, ItemDescription, and TransactionAmount. I'm trying to create something that shows these three fields and a fourth field with the running balance.
So if I initially deposit $100 it will show the first record with TransactionAmount = $100 and RunningBalance = $100. Then the next transaction will subtract the Transactionamount for the new record from the RunningBalance from the previous record to get the RunningBalance for the new record. So if I make a purchase for $2, the AvailableBalance for that record is $98. Is this possible with a query? Here's a picture to describe what I'm talking about ....
How to calculate present age automatically using DOB field. I have created 2 fields DOB and AGE. But everytime i am calculating AGE and then manually typing in AGE field.
calculating a field value.I've got 5 products, numbered 1 - 5 in tblProducts.productID..In one order I'll only ever have the 1 - 5 products, with a maximum of 5 products in any Order.
tblOrder.orderID tblOrderDetail.productID I have a field in tblOrderDetail called "productNumber" which is where the result should go of the calculation.
I need to calculate the total number of products ordered but it could be a combination of them, the thing is that I need to reference them in a specific order if they exist in the order as per the schedule above.
example order 1 = products 1,3,4
the result should be 1.3 (mushroom) 2.3 (egg) 3.3 (fish)
example 2 order 2 = products 1,4
the result should be 1.2 (mushroom) 2.2 (fish)
example 3 order 3 = products 4,1 (this one shows that we haven't ordered it in order, I still need to display that correct order)
I've made a membership database for an imaginary leisure centre as part of my A Level coursework - only after more or less finishing my project, I've realised that I haven't provided a way for the end users to calculate fees for members.
I suppose the calculation I would have to do is multiply the Length of Membership (days) field on the Membership Opportunities by Cost per Month on the MembershipTypes table.
These are the relevant tables and I've also attached my database (the password is "password" for any of the users) ...
- 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.
I am trying to work out the expression that will first count the number of 'Yes' returns in a series of yes/no boxes and then display as a percentage.
To explain...The yes/no boxes represent attendance over a 20 lesson course. I have added a count for each lesson for student attendance..(grouping them in the footer) but I would like to add the number of attendance for each student.
I'm trying to calculate a numeric value of a field based on the value of another field as a row is created using an "After Insert" macro. To test this I've created a table which has two integer columns "NUMBER1" and "NUMBER2". The macro was defined using the wizard as:
Code: EditRecord SetField Alias Name NUMBER2 Value = 5+[NUMBER1] End EditRecord NUMBER2 is not populated when I enter a value in NUMBER1
I get the following in USysApplicationLog:
Error Description -20335 EditRecord failed because the default alias represents a record which is read only.
I've can do this on excel but don't know how in Access. I'm calculating bonuses. My table has salaries, and my query simply multiples each salary amount by a % to get the bonus amount. But I need to calculate adjustments to the bonuses using the sum total of the bonuses my query calculated:
Salary (from table) Bonus (calc'd by query) Adjustment (to be calced)
100,000 1,000 Sum of total bonuses/salary*4% 90,000 900
How do I capture the total of my calculated bonus column to use to calculate the adjustments in my right-most column?
I have a data entry form [Resources] and I would like to display some information about holidays in the form footer. Once the user has picked a combination of Trainer_Name and Start_Date I would like the 'On Change' or 'On lost Focus' event (not sure which would be the best) to perform a datediff calculation.
The datediff calculation would compare the difference between the start date entered on the form and the most recent past Start_Date on a query called [Hours Holiday_P1].I could adapt the same code to also look for the difference between the End_Date on the form and the next Start_Date on the Query.The idea is that when resourcing trainers I know how long it is since and how long it is till their next holiday.The sql for the query is
Code: SELECT Resourcing.Start_Date, Resourcing.Trainer_Name, Resourcing.Duration, Time.Hours, [Hours]/7.4 AS Days FROM (Resourcing INNER JOIN Employees ON Resourcing.Trainer_Name = Employees.Trainer_Name) INNER JOIN [Time] ON Resourcing.Duration = Time.Time WHERE (((Resourcing.Start_Date) Between DateSerial(Year(Date())-IIf(Month(Date())<4,1,0),4,1) And DateSerial(Year(Date())+IIf(Month(Date())>3,1,0),3,31)) AND ((Resourcing.Activity) Like "Holiday*")) ORDER BY Resourcing.Start_Date;
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.
I have a report that is calculating account revenue for my sales reps. I am able to generate the report, group by the sales rep and then total their total account revenue. Now I want to group on the total field that I just calculated. How do I do that?
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?
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
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)
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
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.
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.
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
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.
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)
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.
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).
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.
