By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,505 Members | 1,511 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,505 IT Pros & Developers. It's quick & easy.

Microsoft Access

P: 3
Hey all, hopefully someone can guide me in the right direction. So in this table, under Operations there are 5 selections you can select. Whichever option is selected along with the Total, I need it to go to the appropriate field. So far I have tried and did not work:

IIf([Operation]="S1","Total","0")

I attached images to help.

Attached Images
File Type: jpg 2.jpg (13.7 KB, 140 views)
File Type: jpg 111111111111.jpg (19.4 KB, 141 views)
Jan 1 '18 #1
Share this Question
Share on Google+
5 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,063
jxdq,

Welcome to Bytes!

Are you working with your table in Datasheet view? In order for such calculations to occur, you need to use a Form.

Hope that hepps!
Jan 2 '18 #2

P: 3
Thank you for responding sir! I do have a form.

What do I add inside of design view of "Operations" to make it go to the proper field after being selected and hitting submit?

http://imgur.com/a/fHLqP
Jan 2 '18 #3

twinnyfo
Expert Mod 2.5K+
P: 3,063
jxdq,

It depends on the names of the controls on your Form, but generically:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboOperation_AfterUpdate()
  2. On Error GoTo EH
  3.  
  4.     Me.txtS1 = 0
  5.     Me.txtS2 = 0
  6.     Me.txtS3 = 0
  7.  
  8.     Select Case cboOperation
  9.         Case "S1"
  10.             Me.txtS1 = Me.txtTotal
  11.         Case "S2"
  12.             Me.txtS2 = Me.txtTotal
  13.         Case "S3"
  14.             Me.txtS3 = Me.txtTotal
  15.     End Select
  16.  
  17.     Exit Sub
  18. EH:
  19.     MsgBox "There was an Error!"
  20. Exit Sub
  21. End Sub
There is a slight challenge with this particular arrangement, however. By placing the value of Total into S1..S3, you are introducing redundant information into your table, which is generally a no-no. If you were to generate a Report based on the data in your table, you can accomplish the same results through a query. Generally speaking, you should not maintain any calculated values as permanent values in your Table, unless you don't actually save the values from which the total is calculated.

I hope this makes sense.

Let me know if this hepps.
Jan 2 '18 #4

P: 3
To take away redundant data, is it possible for me to just remove the Total, have the data multiple andthe total go into whichever field under operation is selected?

https://imgur.com/UXxmXd8

https://imgur.com/a/k4cje

How would you do it?
Jan 2 '18 #5

twinnyfo
Expert Mod 2.5K+
P: 3,063
jxdq,

I will give my recommendation and then give the theoretical and practical reasons for this. Other mods and experts are free to chime in, but I think I am on pretty solid ground.

First, if the Total field is only going to go into one of your three other fields, I would have a Total field with the amount and an Operations Field, with the type of operation indicated.

Second, the theory. If you have three separate fields for S1, S2 and S3, then, since the data is only ever stored in one of these fields, you now no longer have redundant data, but empty space. Again, not recommended. By keeping the Total field and Operation fields, you now have 1) no redundant data, and 2) no empty space.

Third, the practical. Right now, you only have three operations: S1, S2 and S3, correct? What happens when you add an operation (this also falls into the realm of theory). What if you had 20 types of operations? This would make your table very wide for the different operation types having a separate field -- AND 95% of those fields would be empty all the time. Not to mention that every time you added another operation type, you would have to restructure your data tables. My recommendation precludes all the restructure and allows for future expansion based upon your needs and still adheres to sound DB practices.

Hope this hepps!
Jan 3 '18 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Browse more Microsoft Access / VBA Questions on Bytes