Multiple Find Unmatched

[dr4ke1990]

I am currently creating a database to calculate mileage from two fields (Departing_Post_Code & Arriving_Post_Code) which I have referenced in a second table (same names + mileage between postcodes). What I want to do is create a query to show the journeys that are not in the referenced table (PostcodeLookup). I know you can pull off the entries from ONE column that isn't in another table, but how do I do this for two? So, for example, in the Journeys table below; the query would display ID 6 (Postcode5 - Postcode1) because both of those entries are not in PostCodeLookup.

ID	Departing_Post_Code	Arriving_Post_Code	Start_Time	End_Time
4	Postcode1	Postcode2	03/11/2013 11:00:00	03/11/2013 11:30:00
5	Postcode2	Postcode3	03/11/2013 09:41:00	03/11/2013 10:03:00
6	Postcode5	Postcode1

ID	Departing_Post_Code	Arriving_Post_Code	Miles
1	Postcode1	Postcode2	21
2	Postcode2	Postcode3	23.2

[lfpm062010]

I don't know why you need the second table to keep track of the miles.

I think this query should work (TABLE1 is the first table and TABLE2 is the second table).

SELECT TABLE1.Departing_Post_Code, TABLE1.Arriving_Post_Code, TABLE2.Miles
FROM TABLE1 LEFT JOIN TABLE2 ON (TABLE1.Arriving_Post_Code = TABLE2.Arriving_Post_Code) AND (TABLE1.Departing_Post_Code = TABLE2.Departing_Post_Code)
WHERE (((TABLE2.Departing_Post_Code) Is Null) AND ((TABLE2.Arriving_Post_Code) Is Null));

Another way is to create query with those post code concatenated then do the join of the two queries on the newly created post code.

Hope this will work for you.

[NTC]

put 2 join lines in your query between the two tables (Departing to Departing, Arriving to Arriving)

then in the criteria put Is Null for these two fields of table 2 (per your post)

your query result then will be just those records of table 1 for which there is no match of both fields in table 2.

[dr4ke1990]

I've tried the concatenation technique, as this is what will server my purpose, but for some reason it isn't working. I've used the 'unmatched' query wizard and I'm trying to pull out the information where the contatenate value in JourneyConcatenate isn't in PostCodeConcatenate but it doesn't work? I've tested this out by putting two completely random postcodes in and concatenating them but it doesn't show in this query....? That doesn't make sense!

 SELECT JourneyConcatenate.Concatenation, JourneyConcatenate.Departing_Post_Code, JourneyConcatenate.Arriving_Post_Code, JourneyConcatenate.Start_Time, JourneyConcatenate.End_Time, JourneyConcatenate.Timespent, JourneyConcatenate.DateOfVisit
FROM JourneyConcatenate LEFT JOIN PostCodeConcatenate ON JourneyConcatenate.[Concatenation] = PostCodeConcatenate.[Concatenation]
WHERE (((PostCodeConcatenate.Concatenation) Is Null));

[dr4ke1990]

Ok so this code worked

 SELECT TABLE1.Departing_Post_Code, TABLE1.Arriving_Post_Code, TABLE2.Miles
FROM TABLE1 LEFT JOIN TABLE2 ON (TABLE1.Arriving_Post_Code = TABLE2.Arriving_Post_Code) AND (TABLE1.Departing_Post_Code = TABLE2.Departing_Post_Code)
WHERE (((TABLE2.Departing_Post_Code) Is Null) AND ((TABLE2.Arriving_Post_Code) Is Null));

What does this bit mean?

 WHERE (((TABLE2.Departing_Post_Code) Is Null) AND ((TABLE2.Arriving_Post_Code) Is Null));

[lfpm062010]

WHERE (((TABLE2.Departing_Post_Code) Is Null) AND ((TABLE2.Arriving_Post_Code) Is Null));

This is "LEFT JOIN". It join "TABLE1" and "TABLE2" using the "Departing_Post_Code" and "Arriving_Post_Code". The "TABLE1" is the main table "LEFT JOIN" to "TABLE2". It will bring back all the "TABLE1" records and fill in "TABLE2" with "NULL" value where "TABLE2" does not have the match two fields "Departing_Post_Code" and "Arriving_Post_Code".

Hope this answer the question.

I will look at the Concatenation and try to give you an answer.

[lfpm062010]

"Journey" is the first table and "PostCode" is your second table.
JourneyConcat Query:
SELECT Journey.ID, [Journey].[Departing_Post_Code] & [Journey].[Arriving_Post_Code] AS NewPostCode, Journey.Departing_Post_Code, Journey.Arriving_Post_Code, Journey.Start_Time, Journey.End_Time
FROM Journey;

PostCodeConcat Query:
SELECT PostCode.ID, [PostCode].[Departing_Post_Code] & [PostCode].[Arriving_Post_Code] AS NewPostCode, PostCode.Departing_Post_Code, PostCode.Arriving_Post_Code, PostCode.Miles
FROM PostCode;

Find Unmatched Query:
SELECT JourneyConcat.NewPostCode, JourneyConcat.Departing_Post_Code, JourneyConcat.Arriving_Post_Code, PostCodeConcat.Miles, PostCodeConcat.ID, PostCodeConcat.NewPostCode, PostCodeConcat.Departing_Post_Code, PostCodeConcat.Arriving_Post_Code, PostCodeConcat.Miles
FROM JourneyConcat LEFT JOIN PostCodeConcat ON JourneyConcat.NewPostCode = PostCodeConcat.NewPostCode
WHERE (((PostCodeConcat.NewPostCode) Is Null));

[dr4ke1990]

I can't see the difference between your concatenate queries to mine? Other than I called my concatenated field something different...

[lfpm062010]

Can you show me the query "JourneyConcatenate" and "PostCodeConcatenate"?

[dr4ke1990]

I've just realised what is different, I was using the below code. Could the TRIM have made the issue?

 =Trim([Departing_Post_Code] & "-" & [Arriving_Post_Code]) As Concatenate 

[lfpm062010]

If you have blank spaces before the "Arriving_Post_Code", then it will make a difference. The easy test is to show everything before put the "IS NULL" criteria. Access normally remove the trailing spaces after data entry.

A word of caution when using character to sperate the "Departing_Post_Code" and "Arriving_Post_Code". If one of the column is "NULL", then that character will still be there when you concatenate.

For example:
If Departing_Post_Code = "Postcode1" and Arriving_Post_Code = "Postcode2", then you should get "Postcode1-Postcode2".

If Departing_Post_Code = "Postcode1" and Arriving_Post_Code = "", then you should get "Postcode1-".

If Departing_Post_Code = "" and Arriving_Post_Code = "Postcode2", then you should get "-Postcode2".

If Departing_Post_Code = "" and Arriving_Post_Code = "", then you should get "-".

Hope this clear things up for you.

[dr4ke1990]

There shouldn't be any spaces because I used the TRIM function? I still don't understand why the find unmatched query wouldn't work on that concatenate query..

[lfpm062010]

Since you did not posted your query "JourneyConcatenate" and "PostCodeConcatenate". You must have your reason to only use Trim after Concatenate the post codes. Personally, I would trim both codes (like, Trim([Journey].[Departing_Post_Code]) & "-" & Trim([Journey].[Arriving_Post_Code]) AS NewPostCode).

This is what I think the query looks like.

JourneyConcatenate:
SELECT Journey.ID, Trim([Journey].[Departing_Post_Code] & "-" & [Journey].[Arriving_Post_Code]) AS NewPostCode, Journey.Departing_Post_Code, Journey.Arriving_Post_Code, Journey.Start_Time, Journey.End_Time
FROM Journey;

PostCodeConcatenate:
SELECT PostCode.ID, Trim([PostCode].[Departing_Post_Code] & "-" & [PostCode].[Arriving_Post_Code]) AS NewPostCode, PostCode.Departing_Post_Code, PostCode.Arriving_Post_Code, PostCode.Miles
FROM PostCode;

"Trim" is not the cause as long as there is no leading and trailing spaces. The "-" will cause an issue as I have mentioned above. If I use the "LEFT JOIN" and remove the "Is Null" and show everything. The "-" will show in the concatenate column.

For example:
SELECT JourneyConcat.ID, JourneyConcat.NewPostCode, JourneyConcat.Departing_Post_Code, JourneyConcat.Arriving_Post_Code, JourneyConcat.Start_Time, JourneyConcat.End_Time, PostCodeConcat.ID, PostCodeConcat.NewPostCode, PostCodeConcat.Departing_Post_Code, PostCodeConcat.Arriving_Post_Code, PostCodeConcat.Miles
FROM JourneyConcat LEFT JOIN PostCodeConcat ON JourneyConcat.NewPostCode = PostCodeConcat.NewPostCode;
'WHERE (((PostCodeConcat.NewPostCode) Is Null));

The result will be:

JourneyConcat.ID	JourneyConcat.NewPostCode	JourneyConcat.Departing_Post_Code	JourneyConcat.Arriving_Post_Code	Start_Time	End_Time	PostCodeConcat.ID	PostCodeConcat.NewPostCode	PostCodeConcat.Departing_Post_Code	PostCodeConcat.Arriving_Post_Code	Miles
4	Postcode1-Postcode2	Postcode1	Postcode2	3/11/2013 11:00:00 AM	3/11/2013 11:30:00 AM	1	Postcode1-Postcode2	Postcode1	Postcode2	21
5	Postcode2-Postcode3	Postcode2	Postcode3	3/12/2013 9:41:00 AM	3/12/2013 10:03:00 AM	2	Postcode2-Postcode3	Postcode2	Postcode3	23
6	Postcode5-Postcode1	Postcode5	Postcode1				-

Notice the last row (ID = 6). The "-" show where the "PostCodeConcat".NewPostCode column.

[dr4ke1990]

Thank you - I think I understand now :-)

Appreciate your explanations!