How to check if there already exists an id in a main table

[Nebil]

Hi,
I was wondering if there's a way to check an existing id in the main table when using temporary table,then use another id for inserting records.Since it may cause error for using the same id in the main table.
Is there a way to do that?

[Reverend Jim]

Try doing a SELECT COUNT(*) on the record with that ID. If you get a result of 0 then the record does not exist.

[Nebil]

BTW does this work,the question i asked when a temp table is being created?And also if it does i kind of wrote this sql,can you comment on that?

"SELECT * INTO altempTable FROM [allowance]" & _
"WHERE [allowance number] NOT IN (SELECT * FROM [allowance] WHERE [allowance number]" & _
"IS NOT NULL"

"SELECT * INTO altempTable FROM [allowance] WHERE [allowance number] IS NULL"

The second statement is which i'm using currently doesn't give me the result that i want.Please help me on this one.

[Reverend Jim]

I don't believe the first query will do anything. The syntax is

WHERE [SOMEVALUE] NOT IN (LIST OF VALUES)

however your syntax is

WHERE [SOMEVALUE] NOT IN (RECORDSET)

What exactly are you trying to do? The second query should work but because I don't know what you are trying to do I can't say whether it will do what you want.

[Nebil]

Here's the thing i have a primary key of type auto number in a main table.Before i add records to the main table i store them in a temp table.
Since the temp table is a copy of the main one and empty as well,new records that are stored in the temp table may hold a duplicate id if there exists a record in the main one.

And when you try to copy data to the main table it raises an exception for trying to insert a record with the same id.so first i want to check if there exists a record in the main table then if it exists,i will assign the next id to the temp table as it's created.Hence there's no problem with copying records from the temp table to the main one.

[Nebil]

I kind of came up with this statement,what it does is it copies the last record to the temp table just to assign the next id to the new record and finally discrads it when copying the new records to the main table.But it only works if there's a record in the main table,any modification is absolutely appreciated.

qry1 = "SELECT * INTO pntempTable FROM per_diem_accomodation WHERE [pda number]=" & _
                "(SELECT MAX ([pda number]) FROM per_diem_accomodation)"



qry2 = "INSERT INTO per_diem_accomodation SELECT * FROM pntempTable WHERE [pda number]" & _
                    "NOT IN (SELECT MIN ([pda number]) FROM pntempTable)"

[Reverend Jim]

If a field has been designated as AUTONUMBER then you cannot include it in the insert query. If you want to insert all records from the temp table into the permanent table you must specify all the fields except the AUTONUMBER field in the select statement. For example, if I a table

table_1
ID          int (PK AUTO NUMBER)
field1      varchar(50)
field2      varchar(50)

I can create a temp table by

SELECT * INTO temp FROM table_1

but to add those records back into table_1 I must do

INSERT INTO table_1 SELECT field1, field2 FROM temp

Because you said the temp table is a copy (in structure) of the permanent table then the ID field is also AUTONUMBER and wiill not allow you to insert a record which has the ID field.

[Nebil]

Thanks Jim,it worked.