当前位置: 动力学知识库 > 问答 > 编程问答 >

I can't make an easy query in Access, maybe problems with primary key

问题描述:

I have a table with columns (29), in which the FrontID is the primary key, but I have another 10 tables (only 4 columns each) which also have the FrontID column but otherwise there is another field called Desc1, Desc2 ... Desc10 and what I want is to fill the first table with the other 10 DescX colum so that only appear when both rows have the same FrontID .

All tables are in .csv format, so I have created the tables in Access and I have charged it in .csv but then when I am trying to make the Query to consilidate all the information, I couldn't get what i wanted, I think that the problem is that i didn't relationated the tables..

In other way, I tried to create a table with 39 columns (29 + 10) to see if they could load when I import the .csv the DescX fields but it didn't work.

Any idea how can I tackle the problem?

Eg. of the bigger .csv file:

DDATE xxx xxx FRONT_ID xxx xxx xxx xxx xxxxxx xxx xxx xxx xxx xxx xxx CCY xxx xxx xxx xxx xxx xxx xxx xxx xxx xxxxxx xxx xxx xxx xxx xxx xxx xxx

And the other 10 .csv are like these:

DDATE FRONT_ID Desc1 xxx xxx

So I want to have the first table with the new Desc1 colum when the FRONT_ID are the same in the original table(the bigger) and the new ones.

Thanks in advance,

Raul

Ok I think this is a better example:

I have 3 tables for example:

 Main Table | TABLE 2 | TABLE 3

ID Name | ID Surname | ID Phone

1 Jhon | 1 Surname1 | 1 666-555-444

2 Mark | 2 Surname2 | 5 777-777-777

3 Susan | 3 Surname3 | 6 123-456-789

4 Peter |

5 Yod |

6 Samuel |

And what I want to get is similar like this:

FINAL TABLE

ID Name Surname Phone

1 Jhon Surname1 666-555-444

2 Mark Surname2

3 Susan Surname3

4 Peter

5 Yod 555-444-333

6 Samuel 444-333-222

I try something like this:

SELECT Table1.ID, Table1.Name, Table2.Surname, Table3.Phone AS Expr1

FROM Table1, Table2, Table3

WHERE Table2.ID=Table1.ID OR Table3.ID=Table1.ID

And I tried making a Query using INNER JOIN too but my result was horrible and i dont know why:

1 Jhon Surname1 666-555-444

Can someone helps me? Maybe I need to write some macro to add new columns on the main table?

As u all can see im new in Access,s o I apreciate some tutorial or a nice page to read too.

Best regards,

Raul

网友答案:

You stated you used Inner Join with an example. This is almost the right way: Just replace INNER JOIN With LEFT JOIN. (Use your stated query which is not seen in your question)

Inner join says it must have an entry in main and both other tables. If you use Left join (Left on main table), you will see the entries from Main Table even if there is no entry in Table 2 or 3.

See also here http://www.w3schools.com/sql/sql_join_left.asp

分享给朋友:
您可能感兴趣的文章:
随机阅读: