Solved How to use "NOT EXISTS" in MS Access?

[mobarak ahmed]

Dears ,

i hope you are doing well

i use the form ' Salaries_Emp ' to record the attendance of employees to table ' Salaries_Emp '

i need when i chose the department from ' Combo_Dep '

show only Employee Name in ' Combo_Emp_Name ' that dose not recorded yet

any help will be appreciated

 

[ebs17]

I cannot open the Demo, my Access version is too old.
General:

SELECT
   A.*
FROM
   A
WHERE
   NOT EXISTS
      (
         SELECT
            NULL
         FROM
            B
         WHERE
            B.Key = A.Key
      )

Produces the same result as ...

SELECT
   A.*
FROM
   A
      LEFT JOIN B
      ON A.Key = B.Key
WHERE
   B.Key IS NULL

 

[Pat Hartman]

Using Access SQL, it is more efficient to use a left join. Jet/ACE do not optimize subqueries well so you should only use subqueries when there is no alternative. In this case, the solution is a simple left join with a where clause looking for Null in the right-side PK.

 

[ebs17]

What needs to be optimized about this simple subquery?

If only one key is used for linking, the query is just as fast as the alternative shown. With more than one key it becomes more problematic. In return, the query can be updated without restrictions, which is also sometimes desirable.

Regardless of a specific use: You should master both variants. In this sense I understood the question.

 

[mobarak ahmed]

I cannot open the Demo, my Access version is too old.
General:

SELECT
   A.*
FROM
   A
WHERE
   NOT EXISTS
      (
         SELECT
            NULL
         FROM
            B
         WHERE
            B.Key = A.Key
      )

Produces the same result as ...

SELECT
   A.*
FROM
   A
      LEFT JOIN B
      ON A.Key = B.Key
WHERE
   B.Key IS NULL

Sorry but I couldn’t get it i need example or with the attached file

 

[Pat Hartman]

What needs to be optimized about this simple subquery?

I'm pretty sure we had this conversation in a different thread so I don't want to go into it again.
There seems to be some website out there that recommends subqueries even when simple left joins will work.

@mobarak ahmed I don't understand what will be controlling the selection. the Saleries_Emp seems like a "master" table rather than a "transactions" table so there would not be a different source table that would control what records had already been added. If you were talking about adding timekeeping records for a period, then you would use a left join to the transaction table to find only employees whose time has not yet been recorded. But I don't understand the context of what you are showing.

 

[mobarak ahmed]

@arnelgp

 

[mike60smart]

Sorry but I couldn’t get it i need example or with the attached file

Why are you not using a Main Form based on EmpAdmin with a Subform based on Salaries_Emp ?

 

[isladogs]

In case it helps. have a look at my two part article on synchronising data.
The second part includes the use of NOT EXISTS with a subquery as one of several approaches.

Synchronise Data 2

This is the second part of an article exploring explores some of the methods that can be used to synchronise data with an external source. The advantages and disadvantages of each method are also discussed.

www.isladogs.co.uk

 

[arnelgp]

i noticed you don't have Payroll Period in your Salaries_emp table, so i added the field.
now, see your employee combobox if it will work for you.

 

[mobarak ahmed]

i noticed you don't have Payroll Period in your Salaries_emp table, so i added the field.
now, see your employee combobox if it will work for you.

awesome

 

[Gasman]

@arnelgp

So not prepared to do any work yourself, just keep asking someone for solutions?

 

[mobarak ahmed]

So not prepared to do any work yourself, just keep asking someone for solutions?

I’m not a professional access user i am an accountant trying to simplify my work
So i try to do my best and when i face hard problem ( for me ) i ask for help
Sorry if anyone fell annoyed by me

 

[Gasman]

Neither was I. I used it in my employment, pretty much as you are doing now.

I got more satisfaction by doing most of the work myself.
I asked here for help when I needed it, and I received it here and was grateful for that help.

However I also wanted to learn as I went. For one it is quicker than waiting for hopefully a solution, and two, again that satisfaction of working it out yourself.

 

[mobarak ahmed]

Neither was I. I used it in my employment, pretty much as you are doing now.

I got more satisfaction by doing most of the work myself.
I asked here for help when I needed it, and I received it here and was grateful for that help.

However I also wanted to learn as I went. For one it is quicker than waiting for hopefully a solution, and two, again that satisfaction of working it out yourself.

I have tried to solve it my self and searched a lot before asking for help here however it would be simple for someone else
I want to say I have tried the hard before the easy one