Delete query

[Lamb2087]

Can some one tell me what is wrong with this query? Acts like it will not run.

DELETE *
FROM dbo_idfy
WHERE PEOPLE_CODE_ID NOT IN (SELECT
dbo_ACADEMIC.PEOPLE_CODE_ID
FROM dbo_ACADEMIC INNER JOIN dbo_idfy ON dbo_ACADEMIC.PEOPLE_CODE_ID =
dbo_idfy.people_code_id
WHERE (((dbo_ACADEMIC.ACADEMIC_YEAR)=[Enter Academic Year]) AND
((dbo_ACADEMIC.ACADEMIC_TERM)=[Enter Academic Term]) AND
((dbo_ACADEMIC.ACADEMIC_SESSION)="01") AND ((dbo_ACADEMIC.ACADEMIC_RATING)
="Here")));

 

[pdx_man]

Without going into it too far ... try using single quotes instead of double.

 

[Lamb2087]

Yes the select subquery runs and works just fine.

 

[Lamb2087]

Yes there are primary keys in the table and I have permission to the idfy table. I have admin rights to all the tables in this db and other db's

 

[Mile-O]

)=""" & [Enter Academic Term] & """)

maybe...


Is the Academic Term numeric or text?

 

[Lamb2087]

academic term is text

 

[Mile-O]

Your query thinks Academic Term as numeric.

 

[Mile-O]

May just be:

)="" & [Enter Academic Term] & "")

 

[Lamb2087]

Here is what I changed it to and it still does not return anything , just sits there.

DELETE *
FROM dbo_idfy
WHERE PEOPLE_CODE_ID NOT IN(
SELECT
dbo_ACADEMIC.PEOPLE_CODE_ID
FROM dbo_ACADEMIC INNER JOIN dbo_idfy ON dbo_ACADEMIC.PEOPLE_CODE_ID =
dbo_idfy.people_code_id
WHERE dbo_ACADEMIC.ACADEMIC_YEAR=[Enter Academic Year]
AND dbo_ACADEMIC.ACADEMIC_TERM=([Enter Academic Term] & "")
AND dbo_ACADEMIC.ACADEMIC_SESSION="01"
AND dbo_ACADEMIC.ACADEMIC_RATING="Here");

 

[Mile-O]

DELETE *
FROM dbo_idfy
WHERE PEOPLE_CODE_ID NOT IN(
SELECT
dbo_ACADEMIC.PEOPLE_CODE_ID
FROM dbo_ACADEMIC INNER JOIN dbo_idfy ON dbo_ACADEMIC.PEOPLE_CODE_ID =
dbo_idfy.people_code_id
WHERE dbo_ACADEMIC.ACADEMIC_YEAR = [Enter Academic Year]
AND dbo_ACADEMIC.ACADEMIC_TERM = "" & [Enter Academic Term] & ""
AND dbo_ACADEMIC.ACADEMIC_SESSION = "01"
AND dbo_ACADEMIC.ACADEMIC_RATING = "Here");

 

[Lamb2087]

Some thing is making the sql just sit and not run. I used the code you sent and it is doing the same thing it did before I made a change.

 

[Mile-O]

I take it you have just written this code as Access would surround all field and table names with square brackets (especially when you have field names with special characters).

DELETE *
FROM [dbo_idfy]
WHERE [PEOPLE_CODE_ID] NOT IN (
SELECT
[dbo_ACADEMIC].[PEOPLE_CODE_ID]
FROM [dbo_ACADEMIC] INNER JOIN [dbo_idfy] ON [dbo_ACADEMIC].[PEOPLE_CODE_ID] =
[dbo_idfy].[people_code_id]
WHERE ((([dbo_ACADEMIC].[ACADEMIC_YEAR]) = [Enter Academic Year])
AND (([dbo_ACADEMIC].[ACADEMIC_TERM]) = "" & [Enter Academic Term]) & ""
AND (([dbo_ACADEMIC].[ACADEMIC_SESSION]) = "01")
AND (([dbo_ACADEMIC].[ACADEMIC_RATING]) = "Here")));

Still, got no idea though...

 

[Lamb2087]

I ran the query as you have written it below and still not working.

 

[Mile-O]

When you run it do you not even get it saying:

You are about to delete ...n record(s). ?

If not:

Are you using the ! on the query design grid to run it?
Are you running it by double clicking it from the database window?
Are you calling it from code?
Are you building it in code?

 

[Lamb2087]

When you run it do you not even get it saying:
I get this when I double click on the query where it shows all the queries in the db
You are about to delete ...n record(s). ?
then it prompts me for year and session

If not:

Are you using the ! on the query design grid to run it?
When I clcik on ! it goes right to prompting me to enter a year

Are you running it by double clicking it from the database window?
You are about to delete ...n record(s). ?
then it prompts me for year and session

 

[Mile-O]

What about Academic Year - is that text or numeric?

 

[Lamb2087]

Academic Year
Academic Term
Academic Session

They are all text datatypes

 

[Mile-O]

You NEED to enclose text in inverted commas:

DELETE *
FROM [dbo_idfy]
WHERE [PEOPLE_CODE_ID] NOT IN (
SELECT
[dbo_ACADEMIC].[PEOPLE_CODE_ID]
FROM [dbo_ACADEMIC] INNER JOIN [dbo_idfy] ON [dbo_ACADEMIC].[PEOPLE_CODE_ID] =
[dbo_idfy].[people_code_id]
WHERE ((([dbo_ACADEMIC].[ACADEMIC_YEAR]) = "" & [Enter Academic Year] & "")
AND (([dbo_ACADEMIC].[ACADEMIC_TERM]) = "" & [Enter Academic Term] & "")
AND (([dbo_ACADEMIC].[ACADEMIC_SESSION]) = "01")
AND (([dbo_ACADEMIC].[ACADEMIC_RATING]) = "Here")));

 

[Lamb2087]

I think the term NOT IN is causing the problem
I do not see why it will not work. When I run the subquery it works just fine.