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
|
|||
|
|||
Joined table: Returns duplicated record
I have 2 tables (hard to list down all attributes , so i give an example)
Table 1: Name Nationality Birthday nickname school allen Japan 01/Jan/2002 al ABC SCHOOL sarah USA 02/Feb/2003 sal BBB SCHOOL Nicole UK 03/Mar/2004 Nic CCC SCHOOL ---------------------------------------------------------------------------- Table 2: Code Nationality Birthday Remark 01 Japan 01/Jan/2002 good boy 02 USA 02/Feb/2003 Nice girl 03 UK 02/Mar/2004 ok person! ---------------------------------------------------------------------------- Problem: I tried to join them to produce a new table, but i get double result for each entry. ---------------------------------------------------------------------------- Question: Can i use "DISTINCT" OR "GROUP BY" Function ? If so, please show me the SQL . Thanks you ! -- Allen Phailat Wongakanit |
#2
|
|||
|
|||
Joined table: Returns duplicated record
How did you join the tables? Could you share your SQL view of your query?
What are the primary and foreign keys in the tables? -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Default.htm "ali" wrote: I have 2 tables (hard to list down all attributes , so i give an example) Table 1: Name Nationality Birthday nickname school allen Japan 01/Jan/2002 al ABC SCHOOL sarah USA 02/Feb/2003 sal BBB SCHOOL Nicole UK 03/Mar/2004 Nic CCC SCHOOL ---------------------------------------------------------------------------- Table 2: Code Nationality Birthday Remark 01 Japan 01/Jan/2002 good boy 02 USA 02/Feb/2003 Nice girl 03 UK 02/Mar/2004 ok person! ---------------------------------------------------------------------------- Problem: I tried to join them to produce a new table, but i get double result for each entry. ---------------------------------------------------------------------------- Question: Can i use "DISTINCT" OR "GROUP BY" Function ? If so, please show me the SQL . Thanks you ! -- Allen Phailat Wongakanit |
#3
|
|||
|
|||
Joined table: Returns duplicated record
Alright Hookom!, since this is a sample database, i'll try my best to portrait my real situation. (cuz my real table contains too many attributes !) I joined INNER JOIN (table1.Nationality = table2.nationality) and (table1.birthday = table2.birthday) I also want to display all attributes from both tables. Thanks a lot again, Hookom ! -- Allen Phailat Wongakanit "Duane Hookom" wrote: How did you join the tables? Could you share your SQL view of your query? What are the primary and foreign keys in the tables? -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Default.htm "ali" wrote: I have 2 tables (hard to list down all attributes , so i give an example) Table 1: Name Nationality Birthday nickname school allen Japan 01/Jan/2002 al ABC SCHOOL sarah USA 02/Feb/2003 sal BBB SCHOOL Nicole UK 03/Mar/2004 Nic CCC SCHOOL ---------------------------------------------------------------------------- Table 2: Code Nationality Birthday Remark 01 Japan 01/Jan/2002 good boy 02 USA 02/Feb/2003 Nice girl 03 UK 02/Mar/2004 ok person! ---------------------------------------------------------------------------- Problem: I tried to join them to produce a new table, but i get double result for each entry. ---------------------------------------------------------------------------- Question: Can i use "DISTINCT" OR "GROUP BY" Function ? If so, please show me the SQL . Thanks you ! -- Allen Phailat Wongakanit |
#4
|
|||
|
|||
Joined table: Returns duplicated record
If you have two people with the same nationality and birthday, you will have
duplicated records. -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Default.htm "ali" wrote: Alright Hookom!, since this is a sample database, i'll try my best to portrait my real situation. (cuz my real table contains too many attributes !) I joined INNER JOIN (table1.Nationality = table2.nationality) and (table1.birthday = table2.birthday) I also want to display all attributes from both tables. Thanks a lot again, Hookom ! -- Allen Phailat Wongakanit "Duane Hookom" wrote: How did you join the tables? Could you share your SQL view of your query? What are the primary and foreign keys in the tables? -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Default.htm "ali" wrote: I have 2 tables (hard to list down all attributes , so i give an example) Table 1: Name Nationality Birthday nickname school allen Japan 01/Jan/2002 al ABC SCHOOL sarah USA 02/Feb/2003 sal BBB SCHOOL Nicole UK 03/Mar/2004 Nic CCC SCHOOL ---------------------------------------------------------------------------- Table 2: Code Nationality Birthday Remark 01 Japan 01/Jan/2002 good boy 02 USA 02/Feb/2003 Nice girl 03 UK 02/Mar/2004 ok person! ---------------------------------------------------------------------------- Problem: I tried to join them to produce a new table, but i get double result for each entry. ---------------------------------------------------------------------------- Question: Can i use "DISTINCT" OR "GROUP BY" Function ? If so, please show me the SQL . Thanks you ! -- Allen Phailat Wongakanit |
#5
|
|||
|
|||
Joined table: Returns duplicated record
-- Allen Phailat Wongakanit "Duane Hookom" wrote: If you have two people with the same nationality and birthday, you will have duplicated records. -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Default.htm "ali" wrote: Alright Hookom!, since this is a sample database, i'll try my best to portrait my real situation. (cuz my real table contains too many attributes !) I joined INNER JOIN (table1.Nationality = table2.nationality) and (table1.birthday = table2.birthday) I also want to display all attributes from both tables. Thanks a lot again, Hookom ! -- Allen Phailat Wongakanit "Duane Hookom" wrote: How did you join the tables? Could you share your SQL view of your query? What are the primary and foreign keys in the tables? -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Default.htm "ali" wrote: I have 2 tables (hard to list down all attributes , so i give an example) Table 1: Name Nationality Birthday nickname school allen Japan 01/Jan/2002 al ABC SCHOOL sarah USA 02/Feb/2003 sal BBB SCHOOL Nicole UK 03/Mar/2004 Nic CCC SCHOOL ---------------------------------------------------------------------------- Table 2: Code Nationality Birthday Remark 01 Japan 01/Jan/2002 good boy 02 USA 02/Feb/2003 Nice girl 03 UK 02/Mar/2004 ok person! ---------------------------------------------------------------------------- Problem: I tried to join them to produce a new table, but i get double result for each entry. ---------------------------------------------------------------------------- Question: Can i use "DISTINCT" OR "GROUP BY" Function ? If so, please show me the SQL . Thanks you ! -- Allen Phailat Wongakanit |
#6
|
|||
|
|||
Joined table: Returns duplicated record
So, how does he write it so the records do not duplicate? I have to do the
same thing and am currently writing seperate queries for each comparison I am doing (answer to a similar question I asked in this community earlier), however when I put these queries together with a union, they duplicate. "Duane Hookom" wrote: If you have two people with the same nationality and birthday, you will have duplicated records. -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Default.htm "ali" wrote: Alright Hookom!, since this is a sample database, i'll try my best to portrait my real situation. (cuz my real table contains too many attributes !) I joined INNER JOIN (table1.Nationality = table2.nationality) and (table1.birthday = table2.birthday) I also want to display all attributes from both tables. Thanks a lot again, Hookom ! -- Allen Phailat Wongakanit "Duane Hookom" wrote: How did you join the tables? Could you share your SQL view of your query? What are the primary and foreign keys in the tables? -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Default.htm "ali" wrote: I have 2 tables (hard to list down all attributes , so i give an example) Table 1: Name Nationality Birthday nickname school allen Japan 01/Jan/2002 al ABC SCHOOL sarah USA 02/Feb/2003 sal BBB SCHOOL Nicole UK 03/Mar/2004 Nic CCC SCHOOL ---------------------------------------------------------------------------- Table 2: Code Nationality Birthday Remark 01 Japan 01/Jan/2002 good boy 02 USA 02/Feb/2003 Nice girl 03 UK 02/Mar/2004 ok person! ---------------------------------------------------------------------------- Problem: I tried to join them to produce a new table, but i get double result for each entry. ---------------------------------------------------------------------------- Question: Can i use "DISTINCT" OR "GROUP BY" Function ? If so, please show me the SQL . Thanks you ! -- Allen Phailat Wongakanit |
#7
|
|||
|
|||
Joined table: Returns duplicated record
Joins should generally include a unique field or fields on one side of the
join. If you don't have unique values, you may have duplicates. If you have specific examples, someone might be able to help you. -- Duane Hookom Microsoft Access MVP "KC_Cheer_Coach" wrote: So, how does he write it so the records do not duplicate? I have to do the same thing and am currently writing seperate queries for each comparison I am doing (answer to a similar question I asked in this community earlier), however when I put these queries together with a union, they duplicate. "Duane Hookom" wrote: If you have two people with the same nationality and birthday, you will have duplicated records. -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Default.htm "ali" wrote: Alright Hookom!, since this is a sample database, i'll try my best to portrait my real situation. (cuz my real table contains too many attributes !) I joined INNER JOIN (table1.Nationality = table2.nationality) and (table1.birthday = table2.birthday) I also want to display all attributes from both tables. Thanks a lot again, Hookom ! -- Allen Phailat Wongakanit "Duane Hookom" wrote: How did you join the tables? Could you share your SQL view of your query? What are the primary and foreign keys in the tables? -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Default.htm "ali" wrote: I have 2 tables (hard to list down all attributes , so i give an example) Table 1: Name Nationality Birthday nickname school allen Japan 01/Jan/2002 al ABC SCHOOL sarah USA 02/Feb/2003 sal BBB SCHOOL Nicole UK 03/Mar/2004 Nic CCC SCHOOL ---------------------------------------------------------------------------- Table 2: Code Nationality Birthday Remark 01 Japan 01/Jan/2002 good boy 02 USA 02/Feb/2003 Nice girl 03 UK 02/Mar/2004 ok person! ---------------------------------------------------------------------------- Problem: I tried to join them to produce a new table, but i get double result for each entry. ---------------------------------------------------------------------------- Question: Can i use "DISTINCT" OR "GROUP BY" Function ? If so, please show me the SQL . Thanks you ! -- Allen Phailat Wongakanit |
Thread Tools | |
Display Modes | |
|
|