You can use a sub-query that looks like the following for the current month.
SELECT Sum([qty failed])
FROM SomeTable as Temp
WHERE Temp.[Part Num] = SomeTable.[Part Num]
AND [Orig Date] Between DateSerial(Year(Date()),Month(Date()),1) AND
DateSerial(Year(Date()),Month(Date())+1,0)
For the prior twelve months (that is May 1, 2008 to April 30,2009) you would
use something like
SELECT Sum([qty failed])
FROM SomeTable as Temp
WHERE Temp.[Part Num] = SomeTable.[Part Num]
AND [Orig Date] Between DateSerial(Year(Date()),Month(Date())-12,1) AND
DateSerial(Year(Date()),Month(Date()),0)
In the query Design grid, you would type the above in two field boxes and add
parentheses around the statements.
Another solution would be to build a query that would return the needed values
and then use that query joined to your current query. That query would look
like the following:
SELECT [Part No],
, Sum(IIF([Orig Date]>=DateSerial(Year(Date()),Month(Date()),1),[qty
failed],0)) as CurrentMonth
,Sum(IIF([Orig Date]<DateSerial(Year(Date()),Month(date()),1),[qty failed],0))
as PriorYear
FROM SomeTable [Orig Date] Between DateSerial(Year(Date()),Month(Date())-12,1)
AND DateSerial(Year(Date()),Month(Date())+1 ,0)
GROUP BY [Part No]
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I will try to do whatever that is. I am a super-novice.
Data structure:
[problem number][part num][orig date][qty failed]
the data is entered using a form. There are about 30 other fields that are
inconsequential to the discussion. The entry represents one event of a part
failure in a mfg setting. So it is basically a table populated by a report of
a non-confomance. Each report fails a unique part number. I am trying to
tally the qty failed per part number for the month and 12 months prior. Does
this clarify?
auto correct is the devil said:
I run a query that returns problems logged in a month. Each returned row has
the data from each problem including the number of failed parts and the part
number per that line item.
I would like to total the total qty of parts failed in the last 12 months
for that given line item.
The eventual outcome is to make a report that has a header (grouped by part
number) that counts qty failed for the month followed by qty failed 12
months. Then the detail would show the individual records. Not sure if doing
this through a subquery would be the way to do this.
