Query with Dlookup

[mari_hitz]

Hi everyone! My name is Marina, I am kindda newbie with access and I would like to know if it is possible the following: A have a database with two tables. I would like to create a query comparing the two tables trough a dlookup. Is this possible? My first table would be the one containing data that I would like to check if it appears in the table two.


I am attaching an example database and in the same I would like to have a query that compares me the two tables and bring me information for the person from table two that it is not included in table one. I do not know if I explained myself good. Hope you can help me

[hertfordkc]

A standard query should be able to handle your problem.

[mari_hitz]

Hi hertfordkc thanks for that! Like I have said I am pretty new at access, I know how to create a query, however, not how to get what I am trying to do. Could you please guide me so I could do it?

Thanks!

[rpeare]

With the example you gave use this if you are looking for only the clients that have a value in each table:

SELECT Table1.ID, Table1.Name, Table1.[Last Name], Table1.Office, Table1.Gender, Table2.[E-mail], Table2.Telephone
FROM Table1 INNER JOIN Table2 ON (Table1.[Last Name] = Table2.[Last Name]) AND (Table1.Name = Table2.Name);

If you want to see all the values in table1 and whether or not they have a value in table2 use this:

SELECT Table1.ID, Table1.Name, Table1.[Last Name], Table1.Office, Table1.Gender, Table2.[E-mail], Table2.Telephone
FROM Table1 LEFT JOIN Table2 ON (Table1.[Last Name] = Table2.[Last Name]) AND (Table1.Name = Table2.Name);

If you want to see all the values in table2 and whether or not they have a value in table 2 you'd use this:

SELECT Table1.ID, Table2.Name, Table2.[Last Name], Table1.Office, Table1.Gender, Table2.[E-mail], Table2.Telephone
FROM Table1 RIGHT JOIN Table2 ON (Table1.[Last Name] = Table2.[Last Name]) AND (Table1.Name = Table2.Name);

However I want to point out a couple of things before you get far in your design. Never used reserved words in your database object names (NAME is a reserved word). Also stay away from using spaces and special characters (#, $, %) other than the underscore (_) in your object names (field names, table names, query names, etc) they will only cause you grief as you go along.

Lastly, what you really should have in your table 2 is the ID field from Table1 instead of the first/last name combination. Performing queries on two tables where the data can change (let's say through marriage the surname changes you will no longer have a match)

[mari_hitz]

Thanks for that! Could you please let me know I put that into the query? In the criteria field?
I am sorry for my ignorance, like I have said I am new at this. Thanks!

[rpeare]

put what where?

each of the three thing I posted is a separate query, you just post the sql code into the SQL window of a query and away you go.