Try doing a
SELECT COUNT(*) on the record with that ID. If you get a result of 0 then the record does not exist.
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.
Edited 2 Years Ago by Nebil
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.
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.
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)"
Edited 2 Years Ago by Nebil
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.
Edited 2 Years Ago by Reverend Jim
Thanks Jim,it worked.
Write a C program that should create a 10 element array of random integers (0 to 9). The program should total all of the numbers in the odd positions of the array and compare them with the total of the numbers in the even positions of the array and indicate ...
Hi. so this is actually a continuation from another question of mineHere but i was advised to start a new thread as the original question was already answered.
This is the result of previous question answered :
code for the listbox - datagridview interaction
At the top of the code ...
I have a 2d matrix with dimension (3, n) called A, I want to calculate the normalization and cross product of two arrays (b,z) (see the code please) for each column (for the first column, then the second one and so on).
the function that I created to find the ...