Many-to-many r/ship and another one-to-many r/ship, but tables aren't connected

[guixian88]

Hi...

I have three main tables, tblRadio, tblBlocks and tblFields. I'd like to have a many-to-many r/ship betweeb tblRadio and tblBlocks and a one-to-many r/ship with one Block having many Fields.

So, I created a junction table named tblMapRadioBlocks, with two primary keys, RadioID and Block ID. These are the fields of my tables.

tblRadio
RadioID (PK) (one)
RadioModel

tblMapRadioBlocks
RadioID (PK and FK) (many)
BlockID (PK and FK) (many)

tblBlocks
BlockID (PK) (one)
BlockName

tblFields
FieldID (PK)
FieldName
FieldValue
BlockID (FK) (many)


The problem is, when I click on the little plus sign in tblRadio to expand a record to view its subdatasheet, I only see blocks, and I can't view the Fields from there.

And I need to create a report, which will display the Radio, with the blocks belonging to it, and the fields belonging to the blocks. but everytime I select a field from tblFields to be included in the report, it won't group the thing properly anymore.

When I was using jz a simple one-to-many r/ship for tblRadio to tblBlocks and tblBlocks to tblFields, I could come up with a report like this

Radio ID
BlockID BlockName
FieldName1
FieldName2

What do I need to do so that I can view the fields of a block of a radio?? Help, please... Thanks.

 

[guixian88]

In other words, I jz wanna know how come Access can't figure out that the fields belong to the blocks which belong to the radios.

It can recognize that one radio has many blocks, it can recognize that one blocks has many fields...

But it can't figure out that one radio has many blocks which has many fields (within each block).

Could there be something wrong with my table design?