Joined table: Returns duplicated record
in [Queries]
|
From: KC_Cheer_Coach on 27 Jun 2008 09:59 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
From: Duane Hookom on 27 Jun 2008 11:01 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
|
Pages: 1 Prev: Access Query: get first date field Next: top N per employee |