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

 

[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

 

[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