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?