Prev: Update Query for Current Record Only
Next: Full text not showing in report
From: Jean on 24 Jun 2008 18:00
Hi I have the following query that i am trying to make it work:

SELECT [MS Incidents May-June 08].[Contract #], [MS Incidents May-June
08].[Product Level 2]
FROM [MS Incidents May-June 08] INNER JOIN [MS Incidents May-June 08] AS [MS
Incidents May-June 08_1]
ON [MS Incidents May-June 08].[Contract #] = [MS Incidents May-June
08_1].[Contract #] AND [MS Incidents May-June 08].[Product Level 2] =[MS
Incidents May-June 08_1].[Product Level 2] AND [MS Incidents May-June
08].[Transaction Date]>=[MS Incidents May-June 08_1].[Transaction Date]
And [MS Incidents May-June 08].[Transaction Date]<=[MS Incidents May-June
08_1].[Transaction Date]+15
GROUP BY [MS Incidents May-June 08].[Contract #], [MS Incidents May-June
08].[Product Level 2]
HAVING Count(*)>=7

I need to keep the Contract# and Product Level 2 of records that have:
1. The same Contract# and
2. The same Product Level 2 and
3. the # of such records are higher or equal to 7 where the "transaction
date" is between a 15 day range.

The Transaction date has the following format: 6/15/2008 0:57

I am not sure why the query is not working!

Any help is greatly appreciated!

Thanks!


From: KARL DEWEY on 24 Jun 2008 18:59
You did not say what the results were. No records, errors, etc.

Make a copy and then try taking it apart piece by piece.
First remove the HAVING Count(*)>=7 requirement.
Next try reducing the spread. If necessary reduce spread to no days -
date=date1.

--
KARL DEWEY
Build a little - Test a little


"Jean" wrote:

> Hi I have the following query that i am trying to make it work:
>
> SELECT [MS Incidents May-June 08].[Contract #], [MS Incidents May-June
> 08].[Product Level 2]
> FROM [MS Incidents May-June 08] INNER JOIN [MS Incidents May-June 08] AS [MS
> Incidents May-June 08_1]
> ON [MS Incidents May-June 08].[Contract #] = [MS Incidents May-June
> 08_1].[Contract #] AND [MS Incidents May-June 08].[Product Level 2] =[MS
> Incidents May-June 08_1].[Product Level 2] AND [MS Incidents May-June
> 08].[Transaction Date]>=[MS Incidents May-June 08_1].[Transaction Date]
> And [MS Incidents May-June 08].[Transaction Date]<=[MS Incidents May-June
> 08_1].[Transaction Date]+15
> GROUP BY [MS Incidents May-June 08].[Contract #], [MS Incidents May-June
> 08].[Product Level 2]
> HAVING Count(*)>=7
>
> I need to keep the Contract# and Product Level 2 of records that have:
> 1. The same Contract# and
> 2. The same Product Level 2 and
> 3. the # of such records are higher or equal to 7 where the "transaction
> date" is between a 15 day range.
>
> The Transaction date has the following format: 6/15/2008 0:57
>
> I am not sure why the query is not working!
>
> Any help is greatly appreciated!
>
> Thanks!
>
>
 | 
Pages: 1
Prev: Update Query for Current Record Only
Next: Full text not showing in report