Help on calculating multiple buckets on a form

[andy-29]

I have a Size Breakdown with 8 Buckets


I'd like to Sum the contents of the buckets and divide that to Total Yards then have the result saved to a field(Per Garment) in a table (Marker Details Table).
Per Garment = Size Breakdown / Total Yards (25 / 7 = 3.57)
I have attached my db.

Thanks!

[June7]

This will require code. I only use VBA. Since the field you want to save value to is in the form RecordSource:

Me.[Per Garment] = (Me.[cboSizeBrkdn].Column(1) + Nz(Me.[cboSizeBrkdn].Column(2)) + Nz(Me.[cboSizeBrkdn].Column(3)) + Nz(Me.[cboSizeBrkdn].Column(4)) + Nz(Me.[cboSizeBrkdn].Column(5)) + Nz(Me.[cboSizeBrkdn].Column(6)) + Nz(Me.[cboSizeBrkdn].Column(7)) + Nz(Me.[cboSizeBrkdn].Column(8))) / [Total Yards]

The real trick is figuring out what event to put this code in.

Alternatives that don't require code:

1. save the [Size Brkdn ID] as foreign key in Marker Details table. Then in a query join the tables on the pk/fk fields. This will make all info of the related records available for calcs on-the-fly when needed

2. combobox RowSource has a column that is a sum of the bucket fields and set this column as the BoundColumn

Why does each field of a Size Brkdn record have same value?

Advise no spaces, special characters, punctuation (underscore is exception) in any names.

[andy-29]

Ok, will redesign. Did you mean to not have spaces on table, querry, report, form names?? Also table Field Names?

[June7]

Yes, recommend no spaces in any names - all you listed.

[andy-29]

Hi June7,

I tried the VB Code and it concatinated the result rather than add the contents of each column.
Am I missing something?


Private Sub cboSizeBrkdn_AfterUpdate()

Me.[TotalGarments] = (Me.[cboSizeBrkdn].Column(1) + Nz(Me.[cboSizeBrkdn].Column(2)) + Nz(Me.[cboSizeBrkdn].Column(3)) + Nz(Me.[cboSizeBrkdn].Column(4)) + Nz(Me.[cboSizeBrkdn].Column(5)) + Nz(Me.[cboSizeBrkdn].Column(6)) + Nz(Me.[cboSizeBrkdn].Column(7)) + Nz(Me.[cboSizeBrkdn].Column(8)))

End Sub

[June7]

I never had to do anything like this with combobox and give up trying to add the columns. Not sure why insists on concatenating. The + is a valid concatenation operator held over from ancient BASIC. For some reason the combobox columns are treated as text even if the data are numbers and since the data is text the + operator defaults to concatenation in the expression.

Do this instead. Calculate another column for the combobox with:
SumBuckets: Nz([SBBucket1])+Nz([SBBucket2])+Nz([SBBucket3])+Nz([SBBucket4])+Nz([SBBucket5])+Nz([SBBucket6])+Nz([SBBucket7])+Nz([SBBucket8])

Change the ColumnCount and ColumnWidths properties. The new column can be 0 width. Refer to the new column for the bucket total.

Side Note: I tried to replicate this issue in Access 2007 with my db and couldn't. Also, tested in Access 2010 with another db and the issue does present, even worse. If more than one record had the same value in first column and I selected the second record, the values of the first record were used in the expression - weird. Oh well, enough!