My query contains two calculated fields [TaxSavings1] and [TaxSavings2], which are based on some currency and number-type fields in one of my underlying tables.
I just created another field in my query which looks like: [TaxSavings1]+[TaxSavings2]. Instead of adding the two fields, it actually lumps the two numbers together. For example, if [TaxSavings1] =135 and [TaxSavings2]=30.25, it will give me: 13530.25. I need it just to simply add, i.e. answer of 165.25.
Does anyone know how to correct this? Thanks in advance.
I know it is said that you should not store calculated data in tables so I need some input on how to go about a problem I am having.
I am creating a DBase with some aircraft info in it. The DBase will be around for awhile so I plan to implement so sort of archiving later on.
The problem is one piece of info I am tracking is flight hours for the aircraft along with hours on certain components. I have a table with info pertaining to each aircraft by number (1 record per aircraft) one of the fields is total current flying hours. I have another table that has individual flight info including number of hours per flight. I want to add the individual flight hours with the total flight hours to get a new total flight hours. Easy with a query but if I get rid of old flights then how will you add them in the query. The only thing I can think of now is to save the newly summed number in the table which is supposedly taboo. Same with the components if it is on the aircraft during the flight it needs to get the new flight hours on it also .
Sorry if this is trivial but any ideas on how this should be done would be great.
Hi, I have a calculated field in a query that concatinates an order number prefix and the part number to give the order number: OrderNum: [OrderPrefix] & [PartNum]
This works fine except for one of the categories. For the third category there are no entries in the tblPartsList - because they are miscellaneous.
How do I change the calculation so that it works as above where there are corresponding Part numbers in tblPartsList and otherwise gives me the order number and any 2 digits e.g. something like [OrderPrefix]*
Having been trying to do this for ages but having trouble with syntax.
I have read a number of these threads now where it says you should not generate new records based on calculated fields. So can some one help with my following problem.
I am creating a customer/contract database where when an order is created, a record set is generated to store dates for invoice periods. I have done this by using the start date of the contract and then adding onto this a value of x days that updates a table giving y records based until the end date of the contract.
Is this the best way to do this or can some one tell me a better way. Thanks
hi. i wanted to kno how to have a field in a table that is calculated from other fields if this is possible. for example i have a order table which has a quantity field and a price field and i want to add a total field that will alutomatically calculate the total (quantity*price) and add it to the field. i know how to do this in a query but don know how to get that field from the query into the table. thanks for any help
I was once told that Access does not like to store calculated values but I would like to.
Here is my expression: =([Shift Lenght]-[Total idle time]-[Total down time])/([Shift Lenght]-[Total idle time])*(0.33*[Total pc])/([Shift Lenght]-[Total idle time]-[Total down time])*([Total pc]-[Total scrap])/([Total pc]+0.1)*100
This gives me the value that I desire, now I need to store it in the table with the rest of the records data. I am planing on storing 3 -4 expressions like this and then averaging them. Then building a query to the averages for the day,week,month, and year.
Please be gentle I am not very familiar with access but learning a lot:)
I heve started a new table and one of the fields is Date of Birth.
I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.
Any ideas on how to make this work? does it have to be a querie?
I heve started a new table and one of the fields is Date of Birth.
I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.
Any ideas on how to make this work? does it have to be a querie?
I heve started a new table and one of the fields is Date of Birth.
I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.
Any ideas on how to make this work? does it have to be a querie?
I have a table with customers, one with products and one transaction table. How can i produce a report, based on a query that will add up the total any one particular customer has spent to date from the transaction table?
hi. i wanted to kno how to have a field in a table that is calculated from other fields if this is possible. for example i have a order table which has a quantity field and a price field and i want to add a total field that will alutomatically calculate the total (quantity*price) and add it to the field. i know how to do this in a query but don know how to get that field from the query into the table. thanks for any help
i have opened a field (which was created in a table) in a form as a calculated fieled the calculations are ok but only in the for the same field in the table remainded empty
how can it be set so the table's field will get the clcaulation ?
I have this query which selects two fields and calculates it in another field. But the calculated field has more than 1 expression.Eg If field A < Field B,Field B + Field C AS X,If field A < Field C,Field A + Field C AS Y.The problem is that when I show this on the Form I want only 1 of the fields to be shown ie either X or Y .if any of the expressions is satisfied then only that field should be shown as a textbox control on the form and not both.How do I do this?? Its really complicated for me..Pls help..
I have two email addresses in a table one personal and one work and a question asking which to use, I have a query running from the table and have an IIF expression creating a field which looks to see which one to use (that all works fine !) anyway my client has now asked for an extra field in the query which combines the two email addresses, as in aaa@homedomain.com;bbb@workdomain.com so that I can run an email to both addresses at the same time. fraid I just can't figure it out ! I'm sure it's obvious, I can't find a similar question posted either, I've tried the expression builder using AND for the two email addressses but just end up getting -1 in field when I run the query, not the two email addresses separated by a ;
In one of my queries, I'd like to reuse a calculated field as criteria in the Where clause. SQL seems reluctant to want to do this. You can RESTATE the calculation in the Where clause, but can't refer to it by Name. You can Order by it, but it can't be in the Where clause.
If the query result set consists on a set of Named columns, why can you refer to a calculated column by Name in the "Order By" clause but not in the Where clause?
Why is this OK?
Select FldA As FirstField, FldB As SecondField, FldA - FldB As TheDifference <-- Just a calculated field From SomeTable
And this is OK?
Select FldA As FirstField, FldB As SecondField, FldA - FldB As TheDifference From SomeTable Order By TheDifference <-- Order By is OK
And this in not OK?
Select FldA As FirstField, FldB As SecondField, FldA - FldB As TheDifference From SomeTable Where TheDifference > 100 <-- Can't use TheDifference in Where clause
Don't want to repeat the calculation.
Select FldA As FirstField, FldB As SecondField, FldA - FldB As TheDifference From SomeTable Where FldA - FldB > 100 <-- I know this works, but ... Order By TheDifference
This seems a daft question but I have searched the forums and can't find an answer so I hope someone can help me.
I have a table: Register(RegID, Pupil, Activity, Duration, Preparation)
I have a query that I want to calculate the total hours the pupil has spent on activities.
The Duration and Preparation fileds are held as number of minutes so for a 1 hour event the number 60 is held.
I have a query with 4 fields: Pupil SumofDuration SumofPreparation TotalHours = ([SumofDuration]+[SumofPreparation])60
The problem I have is that TotalHours is calculating as an whole number. e.g. I have a Record Pupil = 701 SumofDuration = 270 SumofPreparation = 0 TotalHours = 4
But I want TotalHours = 4.5
I have set the Duration and Preparation field sizes to Decimal and Format to General Number.
Hi, I am trying to display all records that are 30 days old from date of registration (& not paid). I have:
30 Days: [SrDate]+30 SrPaid = No
But I don't want to see those that have registered in the (say) last few days, but only those that have not paid and 30 days are up. How would I achieve this?
hi I am making a late report of each employee's data and I calculated for each individual day by having late for how long, but I would like to make another column which displays the total amount of time late. This is currently what I have now:
SELECT username, DateValue(clocktime) AS Date_Clocked, Min(TimeValue(clocktime)) AS Clock_In_Time, ((Clock_In_Time-#9:00:00 AM#)*24) AS Late_By_inHours FROM tablename GROUP BY username, DateValue(clocktime) HAVING Min(TimeValue(clocktime))>#9:00:00#;
clocktime is a date/time field, in reality it is regardless whether it is clock in or clock out time. And now I want my table to look smth like this:
Has anyone knows how to do smth like this? I tried using the SUM() but it states error stating it cant take the expression ((Clock_In_Time-#9:00:00 AM#)*24) smth lidat
I am trying to create a calculated field in a query, that shows money owed from customers. I have 4 different job codes that have their own prices. I also have customers with the amount of hours they owe for, for each job. There are two customers that owe for two different jobs. I know how to write in : Money Owed: [price per hour]*[number of hours]. but i want to have only one line for each customer when i run the query. How do i combine??
I have a main form which shows customer personal details including their budget and a subform which shows the costs for each course they book. The sub form has 4 boxes which I use to capture course fee, travel fees,subsistance and other expenses (along with other details such as course code etc). These cost details are stored in the table study_leave_recs in the respective 4 fields. I show on the subform the total spend for each course being booked using a calculated control. On the main form I use four dsum statements added together to show the total spend per customer. =DSum("[COUR_ACT_FEE]","study_leave_recs ","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")+DSum("[COUR_ACT_TRAVEL]","study_leave_recs ","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")+DSum("[COUR_ACT_SUBSISTANCE]","study_leave_recs ","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")++DSum("[COUR_ACT_OTHER_EXPEN]","study_leave_recs ","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")
It works ok but is there a better way of doing this? Also is there any way I can use the resultant output to calculate / show the remaining budget (the budget is held in the general table "people"? I'm having trouble using the above statement to subtract the calculated spend from the budget box shown in the main form!!
I would appreciate any help, I seem to have spent ages on this on! Thanks in advance. Regards Peter
I am trying to do the following in a form. 1) (Travel hours + work hours) * dollarperhr to get the "Total" bill to a customer.
2) The results will be put in a field "TotalDollars" that is a part of a table.
Please help.
I tried to put an event (on change) on the "dollarperhr" box so that after entering the values in "travel hours" + "Work hours" * "dollarperhr", the calculation will be done to populate "TotalDollars", but it didn't work.
