Append Query 2

[Tarnish]

Hi all,

If you are using an append query and the table you are getting the records from doesn't have a primary key and the table they are appending to has an autonumber primary key, how do you address the primary key in your sql statement?

Thanks, in advance,
T

 

[PHV]

You simply ignore it, ie you don't mention the AutoNumber field in the INTO list.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Tarnish]

Thanks PHV,

I guess my syntax is messed up somewhere else....

I was using a select statement to select columns from the old table and then rename them to the matching column in the new table using 'as' (at least for some of them).....like this:

INSERT INTO MailAppendTest
SELECT Subject as Description, Rec'd as Received, Dated,
To as Recipient, From as Sender, Action, Response
From Mail1;

Is that not the way it's done? Do I have to use all the columns and do they have to be in order? I've never used an append query so I'm just trying to emulate one that doesn't match what I'm doing very well....

Thanks again,
T

 

[lespaul]

If there aren't the exact number and types of columns in the two tables you have to specify the fields:

INSERT INTO MailAppendTest (Field1Name, Field2Name, Field3Name, etc.) SELECT Subject, Rec'd, Dated, To, From, Action, Response FROM Mail1

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Tarnish]

Thanks Leslie,

Even though I'm not quite there yet (choking on the 'Req'd' for some reason..), I think it's just a matter of time thanks to you and PHV.

T

 

[PHV]

You wanted probably something like this:

INSERT INTO MailAppendTest (Description, Received, Dated, Recipient, Sender, Action, Response)
SELECT Subject, [Rec'd], Dated, To, [From], Action, Response
FROM Mail1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Tarnish]

Thanks PHV!

It works! At some point, I'm going to have to figure out how to deal with a situation where the old app has a name (text) and I've used numbers (integer-representing names) in my new app, but I'm happy to have this level reached for now.

Thanks again both of you. I am grateful for the help.
T