Microsoft Access

[jxdq]

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.

[twinnyfo]

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!

[jxdq]

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

[twinnyfo]

jxdq,

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

Private Sub cboOperation_AfterUpdate()
On Error GoTo EH
 
    Me.txtS1 = 0
    Me.txtS2 = 0
    Me.txtS3 = 0
 
    Select Case cboOperation
        Case "S1"
            Me.txtS1 = Me.txtTotal
        Case "S2"
            Me.txtS2 = Me.txtTotal
        Case "S3"
            Me.txtS3 = Me.txtTotal
    End Select
 
    Exit Sub
EH:
    MsgBox "There was an Error!"
Exit Sub
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.

[jxdq]

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?

[twinnyfo]

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!