Distinct Record Query

  • Thread starter Thread starter selgin
  • Start date Start date
S

selgin

I have the following SQL that I want to use to make a table with only
distinct records from the first table. It works fine except that
occasionally patients have two records with different [PT MSS ID] fields.
Otherwise the two records are identical.

I appreciate all the help I have found here!

Here is the code.
SELECT DISTINCT [CHCS MAIN TABLE].[ENTRY DATE], [CHCS MAIN TABLE].[PT LAST
NAME], [CHCS MAIN TABLE].[PT FIRST NAME], [CHCS MAIN TABLE].[PT MI], [CHCS
MAIN TABLE].[PT MSS ID], [CHCS MAIN TABLE].[PT DOB], INTO [PRINT TABLE]

FROM [CHCS MAIN TABLE];



TIA
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Sounds like the table doesn't have a very good primary key, or unique
index, that would prevent duplicate patients. Perhaps you could change
that. In any event you'd have to do data clean up before applying the
PK or index; and, before copying the data from one table to another.

Without adding the PK or unique index you could try DISTINCTROW, or just
GROUP BY all the columns and exclude PT MSS ID, if that column's data
isn't required.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQksJF4echKqOuFEgEQIFXgCfcr9bZJ2xrVd1qQxBaReTngz3PmUAoKmH
wryvYFOuvBGqSR7nrJaCnHe0
=14BO
-----END PGP SIGNATURE-----
 
I will surely give it a shot. Thanks much!
MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Sounds like the table doesn't have a very good primary key, or unique
index, that would prevent duplicate patients. Perhaps you could change
that. In any event you'd have to do data clean up before applying the
PK or index; and, before copying the data from one table to another.

Without adding the PK or unique index you could try DISTINCTROW, or just
GROUP BY all the columns and exclude PT MSS ID, if that column's data
isn't required.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQksJF4echKqOuFEgEQIFXgCfcr9bZJ2xrVd1qQxBaReTngz3PmUAoKmH
wryvYFOuvBGqSR7nrJaCnHe0
=14BO
-----END PGP SIGNATURE-----

I have the following SQL that I want to use to make a table with only
distinct records from the first table. It works fine except that
occasionally patients have two records with different [PT MSS ID] fields.
Otherwise the two records are identical.

I appreciate all the help I have found here!

Here is the code.
SELECT DISTINCT [CHCS MAIN TABLE].[ENTRY DATE], [CHCS MAIN TABLE].[PT
LAST NAME], [CHCS MAIN TABLE].[PT FIRST NAME], [CHCS MAIN TABLE].[PT MI],
[CHCS MAIN TABLE].[PT MSS ID], [CHCS MAIN TABLE].[PT DOB], INTO [PRINT
TABLE]
FROM [CHCS MAIN TABLE];
Click to expand...
Click to expand...
 
Try using a totals query and grouping on all the fields but the one. Use First
for that to get one of the values associated

SELECT [Entry Date], ..., First([Pt Mss id]
FROM ...
GROUP BY [Entry Date], ...
 
Back
Top