If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
query missing some data
Hello,
I have two tables that share one identical column named "code" (which also has been given a relationship to one another). I did a unmatched query to see if all codes in table A were listed in table B. The query came back with zero unmatched items. I ran another query to join that goes: invoice date - table 1 sold to - table 1 code - table 2 size - table 2 quantity - table 1 Table 1 has 2086 records some records have the same code. Table 2 has 866 codes. When the query is run, only 936 records appear. I am not sure where I went wrong? Any suggestions? Thanks |
#2
|
|||
|
|||
Pay attention to the following :
I guess that Table1 contains the details for Table2 So you have 2 things to check: 1) do all records of table1 a record in table2 : the result should return no records and is the most important one SELECT Table1.* FROM Table1 LEFT JOIN Table2 ON Table1.Code=Table2.Code WHERE Table2.Code IS NULL 2) do all table2 records have details in table1 SELECT Table2.* FROM Table1 RIGHT JOIN Table2 ON Table1.Code=Table2.Code WHERE Table1.Code IS NULL By using these queries you can check the relations between the tables. - Raoul "Mel" wrote: Hello, I have two tables that share one identical column named "code" (which also has been given a relationship to one another). I did a unmatched query to see if all codes in table A were listed in table B. The query came back with zero unmatched items. I ran another query to join that goes: invoice date - table 1 sold to - table 1 code - table 2 size - table 2 quantity - table 1 Table 1 has 2086 records some records have the same code. Table 2 has 866 codes. When the query is run, only 936 records appear. I am not sure where I went wrong? Any suggestions? Thanks |
#3
|
|||
|
|||
Select a.*, b.* from Table A LEFT Join Table B on a.code = b.code
is the general syntax. Without knowing your exact table structures and the SQL, it's hard to give much more than that. -- Steve Clark, Access MVP FMS, Inc. www.fmsinc.com/consulting "Mel" wrote in message ... Hello, I have two tables that share one identical column named "code" (which also has been given a relationship to one another). I did a unmatched query to see if all codes in table A were listed in table B. The query came back with zero unmatched items. I ran another query to join that goes: invoice date - table 1 sold to - table 1 code - table 2 size - table 2 quantity - table 1 Table 1 has 2086 records some records have the same code. Table 2 has 866 codes. When the query is run, only 936 records appear. I am not sure where I went wrong? Any suggestions? Thanks |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Pulling data from 1 sheet to another | Dave1155 | Worksheet Functions | 1 | January 12th, 2005 05:55 PM |
Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |
Too Few Parameters error Mail Merge Access Parameter Query | Tony_VBACoder | Mailmerge | 3 | September 14th, 2004 12:15 PM |
Newbie? Do I use Report or Query | John Egan | New Users | 11 | June 28th, 2004 08:31 PM |