Exclude fields after SELECT *

[Steve S]

I need to replicate a number of records from one table back into the same
table so the user is spared the task of re-entering redundant data. this
table has the the primary key as autoNumber. When I run the following I get
a 'Key violation' error. I assume this is because I am trying to copy the
primary key field. Is there a way to exclude selected fields in an INSERT
INTO-- Select Table.*

Currently the SQL has a reference to all (except primary key) 36 fields in
the table.

INSERT INTO [Fees]
SELECT Fees.*
FROM Fees
WHERE (((Fees.[Contest ID])=42));

Any help is appreciated

 

[smartin]

I need to replicate a number of records from one table back into the same
table so the user is spared the task of re-entering redundant data. this
table has the the primary key as autoNumber. When I run the following I get
a 'Key violation' error. I assume this is because I am trying to copy the
primary key field. Is there a way to exclude selected fields in an INSERT
INTO-- Select Table.*

Currently the SQL has a reference to all (except primary key) 36 fields in
the table.

INSERT INTO [Fees]
SELECT Fees.*
FROM Fees
WHERE (((Fees.[Contest ID])=42));

Any help is appreciated

Hi Steve,

I suspect you are right about the PK. You will have select all the
non-PK fields yourself. Fortunately, the query builder makes this easy.
Just point at the first field you want from the table, scroll down and
shift+click the last field you want, and drag into the results.

 

[Steve S]

thanks for the prompt response. Unfortunately that still produces a very
huge SQL string. what I am looking is something like:

INSERT INTO [Fees]
SELECT Fees.*, EXCEPT or EXCLUDE [Fee ID] <--Primary key
FROM Fees
WHERE (((Fees.[Contest ID])=42));
--
Steve S

I need to replicate a number of records from one table back into the same
table so the user is spared the task of re-entering redundant data. this
table has the the primary key as autoNumber. When I run the following I get
a 'Key violation' error. I assume this is because I am trying to copy the
primary key field. Is there a way to exclude selected fields in an INSERT
INTO-- Select Table.*

Currently the SQL has a reference to all (except primary key) 36 fields in
the table.

INSERT INTO [Fees]
SELECT Fees.*
FROM Fees
WHERE (((Fees.[Contest ID])=42));

Any help is appreciated

Hi Steve,

I suspect you are right about the PK. You will have select all the
non-PK fields yourself. Fortunately, the query builder makes this easy.
Just point at the first field you want from the table, scroll down and
shift+click the last field you want, and drag into the results.

 

[Rick Brandt]

I need to replicate a number of records from one table back into the
same table so the user is spared the task of re-entering redundant
data. this table has the the primary key as autoNumber. When I run
the following I get a 'Key violation' error. I assume this is
because I am trying to copy the primary key field. Is there a way to
exclude selected fields in an INSERT INTO-- Select Table.*

Currently the SQL has a reference to all (except primary key) 36
fields in the table.

INSERT INTO [Fees]
SELECT Fees.*
FROM Fees
WHERE (((Fees.[Contest ID])=42));

Any help is appreciated

Nope. You either get them all with * or you have to list them individually.

 

[smartin]

thanks for the prompt response. Unfortunately that still produces a very
huge SQL string.

Huge, schmuge (^: Just for interest, how many fields are you working with?

what I am looking is something like:

INSERT INTO [Fees]
SELECT Fees.*, EXCEPT or EXCLUDE [Fee ID] <--Primary key
FROM Fees
WHERE (((Fees.[Contest ID])=42));

AFAIK, SELECT COUNT(*) FROM ALLOWED_SQL_WORDS WHERE ALLOWED_SQL_WORD IN
('EXCEPT', 'EXCLUDE'); returns 0.

IOW, and with all ribbing aside, it can't be done your way.

Huge can be made less huge by aliasing the table(s) with something
short, like "A". Regardless, Access's rendering of your query will be
ugly, because that is its specialty (^:

 

[Lord Kelvan]

my query would by why are you duplicating data in the same table you
are supose to remove redundancy in a databse not add it but as rick
said you cannot use * for that you have to list each field seperatally

 

[John Spencer]

AND what you are looking for DOES NOT exist.

If you don't want every field in the table(s), you must specify the
fields you do want.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Steve S]

Thanks everybody for verifying what I already suspected. I often look at
code/routines and think "there has to be a simpler/better/more efficient way
to get the same results." this is always code written by others since my
code is always lean and mean and I never waste a CPU stroke(I wish)

In this case there isn't a better way so I will move on the next problem
(whatever that may be - probably find out Monday).

I will have to remember the keyword 'AFAIK'

Yes I agree that the word 'redundant' was a poor choice. Maybe 'similar'
would have been less disturbing to some.








--
Steve S

thanks for the prompt response. Unfortunately that still produces a very
huge SQL string.

Huge, schmuge (^: Just for interest, how many fields are you working with?

what I am looking is something like:

INSERT INTO [Fees]
SELECT Fees.*, EXCEPT or EXCLUDE [Fee ID] <--Primary key
FROM Fees
WHERE (((Fees.[Contest ID])=42));

AFAIK, SELECT COUNT(*) FROM ALLOWED_SQL_WORDS WHERE ALLOWED_SQL_WORD IN
('EXCEPT', 'EXCLUDE'); returns 0.

IOW, and with all ribbing aside, it can't be done your way.

Huge can be made less huge by aliasing the table(s) with something
short, like "A". Regardless, Access's rendering of your query will be
ugly, because that is its specialty (^: