Im not sure the best way to go about this and would be most grateful if I could be pointed in the right direction.
If I had a table called tblEmployee and a field within it called "PayRate" and another table called tblHours with a field in it called "LabourCost" and a field called "Hours". What is the best way to hold the "LabourCost" value so it doesnt get affected if someones "Payrate" is changed.
I expect I have asked this all incorrectly and I really do apologise.
I am creating a database to keep up with the expenses for my art business. I have a table for Vendors with a field (Mileage) for one-way mileage that I enter manually (EX: 11.8 miles). Then I have a calculated field that basically just multiplies the Mileage field times two to get total mileage to and from my house to vendor location. (Total Miles) {The reason I want to do this this way right now is because I didn't keep up with my mileage this year and I am having to look up the distance from my home to each location)
Then I have a "supplies" table where I show the date, the vendor, the item, cost, etc, and I want to add a Total Mileage field based on the calculated Total Miles field in the Vendors table that will automatically populate based on the vendor of each transaction. I just can't remember how to make this work and I know just enough SQL to be dangerous.
the table 2 is the source of a form that will let the user change the numbers. table 1 should change Date1 and Date2 Fields based on the two fields (3months) and (6months) if i want to make a lookup wizard it should be changed manually and if i make a calculated field i can't find other tables in the expression builder
I have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?
Hi folks, Is there a way to have a calculated field within a table? I have a table that I've added four fields to: AmountIT, AmountRE, AmountKK, AmountRes. I want to be able to sum these four fields to give AmountTotal, but is there a way to have in a table?
In an orders table, i want to have a total value calculated... in the table... i was only able to do that in a query... but is it possible to do so in a table?
I know it breaks all the rules to store a calculated field on a table, but I've painted myself into a corner with the way I built this particular database, not good, I have repeating groups and basically the whole database is built this way. I need one form to store a calculated value on a table to solve a problem on a report that encapsulates the total of all the repeating groups. Before you start the lecture, I know I messed up, but this is way to far down the road to start over. Any help would be greatly appreciated.
Is it possible (and logical) for me to create a table with a calculated field from 2 other fields in the same table? Example; I have a long time field (mm/dd/yyyy hh:mm:ss) aka StartTime, and a long time field EndTime. I would like to subtract the StartTime field from the EndTime field to get the total time elapsed, but in hh:mm:ss format. I can get it in hh:mm format, but I really need the hh:mm:ss format. Any Ideas? :cool:
I have been reading various threads about this subject and I know that one shouldn't store a calculated field in a table.
I believe this may be an exception to the rule (but I could be wrong). I am making a pricing database that will use a few manual inputs and the rest of the fields will be calculations (gross/profit margin, expenses) based off these inputs. We need to have these calcuated fields stored because they are essential to the pricing decisions and we will have to go back and access these fields at a later time. Another reason for this is because there will be at least one report that will be used to compare prices and calculated fields associated to a certain product by company, program, item type, ect...
I've learned the hard way about using calculated fields in a table. I used on while building in Access 2010 for users that will be using Access 2007. Once my testers pointed out the error message, I did some research and realized my calculated field was the problem.
So, I created a query to replicate the calculation, deleted the offending field from the original table and redirected everything to point at the new query vice the original table.
However, my testers are still receiving the error about needing version 14 to access that table. There isn't a calculated field there any longer, but they still can't access that table. Did that calculated field put some sort of taint on my database that can't be removed? I'm not sure what to do. Do I need to start over, so that a calculated field was never part of the database to begin with? If so, how much of my existing one can I reuse?
I am trying to add some numbers together for a number of fields, of which at least one field will either be 0 or empty. I am trying to use the following formula in a calculated field in a table:
I did the calculated fields in my table but in my datasheet they are not appearing. Are they not supposed to show there? I have not created any forms yet.
I have a form which calculates alot of numbers. Im trying to figure out how to save the calculation to a table field. Is this possible? Can someone help me with a solution please
I need to populate a table with Grid reference values consisting of 6 figure integers, eastings and northings.
I am receiving/downloading the Grid Refs in a UK Grid Tile format, with 3, 4 or 5 figures preceded by letters.
E.g. SK 456 849, SP 6789 4356.
I have used some query functions and written a bit of VBA behind a form to convert the received coordinates into the full coords I want. The coordinates are now in unbound controls on a form, and I want to pass these back to a table so I can use the data for display purposes.
Ideally I want to link to the table from a GIS and autoupdate without having to open the database (my imported data is linked in to feed the database automatically).
I have done some searching and not found a method to pass the values into a table - What is my best way of doing this automatically?
Have a table with columns of data which I want to total at the end of each row. The Calculated Field does this simply enough but it only seems to be able to add whole integers? How do I get it to accept and add values to 2 decimal places?
i have a subform which contains a lot of calculated values. fields which calculate values are located both in master form and subform. i use those calculated values in various reports where i have to insert formulas again. so i have decided to insert calculated values in table directly. i can use update query but i am unsure how to trigger it; and how to keep fields updated everytime a value is changed.
main form fields = [gsm] , [rate]
sub form fields = [pages], [qty], [gsm]*[rate]*[pages]*[qty]
i have a total in an invoice with is a tabulated form with totals in the detail footer I want to post the calculated total (control) to a table. How ia this done.
i want to insert a calculated value of a e.g field6 (field4 * field5) in forms into the database table. by using expression builders in my forms. i have made that feild and on click it shows the calculated value but on filling in the rest of the form and clicking on Insert button does not update this calculated field but iserts all other fields.
I've read over and over that calculated data is bad, that's fine with me... but here is my dillema...
I have four fields that interact with my calculation in a single form. There is only one table in the database.
AmountF (The stored data in the table) (amount financed) FC (calculated by =[amountf]*.25) (finance charge) Amount Paid (used in calculation of total due) total due (calculated by =[amountf]+[fc]-[amount paid])
The math already works, everything works. I can choose to print the form and it looks great. However, I would like for it to (when I'm done printing the form) somehow save the total due data to the AmountF column in the table. AmountF is in the same line as all the other information, so I would like it to not create a new record just update the old one.
All four of these fields have a text box in the form for themselves.
I am trying to change the data type of a field in a table from calculated to something else. It gives me the error "this data type cannot be changed once the field has been saved"
I have a simple calculated field (As "Ratio") that is readable in Access 2010 query, but crashes in Excel upon importing/running the query in an .xlsx table.
ERROR MESSAGE in EXCEL: "The query did not run, or the database table could not be opened...check database server..contact your database administrator.."
I am simply taking the cost amounts of each project record (shown as "Record Cost") and dividing it by the "Total Cost" of that project to render "Ratio".
The Total Cost is retrieved from another table.
Testing scenarios: If we exclude Ratio, the whole query import just fine. If we filter the query to one project (i.e. "Project A") the ratio field imports fine.
However, the whole unfiltered query does not with the ratio!
Other testing scenarios:
If we use a constant for the denominator AS "1" in Ratio (i.e. recordcost/1) the whole unfiltered query and ratio field imports fine.
If we use a constant for the numerator AS "1" in Ratio (i.e. 1/totalcost) the whole unfiltered query upon import crashes in Excel.
This is a mock example of the dataset, the actual query has 50K+ records:
Project Service Date Record Cost Total Cost Ratio A Welding 1/1/2012 $100 $120 83% A Plumbing 2/1/2012 $20 $120 17% B Welding 1/1/2012 $50 $75 67% B Plumbing 2/1/2012 $25 $75 33% C Welding 1/1/2012 $40 $61 66% C Plumbing 2/1/2012 $21 $61 34%
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.
