Microsoft Access Query Records On One Table But Not Another

Question

Hello,

I had a quick question about a Microsoft Access Query we're trying to write. Our DBAs all left, so we're trying to piecemeal something for a department.

We have 2 tables:

Table 1

• EID
• Email
• PW

Table 2

• EID
• Demographics Info

Our key is EID. We want to run a query to find the EID on Table 2 that isn't on Table 1.

On Query Design View, I'm including everything on Table 2, but adding the Email and PW from Table 1.

The query shows everything, but it doesn't include the info from Table 2 that doesn't have Email/PW from table 1.

The idea is to query out the info from Table 2 that do NOT have a Email/PW from Table 1. Get that info, add it to table 1 and rerun the query to fill every value on Table 2 with that Email/PW info.

If there a way to edit the query to ONLY show the info on Table 2 that doesn't have values from table 1? Trying all sorts of criteria and not coming up with anything.

Thanks.

Answer 1

SELECT EID
FROM [Table 2]
WHERE NOT EXISTS
(SELECT *
FROM [Table 1]
WHERE [Table 1].EID = [Table 2].EID);

Answer 2

That worked BEAUTIFUL.

one more question, is there a way to export all of table 2 and not just the EID from EID missing from Table 1 ?

Answer 3

Maybe you could try something like:

SELECT Table2.* FROM Table2
LEFT JOIN Table1
ON Table2.EID=Table1.EID
WHERE Table1.EID Is Null

Hope that helps...

Answer 4

You can return all columns by using the asterisk wildcard character in the SELECT clause in place of the column names, as in thedbguy's reply.