Solved How to get a sum into a table (1 Viewer)

JS Smith

New member
Local time
Yesterday, 18:42
Joined
Mar 31, 2021
Messages
15
Hi! I'm an Access novice and got a new job. I'm trying to reduce some of the duplication to get more efficient. They have Access but nothing like Sharepoint or other PM software. Eventually, there will be a nice looking Form that can be printed as a Change Order for contractors while feeding a Table for reports and such. I'd like a couple of formulas or something to feed a couple fields in the Table. Not sure my explanation is clear so here's a screen shot with an idea of what I'm after. Everything I've found is experts saying never do calculations in Access, but trying to reduce steps, forgo calculator, and reduce data entry errors to complete the task.

Thanks, in advance, for any help you can provide! :)

1617231349863.png
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:42
Joined
Oct 29, 2018
Messages
15,158
Hi. Welcome to AWF!

If you want to use a database, instead of Excel, you would want to avoid storing calculated values in the table. If you want to perform some calculations and display the results, you can use a query.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:42
Joined
Feb 19, 2002
Messages
31,926
There are lots of reasons for not storing calculated results. Do some reading on normalization or just take our word for now. Do NOT store the results of calculations. As theDBguy suggested, you can include a calculated field in a query and bind it to your form, when you change an argument of the calculation, the form will automagically reflect the new result. Another option is to perform the calculations in the form. There are pros and cons of each and one or the other might turn out to be better/easier for you. To display the results of a calculation, you would use an unbound control. The ControlSource would be the calculation. ForExample:
=FieldA * (FieldB + FieldC)

Again, if any of the three fields is changed on the form, the calculated result will automagically reflect that. That is the power of bound forms.
 

arnelgp

error reading drive A:
Local time
Today, 08:42
Joined
May 7, 2009
Messages
12,703
but first of all add Autonumber field, so it will be easier to Get the Previous Row values.
 

JS Smith

New member
Local time
Yesterday, 18:42
Joined
Mar 31, 2021
Messages
15
There are lots of reasons for not storing calculated results. Do some reading on normalization or just take our word for now. Do NOT store the results of calculations. As theDBguy suggested, you can include a calculated field in a query and bind it to your form, when you change an argument of the calculation, the form will automagically reflect the new result. Another option is to perform the calculations in the form. There are pros and cons of each and one or the other might turn out to be better/easier for you. To display the results of a calculation, you would use an unbound control. The ControlSource would be the calculation. ForExample:
=FieldA * (FieldB + FieldC)

Again, if any of the three fields is changed on the form, the calculated result will automagically reflect that. That is the power of bound forms.
Thanks for adding some facts along with your opinion. I try to not just take someone's word for anything, that's lazy and not education. Usually, I experiment to see if an opinion holds water or applies to my situation. My previous Access experience is limited to using a database in Excel.

So, how would one calculate right in a table? I want to experiment; see how it and your suggestions behave. In the meantime, I'll study up on normalization and ControlSource.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:42
Joined
Feb 28, 2001
Messages
19,929
how would one calculate right in a table

One almost NEVER calculates into a table field. The ONLY except is if for some reason you are capturing a time-sensitive computation for which you would not be able to retain all of the inputs to that calculation.

Remember this incredibly useful and important fact: Forms and reports and VBA-based recordset operations DON'T CARE whether you are working on a table or a query, with the exception of a non-updateable query feeding an editing form. Reports ABSOLUTELY don't care since reports don't update stuff anyway. So if there is a computation to be used, use in an a query - or, if a form is involved, have the form do the computation for you when you open it.

For this reason, you should consider queries as one of the two 'workhorses' of Access. Sure, reports make nice presentations, but if you are working INSIDE the database to manipulate something, odds are it will be a query or VBA that does the work.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:42
Joined
Feb 19, 2002
Messages
31,926
I think doc may have misunderstood the question. To calculate in a query:

Select fld1, fld2, fld3 * (fld4 + fld5) As calcA, fldx from your table; In your form, CalcA will be part of the bound fields and so you can display it in a textbox but it will NOT be updatable. However, changing any of the three fields in the calculation will be reflected in what you see in the control bound to CalcA.
 

JS Smith

New member
Local time
Yesterday, 18:42
Joined
Mar 31, 2021
Messages
15
One almost NEVER calculates into a table field. The ONLY except is if for some reason you are capturing a time-sensitive computation for which you would not be able to retain all of the inputs to that calculation.

Remember this incredibly useful and important fact: Forms and reports and VBA-based recordset operations DON'T CARE whether you are working on a table or a query, with the exception of a non-updateable query feeding an editing form. Reports ABSOLUTELY don't care since reports don't update stuff anyway. So if there is a computation to be used, use in an a query - or, if a form is involved, have the form do the computation for you when you open it.

For this reason, you should consider queries as one of the two 'workhorses' of Access. Sure, reports make nice presentations, but if you are working INSIDE the database to manipulate something, odds are it will be a query or VBA that does the work.
Am I connecting the dots correctly? Tables are just data repositories? I can/should build a query, using data in the existing Project and Contract tables, which will feed this future End-user Form, the form will do the heavy lifting, calculations, look pretty, etc., then the form can append data to the new Change Order table?
 

JS Smith

New member
Local time
Yesterday, 18:42
Joined
Mar 31, 2021
Messages
15
I think doc may have misunderstood the question. To calculate in a query:

Select fld1, fld2, fld3 * (fld4 + fld5) As calcA, fldx from your table; In your form, CalcA will be part of the bound fields and so you can display it in a textbox but it will NOT be updatable. However, changing any of the three fields in the calculation will be reflected in what you see in the control bound to CalcA.
Thank you for your help! I have much studying to do! Learning Access is like pulling a loose sweater thread...
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:42
Joined
Feb 19, 2002
Messages
31,926
You're welcome. Looks like you are following the right thread.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:42
Joined
Feb 28, 2001
Messages
19,929
Am I connecting the dots correctly? Tables are just data repositories? I can/should build a query, using data in the existing Project and Contract tables, which will feed this future End-user Form, the form will do the heavy lifting, calculations, look pretty, etc., then the form can append data to the new Change Order table?

Sounds good to me. If I originally misunderstood your question, I apologize - but if you got at least part of that from my earlier answer, then you got my message anyway.
 

JS Smith

New member
Local time
Yesterday, 18:42
Joined
Mar 31, 2021
Messages
15
Hmm, Doc you got me thinking. Is what I want possible in Access? My last job, I spent 3 years where the most powerful tool available was Excel 2010 so I'm in the habit of making something out of nothing, usually with VBA so that may explain this goofy thinking...

I have 3 tables, 2 full of data and one new (Projects, Contracts and Changes, respectively). I made a query to tie the project and contract data.

My thought was to have a form with a print control or export to a pdf that has the look and feel of a Change Order where:
  1. The user enters a Contract #
    1. The query's corresponding name, address, city , state, zip, original contract amount, etc for both the project and contract would populate.
  2. The user would then complete one drop down, two dollar amounts and a couple text fields.
    1. The form would do some math using the original contract amount (end-users can be really bad at math)
  3. Whatever the user was does in the form should append to the Changes table
I know it's a DBA sin to duplicate data in tables so I'd need something the user can think of as a database which show all the data that was in the completed form (Project and Contracts data as well as the user's data input and the subsequent calculation). Hope that's not too crazy and is a clearer explanation. :)
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:42
Joined
Feb 19, 2002
Messages
31,926
Typically, the RecordSource query of a form selects the main record to be updated. Other tables in the join are there for reference. Even though Access allows you to update multiple tables using the same form, it is poor practice to do so and in fact, best practs is to LOCK any control bound to other than the main table. For example. On an order form, you want to select a customer using a combo but you also want to show other info from the customer record so you left join tblOrder to tblCustomer. That way, when the user selects the customer from the combo, the form automagically fills with the additional customer information. All fields showing customer data EXCEPT for the combo, should be locked because you do not want the user to accidentally update them. They need to go back to the Customer form to update customer data.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:42
Joined
Feb 28, 2001
Messages
19,929
@JS Smith

First and foremost, if you can imagine the data manipulations you want (and DOCUMENT them so you don't forget later), you can probably do it in Access. Will it be easy? Can't tell you because I don't have insight into your imagination.

You are correct, it is a DBA sin to duplicate data - but there are loopholes and exceptions everywhere. There are also workarounds that avoid duplication in some cases.

Duplicating data is not a sin if there is some temporary or variable nature to certain data elements and you want to capture a snapshot of data at a certain time. Making a copy is one way to do that. The implication is that if things change over time, you need to duplicate CURRENT values knowing that the source table might change later. E.g. a cost table where you capture current costs and when you make a new order that gets approved, you have to go by those recorded costs even if the contributing costs change next week.

When data sets are either invariant over time OR you have a history of their changes complete with effective dates, there are ways to use complex queries to tie everything together so that you DON'T duplicate your data. This implies that you can keep records of your cost changes. Usually it is an either/or situation - make copies of current data OR track changes in the contributing data. When you CANNOT keep track of changes, the old "thou shalt not keep duplicated data" rule goes out the window. Hope this helps you understand what is going on with that rule.

Pat Hartman was talking about using JOIN queries so that you can look up stuff "automagically" behind the scenes by tying tables together based on information common to both tables. This "tying together" is the primary function of a JOIN. Pat is one of our most experienced members; she can offer a wealth of information and suggestions for best practices. For the cases where you want a selection made by a user to cause other fields to be filled in without further user action (see your post #13, 1st of your numbered items), a combo box and at worst a little bit of VBA code would probably do that item completely.

Can't speak to the #2 item of same post other than to say "Probably not that hard."

The #3 item might or might not be tricky. It will be a matter of exactly what you wanted that to look like, but I assure you Access can do that. The trick will be to FIRST decide what you want to do AND what it should look like. THEN you can discuss implementation issues.

It's like my late father-in-law used to say. (He was a carpenter/contractor who built a lot of houses.) "Measure twice, cut once." It applies here as "Think twice, implement once."
 

JS Smith

New member
Local time
Yesterday, 18:42
Joined
Mar 31, 2021
Messages
15
Pat & Doc, thanks so much for your guidance. Might have to take this on in steps and create as I pick up skills.

I'm trying to improve a process where the user starts a mail merged word doc using some Access data, edits the word doc to include data from a bunch of pdfs, turn that word doc into a pdf then type up the data contained in the new word/pdf into one of 12 excel spreadsheets. This level of convolution drives the OCD business manager in me nearly insane!

I've data mined all the Excel spreadsheets, created 4 Tables (data separated ;)), created a more robust Mail Merge to feed all the variable data in one go. I've also got an Excel with connections to the new Tables so I can get data onto the user's existing spreadsheet using the same look and feel without any undo "something's changing!!!" panic.

I've copied the live DB to do my learning and BETA testing. SQL is a bit to get my head around. Those JOIN statements are tricky! Understanding Einstein's quote perfectly "The more I learn, the more I realize I don't know."

Thanks again, I'll mark this one as solved but am sure I'll be back with more questions.
 
Last edited:

Users who are viewing this thread

Top Bottom
日韩精品亚洲专区在线影院