Tim, John,

Thanks, you were right. Must have missed somerhing. One more question:

If I want to list all the rows with customers with more then one one company
in this way:
de Boer, Harry  Company1
de Boer, Harry  Company2

How is the SQL statement then (in v3.3 - sorry about the wrong NG)?

Regards, Harry


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht
news:EEC9F55B-33BA-4774-9130-28EC0DEEC949@news.elevatesoft.com...
> Harry,
>
> << dbIsam 3.3. >>
>
> Wrong newsgroup.
>
> << We copied from several companies their customer tables (same structure)
> to one table with an extra field that holds the company. How do I query
this
> table so that the result is only customers that belong to more then one
> company? Postal code and SurName are the identifiers. >>
>
> What John Hay said should work, provided that the Company is unique for
each
> row.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Harry

I think you need a script for this eg

SELECT Surname, Postcode, COUNT(Surname) AS CompanyCount INTO Memory Temp
FROM Customer GROUP
BY Surname,Postcode HAVING CompanyCount > 1;
SELECT DISTINCT Surname,PostCode,CompanyName FROM Customer
JOIN Memory Temp ON Customer.Surname=Temp.Surname AND
Customer.Postcode=Temp.Postcode

John

"Harry de Boer" <harry@staaf.nl> wrote in message
news:1402B4AF-11BA-4A22-89CA-AF4F41373C96@news.elevatesoft.com...
> Tim, John,
>
> Thanks, you were right. Must have missed somerhing. One more question:
>
> If I want to list all the rows with customers with more then one one
company
> in this way:
> de Boer, Harry  Company1
> de Boer, Harry  Company2
>
> How is the SQL statement then (in v3.3 - sorry about the wrong NG)?
>
> Regards, Harry
>
>
> "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in
bericht
> news:EEC9F55B-33BA-4774-9130-28EC0DEEC949@news.elevatesoft.com...
> > Harry,
> >
> > << dbIsam 3.3. >>
> >
> > Wrong newsgroup.
> >
> > << We copied from several companies their customer tables (same
structure)
> > to one table with an extra field that holds the company. How do I query
> this
> > table so that the result is only customers that belong to more then one
> > company? Postal code and SurName are the identifiers. >>
> >
> > What John Hay said should work, provided that the Company is unique for
> each
> > row.
> >
> > --
> > Tim Young
> > Elevate Software
> > www.elevatesoft.com
> >
> >
>
>

John,

Thanks. Works fine for me!

Regards, Harry

"John Hay" <j.haywithoutdot@crbsolutionsremoveallthis.co.uk> schreef in
bericht news:8370FAC9-0F5F-4FD3-9875-25AFA4705FAA@news.elevatesoft.com...
> Harry
>
> I think you need a script for this eg
>
> SELECT Surname, Postcode, COUNT(Surname) AS CompanyCount INTO Memory Temp
> FROM Customer GROUP
> BY Surname,Postcode HAVING CompanyCount > 1;
> SELECT DISTINCT Surname,PostCode,CompanyName FROM Customer
> JOIN Memory Temp ON Customer.Surname=Temp.Surname AND
> Customer.Postcode=Temp.Postcode
>
> John
>
> "Harry de Boer" <harry@staaf.nl> wrote in message
> news:1402B4AF-11BA-4A22-89CA-AF4F41373C96@news.elevatesoft.com...
> > Tim, John,
> >
> > Thanks, you were right. Must have missed somerhing. One more question:
> >
> > If I want to list all the rows with customers with more then one one
> company
> > in this way:
> > de Boer, Harry  Company1
> > de Boer, Harry  Company2
> >
> > How is the SQL statement then (in v3.3 - sorry about the wrong NG)?
> >
> > Regards, Harry
> >
> >
> > "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in
> bericht
> > news:EEC9F55B-33BA-4774-9130-28EC0DEEC949@news.elevatesoft.com...
> > > Harry,
> > >
> > > << dbIsam 3.3. >>
> > >
> > > Wrong newsgroup.
> > >
> > > << We copied from several companies their customer tables (same
> structure)
> > > to one table with an extra field that holds the company. How do I
query
> > this
> > > table so that the result is only customers that belong to more then
one
> > > company? Postal code and SurName are the identifiers. >>
> > >
> > > What John Hay said should work, provided that the Company is unique
for
> > each
> > > row.
> > >
> > > --
> > > Tim Young
> > > Elevate Software
> > > www.elevatesoft.com
> > >
> > >
> >
> >
>
>