Re: Count vs Distinct

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

It's half the way though!!

It does return all master records that have more than 1 details record. But
I need all master records where count(details) > 1 AND distinct(details)=1
!!

This means that all the id's of the details for a particular master record
are all the same.

Ivan

"KARL DEWEY" <KARLDEWEY@xxxxxxxxxxxxxxxxxxxxxxxxx> schrieb im Newsbeitrag
news:EEB9A5EC-DEFF-4688-950F-5B83E681082F@xxxxxxxxxxxxxxxx
This does give you all the master records that have multiple detail id's
the
same.

"Ivan Debono" wrote:

That gives me all master records that have more than 1 details record.
How
can I expand the statement to get all those master records that have all
detail id's the same?

Ivan

"KARL DEWEY" <KARLDEWEY@xxxxxxxxxxxxxxxxxxxxxxxxx> schrieb im
Newsbeitrag
news:1FFAC6B0-396A-4D91-A8A0-46D17A1C0994@xxxxxxxxxxxxxxxx
Try this --
SELECT MasterID.master_ID, MasterID.ID
FROM MasterID
GROUP BY MasterID.master_ID, MasterID.ID
HAVING (((Count(MasterID.ID))>1));


"Ivan Debono" wrote:

Hi all,

I need to create a query based on a master-details tables scenario,
where a
record of the master table can have multiple details records.

For example:
Master Table has 3 records with just 1 field called id (values 1, 2
and
3)

Details table is like this
master_id id
1 10
2 10
2 20
2 30
3 20
3 20

I want to find out because master records have more than 1 detail
record
and
those detail records are the same.

From the above details table, master id 1 is not included because it
has
just 1 details record. master id 2 has 3 details records but their
ids
are
different. Only master id 3 should be returned.

Thanks,
Ivan








.

Relevant Pages

  • Re: Count vs Distinct
    ... That gives me all master records that have more than 1 details record. ... can I expand the statement to get all those master records that have all ... I need to create a query based on a master-details tables scenario, ...
    (microsoft.public.access.queries)
  • Re: Count vs Distinct
    ... FROM Master INNER JOIN Details ... It does return all master records that have more than 1 details ... "Ivan Debono" wrote: ... I need to create a query based on a master-details tables ...
    (microsoft.public.access.queries)
  • Re: Count vs Distinct
    ... It does return all master records that have more than 1 details record. ... "Ivan Debono" wrote: ... I need to create a query based on a master-details tables ...
    (microsoft.public.access.queries)
  • Re: Count vs Distinct
    ... The query should return all orders where count>1 and distinct ... FROM Master INNER JOIN Details ... It does return all master records that have more than 1 details record. ... "Ivan Debono" wrote: ...
    (microsoft.public.access.queries)
  • How to make distinct like this
    ... i have a query between two tables. ... First table has master records, ... Best Regards ... Thomas Seban ...
    (borland.public.delphi.database.ado)