question

nicholasconger-5216 avatar image
0 Votes"
nicholasconger-5216 asked KenSheridan-7466 answered

Microsoft Access Query Records On One Table But Not Another

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.






office-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KenSheridan-7466 avatar image
0 Votes"
KenSheridan-7466 answered KenSheridan-7466 edited

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

nicholasconger-5216 avatar image
0 Votes"
nicholasconger-5216 answered

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 ?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thedbguy avatar image
0 Votes"
thedbguy answered

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...

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KenSheridan-7466 avatar image
0 Votes"
KenSheridan-7466 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.