Calculate sum of a field within the query

  • Thread starter Thread starter auto correct is the devil
  • Start date Start date
A

auto correct is the devil

I have a query that returns the details for a failure record during a
specified time period. Where if I search between 1/1/08 and 1/31/08 the query
returns the records of all failure records during that period. The row shows
the part number and the total parts failed for that record.
I need the query to then sum the total part failures for the previous 12
months. So I would see the following:
Record # | part # | parts failed | Origination Date| Total Parts failed past
12 months
44444 | x | 4 | 1/2/08 | 200
44445 | Y |1 | 1/23/08 | 25

the total parts failed past 12 months would have to reference field part #,
origination date and parts failed fields. The only criteria I enter is the
origination date range.
 
auto said:
I have a query that returns the details for a failure record during a
specified time period. Where if I search between 1/1/08 and 1/31/08 the query
returns the records of all failure records during that period. The row shows
the part number and the total parts failed for that record.
I need the query to then sum the total part failures for the previous 12
months. So I would see the following:
Record # | part # | parts failed | Origination Date| Total Parts failed past
12 months
44444 | x | 4 | 1/2/08 | 200
44445 | Y |1 | 1/23/08 | 25

the total parts failed past 12 months would have to reference field part #,
origination date and parts failed fields. The only criteria I enter is the
origination date range.
Click to expand...


You can use a subquery to do that calculation:

SELECT [Record#]. [Part#], [parts failed],
[Origination Date],
(SELECT Sum([parts failed])
FROM thetable Ax X
WHERE X.[Part#] = T.[Part#]
And [Origination Date] Between
DateAdd("yyyy", -1, [end date]) And [end date]
) As yearfailed
FROM thetable As T
WHERE [Origination Date] Between [start date] And [end date]
 
Back
Top