Append data to an existing table 1

[manf01]

Hi All,

I need help concerning Append query.I will be thankful to you for your concentration.

I have a table created by different fields of 2 different tables. One of them is a linked table.
My problem is that I cannot get the new records in my new table. So I need to append
records except those available in the new table.

( i have tried it but i am getting all records everytime but i need to get only those records which are no currently present in my new table )


Thanks,

Mani

 

[Crowley16]

insert into...
WHERE someID not in (SELECT someID FROM tbl)

--------------------
Procrastinate Now!

 

[manf01]

Thanks a lot Crowley16,

Mani

 

[manf01]

This code is working perfect for a single field to be appended

INSERT INTO NewTable ( [Package Number] )
SELECT MainTable.[Package Number]
FROM MainTable
WHERE [Package Number] not in (Select [Package Number] From NewTable);


But i have tried the same code with many fields but its not giving me even a single field.
Anyone can help me ?


INSERT INTO NewTable ( [Package Number], Offer_Nr, Sender, Sending_Nr,Description )
SELECT MainTable.[Package Number], MainTable.Offer_Nr, MainTable.Sender, MainTable.Sending_Nr, MainTable.Description
FROM MainTable
WHERE [Package Number] not in (Select [Package Number] From NewTable);


Thanks,

 

[Crowley16]

specify all [package number] with it's table...

i.e. NewTable.[Package Number}

--------------------
Procrastinate Now!

 

[manf01]

Hi Crowley16,

thanks for your reply

INSERT INTO NewTable ( NewTable.[Package Number], NewTable.Offer_Nr, NewTable.Sender, NewTable.Sending_Nr,NewTable.Description )

I have tried it in this way but i am getting an error ( its not accepting table name with fields name )

Sorry for asking you again.

Can you give me any hint ?

Thanks

 

[Crowley16]

you don't need to put the NewTable in the NewTable(), just in the select and where parts...

I think your previous sql is getting confused about the [package number] in new table and in old table

--------------------
Procrastinate Now!

 

[manf01]

After using Table name in where and select parts
I got an error like

"You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field. (Error 3306)"

Sorry for asking you again and again.
How i can add here Exits ?

I will be very much thankful to you.

 

[Crowley16]

you shouldn't have to use Exsists, can you post the sql you have at the moment?

--------------------
Procrastinate Now!

 

[manf01]

Here is the sql,

INSERT INTO NewTable ( [Package Number], Offer_Nr, Sender, Sending_Nr,Description )
SELECT MainTable.[Package Number], MainTable.Offer_Nr, MainTable.Sender, MainTable.Sending_Nr, MainTable.Description
FROM MainTable

WHERE (NewTable.[Package Number], NewTable.Offer_Nr, NewTable.Sender, NewTable.Sending_Nr, NewTable.Description)
not in (Select NewTable.[Package Number], NewTable.Offer_Nr, NewTable.Sender,
NewTable.Sending_Nr, NewTable.Description
FROM [NewTable]);


Cheers,

 

[Crowley16]

you can't use NOT IN like that...

if you want to do that, then you need to split the individual fields up...

where (packageNum NOT in (select packageNum from tbl)) AND (offerNr NOT IN (select offerNr from tbl))...

frankly, you should have a primary key in this table, so that you just have to check that the primary key exsists or not exsists.

--------------------
Procrastinate Now!

 

[manf01]

Hi Crowley,

Sorry for late reply because I was not in the office.
I have tried it and its working. In my case I have added the desired fields in

( about the primary key it exists which is on Package Number )

INSERT INTO NewTable( fields name, ….,…..,……,)
SELECT fields name,……,……..,……….

And at the end I have used only Package Number field in where claus

WHERE ((NewTable.[Package Number]) NOT IN (Select [Package Number] From [NewTable])))

Thanks again,