change a table to hold only unique records only

[Guest]

Hi
I'm trying to write some VBA which changes a table to hold only unique records only (with all duplications etc deleted)...

I've been trying to work out how to do it in SQL first, with not so much luck. While I am sure, I can create a sequence of queries which creates a temp table from the union of the first table to itself and then another query for overwriting the original table ("BO_Extract") with the first table and then delete the temp table. It seems a little long winded. Is there a better way? I was trying to create an SQL query which was perhaps dreaming that when something like

SELECT
(SELECT BO_Extract.
FROM BO_Extrac

UNION SELECT BO_Extract.
FROM BO_Extract
INTO BO_Table

 

[TC]

Check out trhe DISTINCT and DISTINCTROW sql keywords in online help.

Also note: every table in a relational database like Access must always have
a so-called "primary key". This is a column (or combination of columns)
which uniquely identify each record in that table (ie. no two records have
the same primary key). Access will ensure that automatically, as long as you
define the primary key in Design view of the table. In that sense, a
properly designed table can >never< have duplicate records.

HTH,
TC

Hi,
I'm trying to write some VBA which changes a table to hold only unique

records only (with all duplications etc deleted)...

I've been trying to work out how to do it in SQL first, with not so much

luck. While I am sure, I can create a sequence of queries which creates a
temp table from the union of the first table to itself and then another
query for overwriting the original table ("BO_Extract") with the first table
and then delete the temp table. It seems a little long winded. Is there a
better way? I was trying to create an SQL query which was perhaps dreaming
that when something like:

 

[Guest]

The problem is its the whole record set (all fields) which requires to be unique. There is no primary key for the table, it is analysed and then indexed. The table acts an an import table for *.csv files and I wish to run the macro so that it removes all duplicates should one file overlap with another or the same file be imported more than once. 'Distinct' will work for a query running of this table, but I want the table to be replaced by 'distinct' records. I can use 'Distinct.*' to create the type of table i wish to have using :
SELECT DISTINCT BO_Extract.*
FROM BO_Extract

But what i really want is :
SELECT DISTINCT BO_Extract.* INTO BO_Extract
FROM BO_Extract

(which won't work).. but is there an alternative to saving a query into a 1) temp table, 2) then writing that table over 'BO_Extract' and 3) deleting temp table. That requires three queries to overight an existing table with unique/distinct records.

Thanks,
Marcus

 

[Alex Dybenko]

i think you can try an other way round - first make a query which returns
duplicate records, then use it as subquery to delete records which are
duplicated, except 1 (using top 1)
but probably this can be too complex, probably using temp table can be more
efficient

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

The problem is its the whole record set (all fields) which requires to be

unique. There is no primary key for the table, it is analysed and then
indexed. The table acts an an import table for *.csv files and I wish to
run the macro so that it removes all duplicates should one file overlap with
another or the same file be imported more than once. 'Distinct' will work
for a query running of this table, but I want the table to be replaced by
'distinct' records. I can use 'Distinct.*' to create the type of table i
wish to have using :

SELECT DISTINCT BO_Extract.*
FROM BO_Extract

But what i really want is :
SELECT DISTINCT BO_Extract.* INTO BO_Extract
FROM BO_Extract

(which won't work).. but is there an alternative to saving a query into a

1) temp table, 2) then writing that table over 'BO_Extract' and 3) deleting
temp table. That requires three queries to overight an existing table with
unique/distinct records.

 

[John Vinson]

The problem is its the whole record set (all fields) which requires to be unique. There is no primary key for the table

If the table has fewer than ten fields you can make ALL OF THE FIELDS
a joint, multi-field Primary Key.

 

[Peter R. Fletcher]

Access will allow you to create a Table without a primary key, but I
cannot imagine why you would ever want to, and such a Table would
certainly break the rules for relational database structure.

Check out trhe DISTINCT and DISTINCTROW sql keywords in online help.

Also note: every table in a relational database like Access must always have
a so-called "primary key". This is a column (or combination of columns)
which uniquely identify each record in that table (ie. no two records have
the same primary key). Access will ensure that automatically, as long as you
define the primary key in Design view of the table. In that sense, a
properly designed table can >never< have duplicate records.

HTH,
TC



records only (with all duplications etc deleted)...
luck. While I am sure, I can create a sequence of queries which creates a
temp table from the union of the first table to itself and then another
query for overwriting the original table ("BO_Extract") with the first table
and then delete the temp table. It seems a little long winded. Is there a
better way? I was trying to create an SQL query which was perhaps dreaming
that when something like:

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher