Showing un-matched fields in a basic query
in [Getting Started]
|
From: Adrian on 9 Jul 2008 06:26 Hi there, I have 2 sets of data created into 2 seperated tables. They are linked through a single PRIMARY KEY. Access's query is able to link and display fields from these 2 tables if the PRIMARY KEY is correct. BUT what about displaying all the fields where the PRIMARY FIELDS do not match? example: - Table 1 container number (primary key) destination port Table 2 container number (primary key) destination port I would like to show the the container numbers and destination ports from both table 1 & 2 which DO NOT MATCH. PLEASE HELP!! Thank you! Regards, Adrian (a in-frequent access user)
From: Dennis on 9 Jul 2008 06:44 Queries - New - Find Unmatched Query Wizard "Adrian" wrote: > Hi there, > > I have 2 sets of data created into 2 seperated tables. They are linked > through a single PRIMARY KEY. Access's query is able to link and display > fields from these 2 tables if the PRIMARY KEY is correct. BUT what about > displaying all the fields where the PRIMARY FIELDS do not match? > > example: - > Table 1 > container number (primary key) > destination port > > Table 2 > container number (primary key) > destination port > > I would like to show the the container numbers and destination ports from > both table 1 & 2 which DO NOT MATCH. > > PLEASE HELP!! Thank you! > > Regards, > Adrian > (a in-frequent access user) >
From: John Spencer on 9 Jul 2008 10:00 A query like this one will show all the containers Table 1 that are not in table 2 SELECT "Not In B" as Missing , A.[Container Number] , A.[Destination Port] FROM Table1 as A LEFT JOIN Table2 as B ON A.[Container Number] = B.[Container Number] WHERE B.[Container Number] is Null For the other way round: SELECT "Not In A" as Missing , B.[Container Number] , B.[Destination Port] FROM Table1 as A RIGHT JOIN Table2 as B ON A.[Container Number] = B.[Container Number] WHERE A.[Container Number] is Null If you want those where the Destination does not match although the Container numbers do match. SELECT "Different Port" as Missing , A.[Container Number] , A.[Destination Port] , B.[Destination Port] FROM Table1 as A INNER JOIN Table2 as B ON A.[Container Number] = B.[Container Number] WHERE A.[Destination Port] <> B.[Destination Port] '==================================================== John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County '==================================================== Dennis wrote: > Queries - New - Find Unmatched Query Wizard > > "Adrian" wrote: > >> Hi there, >> >> I have 2 sets of data created into 2 seperated tables. They are linked >> through a single PRIMARY KEY. Access's query is able to link and display >> fields from these 2 tables if the PRIMARY KEY is correct. BUT what about >> displaying all the fields where the PRIMARY FIELDS do not match? >> >> example: - >> Table 1 >> container number (primary key) >> destination port >> >> Table 2 >> container number (primary key) >> destination port >> >> I would like to show the the container numbers and destination ports from >> both table 1 & 2 which DO NOT MATCH. >> >> PLEASE HELP!! Thank you! >> >> Regards, >> Adrian >> (a in-frequent access user) >>
From: Adrian on 9 Jul 2008 11:27 Hi Dennis, Thank you I will try it out and revert. But I have a "hunch" it'll work. Cheers, Adrian "Dennis" wrote: > Queries - New - Find Unmatched Query Wizard > > "Adrian" wrote: > > > Hi there, > > > > I have 2 sets of data created into 2 seperated tables. They are linked > > through a single PRIMARY KEY. Access's query is able to link and display > > fields from these 2 tables if the PRIMARY KEY is correct. BUT what about > > displaying all the fields where the PRIMARY FIELDS do not match? > > > > example: - > > Table 1 > > container number (primary key) > > destination port > > > > Table 2 > > container number (primary key) > > destination port > > > > I would like to show the the container numbers and destination ports from > > both table 1 & 2 which DO NOT MATCH. > > > > PLEASE HELP!! Thank you! > > > > Regards, > > Adrian > > (a in-frequent access user) > >
From: Adrian on 9 Jul 2008 11:27 Dear John, thank you for the detailed answer. I will experiment and revert. Best Regards, Adrian Tan "John Spencer" wrote: > A query like this one will show all the containers Table 1 that are not > in table 2 > > SELECT "Not In B" as Missing > , A.[Container Number] > , A.[Destination Port] > FROM Table1 as A LEFT JOIN Table2 as B > ON A.[Container Number] = B.[Container Number] > WHERE B.[Container Number] is Null > > For the other way round: > SELECT "Not In A" as Missing > , B.[Container Number] > , B.[Destination Port] > FROM Table1 as A RIGHT JOIN Table2 as B > ON A.[Container Number] = B.[Container Number] > WHERE A.[Container Number] is Null > > If you want those where the Destination does not match although the > Container numbers do match. > > SELECT "Different Port" as Missing > , A.[Container Number] > , A.[Destination Port] > , B.[Destination Port] > FROM Table1 as A INNER JOIN Table2 as B > ON A.[Container Number] = B.[Container Number] > WHERE A.[Destination Port] <> B.[Destination Port] > > '==================================================== > John Spencer > Access MVP 2002-2005, 2007-2008 > The Hilltop Institute > University of Maryland Baltimore County > '==================================================== > > > Dennis wrote: > > Queries - New - Find Unmatched Query Wizard > > > > "Adrian" wrote: > > > >> Hi there, > >> > >> I have 2 sets of data created into 2 seperated tables. They are linked > >> through a single PRIMARY KEY. Access's query is able to link and display > >> fields from these 2 tables if the PRIMARY KEY is correct. BUT what about > >> displaying all the fields where the PRIMARY FIELDS do not match? > >> > >> example: - > >> Table 1 > >> container number (primary key) > >> destination port > >> > >> Table 2 > >> container number (primary key) > >> destination port > >> > >> I would like to show the the container numbers and destination ports from > >> both table 1 & 2 which DO NOT MATCH. > >> > >> PLEASE HELP!! Thank you! > >> > >> Regards, > >> Adrian > >> (a in-frequent access user) > >> >
|
Pages: 1 Prev: Sort Order For all objects Next: How to use ID to fill in other cells in a form |