Summing a query column help

[Eskoraczewski]

Hi, so I have four columns of data:

week number
week end date
id
totalbyweek

basically I wNt to sum the "totalbyweek" column if the ID ="e" and the id= "pg"- and then sum by week number


so instead of having this example
week 1 - pg - 1
week 1 - e - 2


week 1 - n - 2


it reads as:
week 1 - e - 3
week 1 - n - 2


i basically need to sum the e and n by week but the e needs to be the sum of e and pg... Any help?

[June7]

Build an aggregate (Totals) query with conditional expressions.

SELECT [week number], Sum(IIf([ID]="e" Or [ID]="pg", [totalbyweek], 0)) AS SumEPG, Sum(IIf([ID]="n", [totalbyweek], 0)) AS SumN FROM tablename GROUP BY [week number];


Or build a report using Grouping & Sorting features with aggregate calcs in footers. This will allow display of detail records as well as summary calcs.

Create a Group on week number. Use expressions in textboxes in Group and/or Report footers.

=Sum(IIf([ID]="e" Or [ID]="pg", [totalbyweek], 0))

=Sum(IIf([ID]="n", [totalbyweek], 0))