join 3 table query with

[dio.m]

Hello all, I'm a newbie in sql, can somebody help me with the query, FYI, I have 2 tables and 1 Query with details in below:

TABLE_1
LocationID LocationName LocationZone
1 Singapore SE
2 Indonesia SE
3 Malaysia SE
4 Thailand SE
5 Japan NO
6 China MA
7 Korea MA

TABLE_2
RatingID RatingDesc
1 Low
2 Medium
3 High
4 Extreme

QUERY_TBL
DetailID LocationZone LocationName RatingDesc RatingDetail Status
1 SE Singapore Medium Medium M
2 MA China Medium Medium F
3 SE Indonesia Extreme Extreme E

And What I need is how to get the result with all the LocationZone, LocationName and RatingDesc, include all the data that is not inside the QUERY_TBL, for example like this(maybe, because i need all the LocationZone, LocationName, and RatingDesc to be displayed):

----------------------------------------------------------------------
LocationZone LocationName RatingDesc RatingDetail Status
SE Singapore Medium Medium M
MA China Medium Medium F
SE Indonesia Extreme Extreme E
SE Malaysia
SE Thailand
MA Korea
NO Japan
Low
High
----------------------------------------------------------------------------

Please if anyone can help me, I'm realy need it to make access report from that query. I don't know the query. Thanks Guys

 

[JamesMcS]

Hi, welcome to the forum!

So how are you going to link the two tables together in a query? There doesn't seem to be any correlation between risks and locations at the moment.... If you don't have a link, you'll end up with a Cartesian Result (google it for more info)

 

[dio.m]

Thanks for the quick reply, I'd realy appreciate it.
The main idea is to show all the LocationName based on LocationZone and all the RatingDesc with the comparasion criteria from TABLE_2 and QRY_TBL. Even with all the RatingDesc Field who do not have any value in QRY_TBL.
The goal from the query is to create a Pivot Table to review all the total count RatingDesc From All The LocationName and LocationZone in QRY_TBL. Like this: (sorry, if it mess up)

------------------------------------------------------------------------------------
****************LOCATIONZONE || LOCATIONNAME
****************SE .....................................................................| NO..........| MA
****************SINGAPORE || INDONESIA|| THAILAND ||| JAPAN ||| CHINA||KOREA
RATINGDESC
LOW.....................| 0......................|.........................| 0....................| 0................| ............|................|
MEDIUM............| 1......................|..........................| 0 ...................| 0................| 1..........|................|
HIGH...................| 0......................|..........................| 0.....................| 0...............| ............|................|
EXTREME..........| 0......................| 1......................| 0......................| 0...............| ............|................|

--------------------------------------------------------------------------------------------------------

With many thanks,

Dio

 

[dio.m]

Or maybe I will use the RatingID to QRY_TBL? But I realy don't have any idea to make the query.

 

[dio.m]

Almost forgot, I'd include RatingID to QRY_TBL.

 

[JamesMcS]

Can you upload your database so I can see what you've got so far? I don't really understand what you're getting at at the moment.

 

[dio.m]

The query is Query1, The Result that i cannot get is in Forms -> pivot_tbl_sub_riskSummary.
It should be display all the RiskRatingDesc no matter the filter selected, like the LocationZone and Name field in pivot table.

Thanks

 

[JamesMcS]

You've just got your query joins to the risk description table round the wrong way, that's all. Show all records from that table.