How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask R. Michael Your Own Question

R. Michael
R. Michael, MIS Analyst
Category: Microsoft Office
Satisfied Customers: 60
Experience:  Masters of Info. Systems; 10+ years using Microsoft Office.
71693728
Type Your Microsoft Office Question Here...
R. Michael is online now
A new question is answered every 9 seconds

How do I get a total from a multi value field in Access? I

Customer Question

How do I get a total from a multi value field in Access? I have a build list that has 1 multivalue field that contains the relationship to the inventory including part #, part name, and part price. I want to make the next column total just the part price from that multivalue field.
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  R. Michael replied 1 year ago.

Hi thank you for using JustAnswer.com. It will be my pleasure to assist you today. I will re-create your list above and post back with the solution. PS: I know the books don't offer much for multivalue fields and they are tricky but a little vba should do the trick

Customer: replied 1 year ago.
Thank you. Do you need any more info?
Expert:  R. Michael replied 1 year ago.

Can you provide that exact table names and Field names, this way I can write a custom fuction whithout you having to change them. Also the Bound column in your multi-value lookup field would be helpful

I don't want to make assumptions.

Also just an FYI, Access doesn't suppot multi-value fields in calculated field expressions, so the solution is to trigger a function in vba and populate the totals.

Customer: replied 1 year ago.
I have one table named Inventory with Part Number, Part Name, Description, Price, and QTY. the other table is Build Sheet that has the multivalue cell named Parts List that include Part Name, Description, and Price from the Inventory Table.
Expert:  R. Michael replied 1 year ago.

I saw phone request but it wouldn't let me accept. I am finalizing the function now should have result shortly

Customer: replied 1 year ago.
I don't know why. It is on and working....
Expert:  R. Michael replied 1 year ago.

I proposed same offer Offer proposed: Live discussion via phone (We can have a live discussion via phone to make it easier and faster to get all the information you need. 1-***-***-****), $10

Expert:  R. Michael replied 1 year ago.

See code below: I will also provide a sample db with the code for you.

Function SumMultiValueField()

Dim dbs As Database
Dim sqlTable As String
Dim rstTable As Recordset
Dim rstMultiValueField As Recordset
Dim sqlPartPrice As String
Dim rstPartPrice As Recordset
Dim InventoryID As Long
Dim TotalPrice As Double

Set dbs = CurrentDb
'Selects All Fields of table that contains Multi-Value Field
sqlTable = "SELECT * FROM [Build Sheet]"
Set rstTable = dbs.OpenRecordset(sqlTable, dbOpenDynaset)

'Loops through all records of table that contains Multi-Value Field
Do While Not rstTable.EOF

'Sets Multi-Value Field equal to a recordset(list of prices selected)
Set rstMultiValueField = rstTable("Parts List").Value

'Initialize Total Price (in case no items selected)
TotalPrice = 0

'Loops through Selected Values of Multi=Value Field
Do While Not rstMultiValueField.EOF

InventoryID = rstMultiValueField.Fields(0).Value
sqlPartPrice = "SELECT Inventory.Price " & _
"FROM Inventory " & _
"WHERE (Inventory.[Part Number]=" & InventoryID & ")"
Set rstPartPrice = dbs.OpenRecordset(sqlPartPrice, dbOpenDynaset)

If rstPartPrice.RecordCount > 0 Then
TotalPrice = TotalPrice + rstPartPrice("Price").Value
End If

rstMultiValueField.MoveNext
Loop

'Edits Total Field
rstTable.Edit
rstTable("Total Price").Value = TotalPrice
rstTable.Update

rstTable.MoveNext
Loop

End Function

Expert:  R. Michael replied 1 year ago.

Also here is a link to the database i mocked up. The macro calls the function that calculates total price. See Link Below

https://drive.google.com/file/d/0B3hbJgQW_-OINmQtcGVVOXhicEk/view?usp=sharing