CODEWITHSUNDEEP
databasems-accesscontrolscontinuous-forms
mccarthy995
mccarthy995

Reputation: 75

Sum all records that have X value in another column and display value in a textbox

for context I'm building a database for Wall Tiles that help a client know what ones to order. I have a query that pulls all the tiles the user selected on another form in a list via continuous forms. I need to add the Total of those tiles together, but show how many 8mm, 10mm, and 12mm tiles he needs to order in a textbox that's in the header of a form that the query is bound to.

PickQuery returns something like this:

Code Total Size
B23432 4 8mm
B22343 3 8mm
B32423 2 12mm

The intended outcome

8mm 10mm 12mm
7 0 2

I tried this in the textboxes within the form header:

8mm 10mm 12mm
=Sum(IIf([Size]="8mm",[Total],0)) =Sum(IIf([Size]="10mm",[Total],0)) =Sum(IIf([Size]="12mm",[Total],0))

Also:

8mm 10mm 12mm
=Sum(IIf([PickQuery].[Size]="8mm",[PickQuery].[Total],0)) =Sum(IIf([PickQuery].[Size]="10mm",[PickQuery].[Total],0)) =Sum(IIf([PickQuery].[Size]="12mm",[Total],0))

What I'm actually getting is this:

8mm 10mm 12mm
#Error #Error #Error

Any Ideas?

Upvotes: 0

Views: 62

Answers (0)

Related Questions