How to link 2 tables using a link table?

[vc1881]

Hello,

Table 1 contains

ID Make
1 GM
2 Ford
3 Chrysler

Table 2 contains

ID Colour
1 Red
2 White
3 Blue


LinkTable contains

ID Make_ID Colour_ID
1 1 1 (GM Red)
2 2 1 (Ford Red)
3 1 3 (Ford Blue)

My report is group by the two columns in the LinkTable

Group1 is grouped by Make_ID from the LinkTable, the Make field from the Table 1 is also included in Group 1

Goup 2 is grouped by Colour_ID from the LinkTable. The problem occurs when I insert the Colour field from the Table 2.

I can't figure out why group 2 is not work, please Help.

Thanks,

Victor

 

[IanWaterman]

PLease show SQL statement so we can see how you have linked tables.

Ian

 

[vc1881]

Hello,

I don't have an SQL, in the tables are linked through the relatioship section of access.

table 1 Id is linked to Make_ID field in Link_Table (1 to many)

table 2 Id is linked to Colour_ID field in Link_Table (1 to many)

Tables are also linked the same in the link section of crystal report.

Victor

 

[IanWaterman]

What problem occurs when you add the colour field.

YOur links look OK.

Ian

 

[vc1881]

The data from group1 dissapears, seems like adding group 2 with group 1 causes the report not to work.

For example:

group 1

GM
Ford

Group 2
Red
Green

Group 1 is not linking with group 2.

Victor

 

[IanWaterman]

Please show exact layout as data appears, including details.

How many records are returned(count from lower right hand side)

Ian

 

[vc1881]

The files are in this link:

http://www.box.net/shared/rbi9gebnhg

Thanks,

Victor

 

[lbass]

Groups are nested within each other, so you should see the following based on your data above:

Ford
Blue
Red
GM
Red

-LB

 

[vcharles]

I just made some changes, but it's still not working the way it shoul, the powepoint file shows the design and desired output, the latest report ends with 2008.rpt. What table should I be grouping by?, I think I'm grouping on the wrong tables/fileds.

http://www.box.net/shared/rbi9gebnhg

Thanks,

Victor

 

[vcharles]

Hello,

I finally figured it out

Thank You for all your Help.

Victor

 

[lbass]

Please explain what you figured out so that others can benefit.

-LB