Hi, here is my issue. I have a table with dates and part numbers on
it. It is a table I append each week with what is on the report I am
uploading. What I am looking for is to see how long parts have been on
the list. The problem is a part may be on the list this week, off the
list next week and then on the list for 4 weeks after. I can't do a
simple min for the date because that would give me this week. What I
am looking for is how many current consecutive weeks the part has been
on and when the first week was of the current weeks list. Any ideas?

Thanks

This may get you started on identifying where the breaks occur. It is
untested, but it may give you an idea.

SELECT A.PartNumber, A.ActiveDate
, Max(B.ActiveDate) as PriorDate
FROM TheList As A LEFT JOIN TheList As B
ON A.PartNumber = B.PartNumber
AND A.ActiveDate > B.ActiveDate
GROUP BY A.PartNumber, A.ActiveDate
HAVING A.ActiveDate -7 < Max(B.ActiveDate)


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On 2/23/2011 2:46 PM, pat67 wrote:
> Hi, here is my issue. I have a table with dates and part numbers on
> it. It is a table I append each week with what is on the report I am
> uploading. What I am looking for is to see how long parts have been on
> the list. The problem is a part may be on the list this week, off the
> list next week and then on the list for 4 weeks after. I can't do a
> simple min for the date because that would give me this week. What I
> am looking for is how many current consecutive weeks the part has been
> on and when the first week was of the current weeks list. Any ideas?
>
> Thanks

On Feb 24, 8:38*am, John Spencer <JSPEN...@Hilltop.umbc> wrote:
> This may get you started on identifying where the breaks occur. *It is
> untested, but it may give you an idea.
>
> SELECT A.PartNumber, A.ActiveDate
> , Max(B.ActiveDate) as PriorDate
> FROM TheList As A LEFT JOIN TheList As B
> ON A.PartNumber = B.PartNumber
> AND A.ActiveDate > B.ActiveDate
> GROUP BY A.PartNumber, A.ActiveDate
> HAVING A.ActiveDate -7 < Max(B.ActiveDate)
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
> On 2/23/2011 2:46 PM, pat67 wrote:
>
>
>
> > Hi, here is my issue. I have a table with dates and part numbers on
> > it. It is a table I append each week with what is on the report I am
> > uploading. What I am looking for is to see how long parts have been on
> > the list. The problem is a part may be on the list this week, off the
> > list next week and then on the list for 4 weeks after. I can't do a
> > simple min for the date because that would give me this week. What I
> > am looking for is how many current consecutive weeks the part has been
> > on and when the first week was of the current weeks list. Any ideas?
>
> > Thanks- Hide quoted text -
>
> - Show quoted text -

ok let me check it out. thanks

On Feb 24, 8:38*am, John Spencer <JSPEN...@Hilltop.umbc> wrote:
> This may get you started on identifying where the breaks occur. *It is
> untested, but it may give you an idea.
>
> SELECT A.PartNumber, A.ActiveDate
> , Max(B.ActiveDate) as PriorDate
> FROM TheList As A LEFT JOIN TheList As B
> ON A.PartNumber = B.PartNumber
> AND A.ActiveDate > B.ActiveDate
> GROUP BY A.PartNumber, A.ActiveDate
> HAVING A.ActiveDate -7 < Max(B.ActiveDate)
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
> On 2/23/2011 2:46 PM, pat67 wrote:
>
>
>
> > Hi, here is my issue. I have a table with dates and part numbers on
> > it. It is a table I append each week with what is on the report I am
> > uploading. What I am looking for is to see how long parts have been on
> > the list. The problem is a part may be on the list this week, off the
> > list next week and then on the list for 4 weeks after. I can't do a
> > simple min for the date because that would give me this week. What I
> > am looking for is how many current consecutive weeks the part has been
> > on and when the first week was of the current weeks list. Any ideas?
>
> > Thanks- Hide quoted text -
>
> - Show quoted text -

I am going to have to wait until next week when i have 3 dates. with
only 2 right now, the qry returns blanks.

On Feb 24, 10:04*am, pat67 <pbus...@comcast.net> wrote:
> On Feb 24, 8:38*am, John Spencer <JSPEN...@Hilltop.umbc> wrote:
>
>
>
>
>
> > This may get you started on identifying where the breaks occur. *It is
> > untested, but it may give you an idea.
>
> > SELECT A.PartNumber, A.ActiveDate
> > , Max(B.ActiveDate) as PriorDate
> > FROM TheList As A LEFT JOIN TheList As B
> > ON A.PartNumber = B.PartNumber
> > AND A.ActiveDate > B.ActiveDate
> > GROUP BY A.PartNumber, A.ActiveDate
> > HAVING A.ActiveDate -7 < Max(B.ActiveDate)
>
> > John Spencer
> > Access MVP 2002-2005, 2007-2010
> > The Hilltop Institute
> > University of Maryland Baltimore County
>
> > On 2/23/2011 2:46 PM, pat67 wrote:
>
> > > Hi, here is my issue. I have a table with dates and part numbers on
> > > it. It is a table I append each week with what is on the report I am
> > > uploading. What I am looking for is to see how long parts have been on
> > > the list. The problem is a part may be on the list this week, off the
> > > list next week and then on the list for 4 weeks after. I can't do a
> > > simple min for the date because that would give me this week. What I
> > > am looking for is how many current consecutive weeks the part has been
> > > on and when the first week was of the current weeks list. Any ideas?
>
> > > Thanks- Hide quoted text -
>
> > - Show quoted text -
>
> ok let me check it out. thanks- Hide quoted text -
>
> - Show quoted text -

Ok. I added data to make 3 sets of dates. I ran the query and what I
see is where a part is on the first week, then not the second, but
then again on the third, it shows twice like this

Part Date Prior Date
123 2/14/11 3/1/11
123 3/1/11 3/1/11


What I am looking for is for it to show like this

Part Earliest Date
123 3/1/11


meaning even though this part was on the 2/14 report, it wasn't on
2/22 report and thus is new for 3/1. So if it were on 3/8, 3/15 and
3/22, i would see 3/1 as the earliest date. If in fact it came off and
was not back on until the 3/22 report, the the date i would see is
3/22

Am i explaining it clearly? I know what I mean I am trying to convey
it properly.

Thanks