Non-Duplicate Records 1

[djwatts]

Hi All,

I have a progress database that I'm having to export into fixed width text files for data conversion to a new system. So Im using access.

I have a table that has many duplicate values in one of the reference fields. I need to pull all of the records that only have one occurrance in the table.

I have been trying to use the count expression in SQL and grouping by the duplicated data and setting the criteria to =1

But this seems to be causing problems when I make joins to other tables starts showing too many records.

Anyone shed any light on this???

Thanks

 

[M8KWR]

you could create a query use the grouping, but instead of saying group by, use the max value instead... this should only bring in one row for each value.

hope this helps.

 

[djwatts]

I want to exclude all records that have a duplicate reference and only work with the unique ones

Thanks

 

[M8KWR]

sorry mistake.... never try and answer to posts at the same time....

So in one query you have grouped the value and select to only return ones where a count = 1

And that works fine...

And when you use that query within another, this is then bringing in muliple value.

If thats the case, then the other table you are linking it to also have multiple values, of the ones within the first query that only have a unique value.

 

[PHV]

Something like this (SQL code) ?
SELECT A.*
FROM yourTable As A INNER JOIN (
SELECT theField FROM yourTable GROUP BY theField HAVING Count(*)=1
) As U ON A.theField = U.theField

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[djwatts]

Hello all,

Thanks for the responses. I think I need to give a little more detail on the problem. for the database conversion I need to re-arrange a lot of the data into fixed-width text files in a totally different format.

The table I was mentioning is a list of tennancy information

TNCY-REF TNCY-SYS-REF (PK) TNCY-STATUS
12345 1 CUR
12346 2 CUR

The TNCY-SYS-REF field is the primary key that links the person & place tables

Now this is how it should be designed. Unfortunately the design of the new system is different. We need the format below

TNCY-REF TNCY-SYS-REF PERSON1 PERSON2 PLACE
123456 1 1234 1235 500

In our current system some tennancies have one tenant and others have two. To gather all of the records with two tenants I have used the following SQL.

SELECT A.[TENANCY-REF], A.[PERSON-REF] AS Person1_No, B.[PERSON-REF] AS Person2_No
FROM TNCY AS A INNER JOIN TNCY AS B ON A.[TENANCY-REF] = B.[TENANCY-REF]
WHERE A.[PERSON-REF] < B.[PERSON-REF];

But this excludes all of the records that only have one person against the tenancy, so I need a separate query that can pull all of the tenancies with only one person assigned???

Thanks for any help on this guys

 

[PHV]

Perhaps something like this ?
SELECT A.[TENANCY-REF], A.[PERSON-REF] AS Person1_No, B.[PERSON-REF] AS Person2_No
FROM TNCY AS A LEFT JOIN TNCY AS B ON A.[TENANCY-REF] = B.[TENANCY-REF] AND A.[PERSON-REF] < B.[PERSON-REF];


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886