Avoiding duplicates

[Stapes]

Hi
I have a code procedure that is building a file from a series of
operations. I wanted to avoid the possibility of duplication, so I set
up the first field as indexed.
However, when I run SELECT ...INTO, it appears to delete my table
definition & make a new one. My index is gone.
How damned frustrating.

Stapes

 

['69 Camaro]

Hi, Stapes.

However, when I run SELECT ...INTO, it appears to delete my table
definition & make a new one. My index is gone.

It does delete the original table. Instead of a make table query, use an
append query, which uses INSERT INTO syntax. For example, try:

INSERT INTO tblReceiveData
SELECT *
FROM tblData
ORDER BY ID;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Stapes]

Hi, Stapes.


It does delete the original table. Instead of a make table query, use an
append query, which uses INSERT INTO syntax. For example, try:

INSERT INTO tblReceiveData
SELECT *
FROM tblData
ORDER BY ID;

HTH.
Gunny

Seehttp://www.QBuilt.comfor all your database needs.
Seehttp://www.Access.QBuilt.comfor Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.comhttp://www.Access.QBuilt.com/html/expert_contributors2.htmlfor contact
info.







- Show quoted text -

Hi
I have tried that - it didn't work. This is my command: -

SELECT TTEMP_CampaignRun01.PK_Comp, TTEMP_CampaignRun01.FK_SchoolType,
TTEMP_CampaignRun01.TLK_Cat, TTEMP_CampaignRun01.Country ,
TTEMP_CampaignRun01.Cnty, TTEMP_CampaignRun01.Town,
TTEMP_CampaignRun01.Pocd INTO TTEMP_CampaignRun02 FROM
TTEMP_CampaignRun01 WHERE (((TTEMP_CampaignRun01.Town)= 'ALRESFORD'))
ORDER BY TTEMP_CampaignRun02.PK_Comp;

or

INSERT INTO TTEMP_CampaignRun02 SELECT TM_Customers.PK_Comp,
TM_Customers.FK_SchoolType, TM_Customers.TLK_Cat,
TM_Customers.Country, TM_Customers.Cnty, TM_Customers.Town,
TM_Customers.Pocd FROM TM_Customers WHERE (((TM_Customers.Town)=
'ALRESFORD')) ORDER BY TTEMP_CampaignRun02.PK_Comp;

Stapes

 

['69 Camaro]

Hi, Stapes.

I have tried that - it didn't work. This is my command: -

INSERT INTO TTEMP_CampaignRun02 SELECT TM_Customers.PK_Comp,
TM_Customers.FK_SchoolType, TM_Customers.TLK_Cat,
TM_Customers.Country, TM_Customers.Cnty, TM_Customers.Town,
TM_Customers.Pocd FROM TM_Customers WHERE (((TM_Customers.Town)=
'ALRESFORD')) ORDER BY TTEMP_CampaignRun02.PK_Comp;

TTEMP_CampaignRun02 already exists? With the primary key or unique index
set on which columns? And you're getting duplicate records in _those_
columns?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Stapes]

Hi, Stapes.


TTEMP_CampaignRun02 already exists? With the primary key or unique index
set on which columns? And you're getting duplicate records in _those_
columns?

HTH.
Gunny

Seehttp://www.QBuilt.comfor all your database needs.
Seehttp://www.Access.QBuilt.comfor Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.comhttp://www.Access.QBuilt.com/html/expert_contributors2.htmlfor contact
info.









- Show quoted text -

Hi
Yes, I set TTEMP_CampaignRun02.PK_Comp as indexed. After running the
code, that setting had gone.

 

['69 Camaro]

Hi, Stapes.

Yes, I set TTEMP_CampaignRun02.PK_Comp as indexed.

"As indexed" means what? That your primary key is set on
TTEMP_CampaignRun02.PK_Comp? Or is the primary key set on some other
column(s) in the TTEMP_CampaignRun02 table?

After running the
code, that setting had gone.

Ensure that you're running the append query, not the make-table query. The
append query is this one:

INSERT INTO TTEMP_CampaignRun02 SELECT TM_Customers.PK_Comp,
TM_Customers.FK_SchoolType, TM_Customers.TLK_Cat,
TM_Customers.Country, TM_Customers.Cnty, TM_Customers.Town,
TM_Customers.Pocd FROM TM_Customers WHERE (((TM_Customers.Town)=
'ALRESFORD')) ORDER BY TTEMP_CampaignRun02.PK_Comp;

And the ORDER By clause should name the table the data source is coming
_from_, not the destination table. Use TM_Customers.PK_Comp.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.