Sum of $ in form, not in table etc.

[Matty D]

I have tried searching through these forums for an answer and I thought I found one, two actually, but I still can't seem to make things happen on my end. I appologize first off because I have very limited Access knowledge outside of some basics.

Here is my situation. I have 8 fields for cost, labeled items$1, items$2 etc. I created a total amount (total$_items) box with an expression =Nz(items$1)+Nz(items$2)... etc. in the control source, which gives me the total amount of items. This is fine but it does not write this number to the table (I now know why i.e. formula in control box) field name the same.

I found a thread very similar to mine that used a round-about way to make this work. It suggested (but did not endorse saving calculated values) to create a hidden box on the form with the same formula, then set total$_items control source back to the table. It also said you needed to go into the "current event" for the form and tell total$_items to = hidden text box. I tried this but since I have no idea how to code I simply tried to duplicate the code in the other thread using my lables and boxes etc. The end result is that the hidden box calculates things as it should but no value is displayed in the items$_total box.

I am still learning slowly how to make my database better but unfortunately I can not start from scratch as I have over 6 months of data in it now and need to start summarizing it shortly So any assistance would be greatly appreciated.

 

[Matty D]

Well I tried one last time and realised my error, I did not code things properly. Please close or erase this thread. Sorry for any inconvinince.

 

[boblarson]

No inconvenience at all. I would, however if I were you, look into Normalization as your database is definitely NOT normalized if it has repeating groups (Item1, Item2, etc.).

Also, you should NOT use special characters, or spaces, in object names or field names nor should you use Access reserved words for those as well. For example, you should not have the $ sign in your field names. That will only cause you major grief later as you try to do things because to Access the $ sign denotes a string.

Just some things to think about. I know you are saying that you have to go with what you have, but I'll tell you right now, it will become a nightmare for you, or anyone following after you, to maintain the database the way it is currently designed.

So, just let that be a warning, which you can disregard or heed, but it is up to you. I highly suggest that you get some help here in correctly designing your database so you don't suffer major headaches later trying to "band-aid fix" things in order to get the data out the way you want it.

 

[Matty D]

I really do wish I found this site before I created this monster. If I change the names of fields now it should not affect anything except in my queries and reports correct? I can do those over again fairly easily.

Normilization of items1, items2 etc., I don't follow, nor do I know what normilization means If there are some steps I can take currently to make this better I will. Is there perhaps a link to a resource that would be better then me pestering you guys??

I have learned a ton from creating this database but it has been all through self education so it is probably a real nightmare of a database compared to some one classicaly educated in them.

 

[boblarson]

Try checking this out to see if it doesn't help with Normalization:

http://www.phlonx.com/resources/nf3/

 

[Matty D]

Try checking this out to see if it doesn't help with Normalization:

http://www.phlonx.com/resources/nf3/

Thank you very much for that quick reply, I was googling as you wrote that so you saved me some time. I will read through it and start thinking about how I'm going to do this next year as I don't think I want to turn this database into more then one years worth of data. Hopefully I can learn enough before January to make a moderately better database. Thanks again.

 

[RuralGuy]

Hi Matty,
I don't want to sound like I'm piling on but in addition to Bob's excellent suggestions, you might want to reconsider storing the calculated value. In most cases it is far better and less problematic if you just calculate the value each time you need it.

 

[Matty D]

Hi Matty,
I don't want to sound like I'm piling on but in addition to Bob's excellent suggestions, you might want to reconsider storing the calculated value. In most cases it is far better and less problematic if you just calculate the value each time you need it.

I know your correct. As I was trying to find the solution to my problem it became very clear that storing the calculated value was a no-no. However I don't have the knowledge to get the 8 items to total up as well as one additional field all grouped under a series of account numbers that needs to be added in a report/query yet. The work around I'm using is not working 100% but it may have to do until I can learn how to build better expressions. Believe it or not this set up is better then what they had in place before, scary I know

 

[neileg]

However I don't have the knowledge to get the 8 items to total up as well as one additional field all grouped under a series of account numbers that needs to be added in a report/query yet.

Until you get to grips with normalisation, you can use the same formula in a calculated field in the query on which you base your report.

 

[RuralGuy]

Well good luck. Post again if you get stuck.