Hello;
Stats
D&
v4.25 b7

What I am trying to do is to identify all items within a DHA that have the same MFCNo asigned. There should be a unique MFCNo for each items with the DHA. Or to put it another way
someone at the data entry level didn't care less if the MFCNo was accurate.
I'm getting a littil punch drunk on this.

Thanks in advance

Allan

here is my code

select Distinct dha,mfcno, ItemNo, count(ItemNo) as CItemNo, OID AS LinkOID
into memory\duptmp
from SITESItem
where mfcno is not null
group by  dha,mfcno
having CItemNo > 1;
create index myindex on memory\duptmp (mfcno);

Select mfcno,* from SITESItem
join "\memory\duptmp" on "\memory\duptmp".[mfcno] = [SITESItem].[mfcno]
ORDER BY MFCNO;

Your concept is OK, but selecting distinct will negate the logic, since it
eats the duplicates. You WANT the duplicates, so that you get count > 1.

Robert

"Allan Brocklehurst" <brock@ns.sympatico.ca> wrote in message
news:586443C6-80BE-4AA4-81CE-E46C8B2AEA97@news.elevatesoft.com...
> Hello;
> Stats
> D&
> v4.25 b7
>
> What I am trying to do is to identify all items within a DHA that have the
> same MFCNo asigned. There should be a unique MFCNo for each items with the
> DHA. Or to put it another way
> someone at the data entry level didn't care less if the MFCNo was
> accurate.
> I'm getting a littil punch drunk on this.
>
> Thanks in advance
>
> Allan
>
> here is my code
>
> select Distinct dha,mfcno, ItemNo, count(ItemNo) as CItemNo, OID AS
> LinkOID
> into memory\duptmp
> from SITESItem
> where mfcno is not null
> group by  dha,mfcno
> having CItemNo > 1;
> create index myindex on memory\duptmp (mfcno);
>
> Select mfcno,* from SITESItem
> join "\memory\duptmp" on "\memory\duptmp".[mfcno] = [SITESItem].[mfcno]
> ORDER BY MFCNO;
>

Thanks Robert;

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote:

Your concept is OK, but selecting distinct will negate the logic, since it
eats the duplicates. You WANT the duplicates, so that you get count > 1.

Robert

"Allan Brocklehurst" <brock@ns.sympatico.ca> wrote in message
news:586443C6-80BE-4AA4-81CE-E46C8B2AEA97@news.elevatesoft.com...
> Hello;
> Stats
> D&
> v4.25 b7
>
> What I am trying to do is to identify all items within a DHA that have the
> same MFCNo asigned. There should be a unique MFCNo for each items with the
> DHA. Or to put it another way
> someone at the data entry level didn't care less if the MFCNo was
> accurate.
> I'm getting a littil punch drunk on this.
>
> Thanks in advance
>
> Allan
>
> here is my code
>
> select Distinct dha,mfcno, ItemNo, count(ItemNo) as CItemNo, OID AS
> LinkOID
> into memory\duptmp
> from SITESItem
> where mfcno is not null
> group by  dha,mfcno
> having CItemNo > 1;
> create index myindex on memory\duptmp (mfcno);
>
> Select mfcno,* from SITESItem
> join "\memory\duptmp" on "\memory\duptmp".[mfcno] = [SITESItem].[mfcno]
> ORDER BY MFCNO;
>