Microsoft Access - Help on storing a calculated field from form in table
Asked By L Williams on 26-Jun-12 03:50 PM
This question concerns a calculated field on frmNCC. At the request of the dbase users, they wanted this form as part in Access instead of in Excel where it belongs. So I slaved over the myriad calculations and came up with a beautiful, perfectly functioning form... UNTIL... I remembered that I need to be have that total field [TotalNCCCost] in tblNCC because it needs to be downloaded into a spreadsheet.
Assumptions and attempts: I assume I need to create a field in tblNCC called TotalNCCCost and make that the control source for this field. And I tried that. I then moved the calculation into the AfterUpdate of the form. This caused conflict with the Me.Recalc expressions. I tried deleting the Me.Recalcs and the form ceased to function.
I am at a loss and I am under a serious time crunch. They want to go live with this puppy next week. I would greatly appreciate any help you have to offer. I am an Access novice -- especially when it comes to VBA -- so please be as explicit as possible with your advice. Thank you.
Here is a copy of the file with all of the data removed for confidentiality reasons. http://www.eggheadcafe.com/fileupload/1717688819_Nonconformance dBase no data.zip
wally eye replied to L Williams on 26-Jun-12 07:12 PM
A couple tricks to get you there:
Rename your existing TotalNCCCost to TotalNCCCostCalc, and create a new TotalNCCCost control. Hide the TotalNCCCost control (I normally change the background color to red, to highlight the fact that it is hidden). Then, create a Form_BeforeUpdate event:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.TotalNCCCost = Me.TotalNCCCostCalc
End Sub
I know you don't have time to work with this before you go live, but it is not a good model to have all your fields spelled out in this manner. Normally, I would recommend having a couple tables, say tblSupportPersonnel:
SptPrsID AutoNumber
SptPrsDesc Text - Description of support personnel
SptPrsRate Double
and tblNCCPersonnel:
SptPrsID Long - Foreign Key from tblSupportPersonnel
Qty Long
Hrs Long
TotHrs Long
Rate Double
TotalAmt Double
Or something like that. When you want to add a new support personnel, you would just add an entry to the tblSupportPersonnel. Unfortunately, it does take a lot more work up front, and is a bit more complicated. The benefits over time will add up, though.
L Williams replied to wally eye on 26-Jun-12 08:32 PM
I was beginning to ffear something like that. #1 -- Gee, it would have been a lot simpler to leave this bugger in Excel to begin with. and #2 - There really ought to be a way to tell Access, "Hey, that field there; put it over there."
Thanks for your help.
Pat Hartman replied to L Williams on 28-Jun-12 08:24 PM
I agree. There is definitely a problem with your schema. In spite of that, there is no reason to store a calculated value. Modify the query you use as the RecordSource of the form to do the calculations.
Start by Removing the calculated columns from the table.
Select ..., [LineOpTtl]*[LineOpRate] As LineOpCost, [LineLdrTtl]*[LineLdrRate] As LineLdrCost, ....
From ...
Once you've fixed the query, you can bind LineOpCost, LineLdrCost, etc to controls. When the other values are changed, Access will calculate these.
Export the query rather than the table.
In my report, I am populating a column with otherwise fieldA – fieldD Etc etc up to row 100. I now need to give a total of my 100 rows in the Page Footer. The sum function appears to apply only page footer, sum the three subtotals. thanks Matthew : I'll give it a try keywords: Microsoft Access Microsoft Access, Page, Microsoft Access, report, sum function description: MS Access : Reports Totals In my report, I
i have tables items, order and bill. . i want that when user select any item or items then its price and grand total should display on main form. . guide me In the order details subform, add a new to a new record to see the mainform control update with the new subtotal. keywords: Microsoft Access, Sum, control source, grand total description: total of rows in access i have tables items, order and bill. . i want
I'm trying to get a running total, but running into some problems. The issue I have is this: I have tables that tblTotal). The issue I'm having is that when I try to use a running total expression it uses the primary ID and not the actual cost the ID is linked understand where 2 and 6 came from. . .help us help you. . sincerely, maurice maglalang keywords: Microsoft Access, Microsoft, data tables description: Running Total I'm trying to get a running total, but running
i have tables items, order and bill. . i want that when user select any item or items then its price and grand total should display on main form. . guide me Hi, I think it depends on how the segment to populate its Price from Item table corresponding to the Item selected. The Grand total (I guess it is the total price of Items selected) could be calculated something like, SELECT SUM(Price) FROM <Item table 1) The above is applicable for single selection as well. Hope these assist. keywords: SUM, Microsoft Access, Check, Box, Click, Item, grand total, price description: total i have tables items, order and
When using Access I have a problem. How do I combine multiple entries into a single line item multiple line items I want ot to show only one Sales Order Number with a total. The fields are PO Number, Sales Order Number, Unit Price and Sum of 2% Fee it from the GROUP BY list. keywords: MIN, SUM, Sales Order Number, Sales Orders description: Access When using Access I have a problem. How do I combine multiple entries into a single line item
How Can I Do ??? I Have 3 Table Item How Can I Do This I Have Attech Data Sample Please Solve This Problem New Microsoft Access Database (2).zip Thanks Query for desired output. Also, query updated in the attached database. SELECT A.ItemID, A.ItemName, A.UnitID, Sum (B.Quantity) AS [Total Recieve], Sum (C.Quantity) AS Total Issue], Sum (B.Quantity) - Sum (C.Quantity) AS Balance FROM ([Item List] A INNER JOIN ON A.[ItemID] = C.[Item Name ] GROUP BY A.ItemID, A.ItemName, A.UnitID; New Microsoft Access Database.zip Hello, Sir I Have Check This Solution But This Is Not Correct In
Hi, Is it possible to access a tables data over multiple joins? For instance, for each row of Table A, I want the associated data from Table D. Is this possible in access? If it's not, could you suggest an alternative? Please see the example. Many Thanks ID = Table C.ID LEFT JOIN Table D ON Table C.ID = Table D.ID Access does support multiple joins and check the query you have which should be working. . . let the associated identifier. Table D then has specific items related to Table A (e.g. Total number of 'problems'). I want to sum up the problems for each association. This is why I need to access information in Table D from Table A. Please help! My mind is blown! Many Thanks
I'm trying to create a pie chart in a report in access - the chart is part of a total reward statement for employees so needs to update with every change in employee. I can Hope this helps. keywords: Chart, report description: Creating a pie chart in a report in Access I'm trying to create a pie chart in a report in access - the chart is part of a total reward statement for empl
I am encountering an odd behavior with my Microsoft Access 2007 Navigation scroll bar. I have used Access extensively over the past year and it has worked normally. The problem has surfaced over navigate up / down a large list of objects. I did a full repair on my Microsoft Office Professional 2007 but to no avail. Does anyone have any solution(s) or ideas? Regards, Mike Collard, MOS-MS Access 2007 The behavior you describe is not an error nor can you "fix" it, however is a different way of getting there. When working with large tables or results sets, Access does not immediately have all records in memory. When you drag the scroll bar to
Yes, I'm just getting around to this, and have the following questions (so far): An Access db relies upon a good number of Oracle tables. In order to query them and create a nice, bulletproof data processing routine for the user. But I can't have Access "failing to connect" willy-nilly. Is there concensus on whether it would be advantageous to an export mechanism that you could just dump the entire set of tables out to Access? I know SQL Server has this capability. I've been developing with Access since 1994. I fell in love with Access because of its ability to "Access" various ODBC data sources. Very few of my applications