Should be easy - but...

[mr tom]

Let's say I have an access database of policies customers have taken out.
Primary key is member_ID (autonumber). Policy Number is also present.

Each customer has a National Insurance Number (Social Security Number for
any readers in the US). This identifies any customer uniquely, but if a
customer has more than one policy, this number will occur on multiple rows.

I need to arrive at a list of all customers, with each customer appearing
only once irrespective of how many policies they have.

I know the database is wrong - should be a 1 to many relationship, but I
have no control over that.

I can see this requires a query, (I've done a lot of simple ones before) but
this one has stumped me.

Many thanks for any guidance you can give.

Tom.

 

[Mick]

Have you tried using the 'DISTINCT' predicate in a 'select' statement?

 

[John Spencer]

It depends on whether you need other information in your list and what the
information is.

SELECT Distinct [National Insurance Number]
FROM [The table]

In the query grid, click on the top area - not on a table in the top area.
Select View: Properties
Set Unique values to Yes (one record per combination of displayed values)

If you want the policy number included then you probably need a totals
query. And you need to make a decision on which policy number you want to
display.

SELECT [National Insurance Number]
, First([policy number] as RandomlySelectedPolicyForThisNumber
FROM [The table]
GROUP BY [National Insurance Number]

 

[mr tom]

Would DISTINCT give me only customers who appear once, or would it also
include one entry for customers who appear multiple times?

If it helps, my SQL is:

SELECT PAR_Scheme.SCHEMEID, PAR_Scheme.ROW_DELETED, PAR_Scheme.SCHEMEIDENT,
PAR_Scheme.SCHEMENAME, PAR_Scheme.PRODNAME, PAR_Member.NI_NO,
PAR_Member.ROW_DELETED, PAR_Member.PUPD
FROM PAR_Scheme INNER JOIN PAR_Member ON PAR_Scheme.SCHEMEID =
PAR_Member.SCHEMEID
WHERE (((PAR_Scheme.ROW_DELETED)="N") AND ((PAR_Scheme.PRODNAME) Like "FR*")
AND ((PAR_Member.ROW_DELETED)="N")) OR (((PAR_Scheme.ROW_DELETED)="N") AND
((PAR_Scheme.PRODNAME)="CPS") AND ((PAR_Member.ROW_DELETED)="N")) OR
(((PAR_Scheme.ROW_DELETED)="N") AND ((PAR_Scheme.PRODNAME)="MGP") AND
((PAR_Member.ROW_DELETED)="N")) OR (((PAR_Scheme.ROW_DELETED)="N") AND
((PAR_Scheme.PRODNAME)="Non Stakeholder GOP") AND
((PAR_Member.ROW_DELETED)="N"));

(Don't worry that I'm covering 2 tables or the other criterea - it just
refines my output further)

Many thanks,

Tom.

 

[mr tom]

Thanks.

I tried both methods. The right click, unique values wasn't unique based on
only NI (i.e. if any other part of the queried data was different, then the
record still displayed.

I couldn't quite get to grips with the DISTINCT - I took:

SELECT PAR_Scheme.SCHEMEID, PAR_Scheme.ROW_DELETED, PAR_Scheme.SCHEMEIDENT,
PAR_Scheme.SCHEMENAME, PAR_Scheme.PRODNAME, PAR_Member.NI_NO,
PAR_Member.ROW_DELETED, PAR_Member.PUPD
FROM PAR_Scheme INNER JOIN PAR_Member ON PAR_Scheme.SCHEMEID =
PAR_Member.SCHEMEID
WHERE (((PAR_Scheme.ROW_DELETED)="N") AND ((PAR_Scheme.PRODNAME) Like "FR*")
AND ((PAR_Member.ROW_DELETED)="N")) OR (((PAR_Scheme.ROW_DELETED)="N") AND
((PAR_Scheme.PRODNAME)="CPS") AND ((PAR_Member.ROW_DELETED)="N")) OR
(((PAR_Scheme.ROW_DELETED)="N") AND ((PAR_Scheme.PRODNAME)="MGP") AND
((PAR_Member.ROW_DELETED)="N")) OR (((PAR_Scheme.ROW_DELETED)="N") AND
((PAR_Scheme.PRODNAME)="Non Stakeholder GOP") AND
((PAR_Member.ROW_DELETED)="N"));

And tried to add 'Distinct':
SELECT PAR_Scheme.SCHEMEID, PAR_Scheme.ROW_DELETED, PAR_Scheme.SCHEMEIDENT,
PAR_Scheme.SCHEMENAME, PAR_Scheme.PRODNAME, Distinct[PAR_Member.NI_NO],
PAR_Member.ROW_DELETED, PAR_Member.PUPD
FROM PAR_Scheme INNER JOIN PAR_Member ON PAR_Scheme.SCHEMEID =
PAR_Member.SCHEMEID
WHERE (((PAR_Scheme.ROW_DELETED)="N") AND ((PAR_Scheme.PRODNAME) Like "FR*")
AND ((PAR_Member.ROW_DELETED)="N")) OR (((PAR_Scheme.ROW_DELETED)="N") AND
((PAR_Scheme.PRODNAME)="CPS") AND ((PAR_Member.ROW_DELETED)="N")) OR
(((PAR_Scheme.ROW_DELETED)="N") AND ((PAR_Scheme.PRODNAME)="MGP") AND
((PAR_Member.ROW_DELETED)="N")) OR (((PAR_Scheme.ROW_DELETED)="N") AND
((PAR_Scheme.PRODNAME)="Non Stakeholder GOP") AND
((PAR_Member.ROW_DELETED)="N"));

I know it's probably a really stupid mistake - I have to confess I'm still
learning this side of things.

Tom.

It depends on whether you need other information in your list and what the
information is.

SELECT Distinct [National Insurance Number]
FROM [The table]

In the query grid, click on the top area - not on a table in the top area.
Select View: Properties
Set Unique values to Yes (one record per combination of displayed values)

If you want the policy number included then you probably need a totals
query. And you need to make a decision on which policy number you want to
display.

SELECT [National Insurance Number]
, First([policy number] as RandomlySelectedPolicyForThisNumber
FROM [The table]
GROUP BY [National Insurance Number]

Let's say I have an access database of policies customers have taken out.
Primary key is member_ID (autonumber). Policy Number is also present.

Each customer has a National Insurance Number (Social Security Number for
any readers in the US). This identifies any customer uniquely, but if a
customer has more than one policy, this number will occur on multiple
rows.

I need to arrive at a list of all customers, with each customer appearing
only once irrespective of how many policies they have.

I know the database is wrong - should be a 1 to many relationship, but I
have no control over that.

I can see this requires a query, (I've done a lot of simple ones before)
but
this one has stumped me.

Many thanks for any guidance you can give.

Tom.

 

[John Spencer]

DISTINCT returns rows where the combination of all the values in the row is
unique. So if SchemeID is 1 and Par_Member.PUPID has a value of x in one
record and a value of y in another record, you will get two rows back - the
combination of all the values is different.

SELECT DISTINCT PAR_Scheme.SCHEMEID
, PAR_Scheme.ROW_DELETED
, PAR_Scheme.SCHEMEIDENT
, PAR_Scheme.SCHEMENAME
, PAR_Scheme.PRODNAME
, PAR_Member.NI_NO
, PAR_Member.ROW_DELETED
, PAR_Member.PUPD
FROM PAR_Scheme INNER JOIN PAR_Member
ON PAR_Scheme.SCHEMEID = PAR_Member.SCHEMEID
WHERE (((PAR_Scheme.ROW_DELETED)="N") AND ((PAR_Scheme.PRODNAME) Like "FR*")
AND ((PAR_Member.ROW_DELETED)="N")) OR (((PAR_Scheme.ROW_DELETED)="N") AND
((PAR_Scheme.PRODNAME)="CPS") AND ((PAR_Member.ROW_DELETED)="N")) OR
(((PAR_Scheme.ROW_DELETED)="N") AND ((PAR_Scheme.PRODNAME)="MGP") AND
((PAR_Member.ROW_DELETED)="N")) OR (((PAR_Scheme.ROW_DELETED)="N") AND
((PAR_Scheme.PRODNAME)="Non Stakeholder GOP") AND
((PAR_Member.ROW_DELETED)="N"));

You have to make a decision on what you want returned/shown. If you don't
show the field in the select clause, it won't be used in deciding how to
make unique rows. In a query, every row must have the same fields. If it
doesn't make any difference which of the values you return then you can use
something like. If all you want is the list of UNIQUE NI_NO then you could
use the following query.

SELECT DISTINCT PAR_Member.NI_NO
FROM PAR_Scheme INNER JOIN PAR_Member
ON PAR_Scheme.SCHEMEID = PAR_Member.SCHEMEID
WHERE (((PAR_Scheme.ROW_DELETED)="N") AND ((PAR_Scheme.PRODNAME) Like "FR*")
AND ((PAR_Member.ROW_DELETED)="N")) OR (((PAR_Scheme.ROW_DELETED)="N") AND
((PAR_Scheme.PRODNAME)="CPS") AND ((PAR_Member.ROW_DELETED)="N")) OR
(((PAR_Scheme.ROW_DELETED)="N") AND ((PAR_Scheme.PRODNAME)="MGP") AND
((PAR_Member.ROW_DELETED)="N")) OR (((PAR_Scheme.ROW_DELETED)="N") AND
((PAR_Scheme.PRODNAME)="Non Stakeholder GOP") AND
((PAR_Member.ROW_DELETED)="N"));

If you want other fields returned you may have to use an aggregate query
(also known as group by query or totals query).

Thanks.

I tried both methods. The right click, unique values wasn't unique based
on
only NI (i.e. if any other part of the queried data was different, then
the
record still displayed.

I couldn't quite get to grips with the DISTINCT - I took:

SELECT PAR_Scheme.SCHEMEID, PAR_Scheme.ROW_DELETED,
PAR_Scheme.SCHEMEIDENT,
PAR_Scheme.SCHEMENAME, PAR_Scheme.PRODNAME, PAR_Member.NI_NO,
PAR_Member.ROW_DELETED, PAR_Member.PUPD
FROM PAR_Scheme INNER JOIN PAR_Member ON PAR_Scheme.SCHEMEID =
PAR_Member.SCHEMEID
WHERE (((PAR_Scheme.ROW_DELETED)="N") AND ((PAR_Scheme.PRODNAME) Like
"FR*")
AND ((PAR_Member.ROW_DELETED)="N")) OR (((PAR_Scheme.ROW_DELETED)="N") AND
((PAR_Scheme.PRODNAME)="CPS") AND ((PAR_Member.ROW_DELETED)="N")) OR
(((PAR_Scheme.ROW_DELETED)="N") AND ((PAR_Scheme.PRODNAME)="MGP") AND
((PAR_Member.ROW_DELETED)="N")) OR (((PAR_Scheme.ROW_DELETED)="N") AND
((PAR_Scheme.PRODNAME)="Non Stakeholder GOP") AND
((PAR_Member.ROW_DELETED)="N"));

And tried to add 'Distinct':
SELECT PAR_Scheme.SCHEMEID, PAR_Scheme.ROW_DELETED,
PAR_Scheme.SCHEMEIDENT,
PAR_Scheme.SCHEMENAME, PAR_Scheme.PRODNAME, Distinct[PAR_Member.NI_NO],
PAR_Member.ROW_DELETED, PAR_Member.PUPD
FROM PAR_Scheme INNER JOIN PAR_Member ON PAR_Scheme.SCHEMEID =
PAR_Member.SCHEMEID
WHERE (((PAR_Scheme.ROW_DELETED)="N") AND ((PAR_Scheme.PRODNAME) Like
"FR*")
AND ((PAR_Member.ROW_DELETED)="N")) OR (((PAR_Scheme.ROW_DELETED)="N") AND
((PAR_Scheme.PRODNAME)="CPS") AND ((PAR_Member.ROW_DELETED)="N")) OR
(((PAR_Scheme.ROW_DELETED)="N") AND ((PAR_Scheme.PRODNAME)="MGP") AND
((PAR_Member.ROW_DELETED)="N")) OR (((PAR_Scheme.ROW_DELETED)="N") AND
((PAR_Scheme.PRODNAME)="Non Stakeholder GOP") AND
((PAR_Member.ROW_DELETED)="N"));

I know it's probably a really stupid mistake - I have to confess I'm still
learning this side of things.

Tom.

It depends on whether you need other information in your list and what
the
information is.

SELECT Distinct [National Insurance Number]
FROM [The table]

In the query grid, click on the top area - not on a table in the top
area.
Select View: Properties
Set Unique values to Yes (one record per combination of displayed values)

If you want the policy number included then you probably need a totals
query. And you need to make a decision on which policy number you want
to
display.

SELECT [National Insurance Number]
, First([policy number] as RandomlySelectedPolicyForThisNumber
FROM [The table]
GROUP BY [National Insurance Number]

Let's say I have an access database of policies customers have taken
out.
Primary key is member_ID (autonumber). Policy Number is also present.

Each customer has a National Insurance Number (Social Security Number
for
any readers in the US). This identifies any customer uniquely, but if
a
customer has more than one policy, this number will occur on multiple
rows.

I need to arrive at a list of all customers, with each customer
appearing
only once irrespective of how many policies they have.

I know the database is wrong - should be a 1 to many relationship, but
I
have no control over that.

I can see this requires a query, (I've done a lot of simple ones
before)
but
this one has stumped me.

Many thanks for any guidance you can give.

Tom.

 

[mr tom]

Managed it simply by running 2 queries. First simply grabbed the ni nos and
grouped by them (gives one unique list)

Second groups by the companies and then counts the occurances in the first.

Many thanks for all the help.

Tom.