unmatched query wizard

[Jen0dorf]

Hi

I need to compare two tables - tbldeceased and Graves_2015 - and list the records not in tbldeceased but are in graves_2015.

Thought I'd use the unmatched query wizard but I need to use two or more fields as criteria ie Surname and forenames.

I seemed unable to do this in query wizard so I created the query using the criteria surname=surname and then went into sql mode and edited the query however this is not work


Is there a way to compare tables using multiple field criteria

thanks



Ian

[ssanfu]

It would be helpful if you posted the SQL of your query.

[Jen0dorf]

Hi

senior moment

thought I had

[CODE

SELECT Graves_2015.Plot, Graves_2015.GraveNo, Graves_2015.Forenames, tblDeceased.Surname
FROM Graves_2015 LEFT JOIN tblDeceased ON Graves_2015.[Forenames] = tblDeceased.[Forenames]
WHERE (((tblDeceased.Surname)="IsNull") AND ((tblDeceased.Forenames) Is Null));


[/CODE]

When I run the query I get no results. Which I assumed meant that every record that had a forename and Surname had a matching record in both tables.

However being a doubting Thomas I doubt my code

thanks

Ian

[CJ_London]

hmm, your coding is a bit wrong. not sure where you are going with this

WHERE (((tblDeceased.Surname)="IsNull") AND ((tblDeceased.Forenames) Is Null))

and little point in returning tblDeceased.Surname if you are looking for cases where it doesn't exist (so nothing to return) and in your criteria above you want it to ="isnull"

me suspects you are thrashing around a bit trying to find something that works. Slow down and do it in small steps and understand what each step is doing before moving to the next step

try - this

SELECT Graves_2015.Plot, Graves_2015.GraveNo, Graves_2015.Forenames
 FROM Graves_2015 LEFT JOIN tblDeceased ON Graves_2015.[Forenames] = tblDeceased.[Forenames] and Graves_2015.[Surname] = tblDeceased.[Surname]
 WHERE tblDeceased.Surname is null

[ssanfu]

WHERE (((tblDeceased.Surname)="IsNull") AND ((tblDeceased.Forenames) Is Null));

Just an FYI - once you enclose something in quotes, it becomes a string.
So the WHERE clause is asking "Is there a Surname in table tblDeceased that is equal to "IsNull" AND a Forenames in table tblDeceased that Is Null ".

Turns out, you were right to question the query results.

[Jen0dorf]

Hi

thanks one and all. Thought the query wizard was the answer perhaps not. Possible I needto go in a different direction and use an append query?

My problem/task is that for weeks/months I have been working on the database for my family history society to list the deceased in a cemetary.

The table given to me was one listing graves entries- 39600 in all

With much help and assistance I got it all working perfectly as they wanted.

BUT having given them a final copy to look at I was presented with another table which a volunteer had been "adding to" - appears the original database was not locked down.

this new table only contains 38764 records but is supposed to be newer.

My aim is to make sure that all the records in the second table are in the first.

MY thought was to use the query wizard to list the non matching records in the second table and then append them to the first table

From your kind comments it seems I may be on the wrong track so is an append operation a better idea?

thanks

Ian

[ItsMe]

It sounds like you need an unmatched query to locate records that do not match. This result can then be referenced to update and or append. As mentioned in post #4, you need to do is start over, maybe using the wizard, creating an unmatched result for a single column. Then add a second join.

I worked through this exercise myself and came up with a similar result as Ajax did. However, I think you will still need the second field, the one the second join is using, in the WHERE criteria. So I came up with this. Maybe take a look at this SQL in Design View and in Data Sheet view.

SELECT Graves_2015.Plot, Graves_2015.GraveNo
FROM Graves_2015 
LEFT JOIN tblDeceased ON (Graves_2015.Surname = tblDeceased.Surname) AND (Graves_2015.Forenames = tblDeceased.Forenames)
WHERE (((tblDeceased.Surname) Is Null) AND ((tblDeceased.Forenames) Is Null));

[CJ_London]

WHERE (((tblDeceased.Surname) Is Null) AND ((tblDeceased.Forenames) Is Null));

I debated with myself this point - if there is no record, all fields in tbledeceased will be null so my view was you only need one in the criteria.