Many to Many Relationship

hi...i'm new here. i was wondering if someone could help me out.

i have a many-to-many r/ship between the tblRadio and tblBlock tables.
so, i created a junction table called tblRadioBlock with the primary
keys of tblRadio and tblBlock as primary and foreign keys.

then, i have another table called tblField, which has a one-to-many
r/ship with the tblBlock, ie one block has many fields. so, i have a
the tblBlock primary key as a foreign key in tblField.

the problem now is, that i can't seem to link the data from field to
block to table when i'm creating a report. whenever i select fields
from these three tables, it won't group properly. if i select tblRadio
and tblBlock fields, the report wizard will group Radio fields then
Block fields. if i select Block and Field fields, it'll group by block
then by fields.

when i select fields from all three tables, i just get one lump of
data, which is not what i want at all. i hope i've stated my problem
clearly. could anyone tell me what i'm doing wrong?

i'm thinking it probably has something to do with the relationships,
since if the relationships are correct, the report wizard will group
them correctly.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

guixian88 wrote in
:

i have a many-to-many r/ship between the tblRadio and tblBlock tables.
so, i created a junction table called tblRadioBlock with the primary
keys of tblRadio and tblBlock as primary and foreign keys.

then, i have another table called tblField, which has a one-to-many
r/ship with the tblBlock, ie one block has many fields. so, i have a
the tblBlock primary key as a foreign key in tblField.

Okay: we have

Blocks RadioBlocks
====== =========== Radios
BlockID ------+--- BlockID ======
Description \ RadioCode --------- RCode
BlockStats | Frequency
etc | NumTransistors
| Fields etc
| ======
| FieldNumber
+-- BlockID
CropType
etc

the problem now is, that i can't seem to link the data from field to
block to table when i'm creating a report.

I don't understand. What is a Table -- we only have Radios, Blocks and
Fields in the description above?

the report wizard will group Radio fields then
Block fields. if i select Block and Field fields, it'll group by block
then by fields.

The best answer is just Don't Use the Wizards. Start off by creating the
query and get the grouping right on that first. Use the query grid design
window and enter the tables you actually need, and then join them and sort
them. Once you understand your data, then you can create the report -- the
wizard maybe a little quicker than doing it by hand although I usually find
the reverse. But do get the query right first!

Hope that helps


Tim F

In article ,
says...

hi...i'm new here. i was wondering if someone could help me out.

i have a many-to-many r/ship between the tblRadio and tblBlock tables.
so, i created a junction table called tblRadioBlock with the primary
keys of tblRadio and tblBlock as primary and foreign keys.

then, i have another table called tblField, which has a one-to-many
r/ship with the tblBlock, ie one block has many fields. so, i have a
the tblBlock primary key as a foreign key in tblField.

the problem now is, that i can't seem to link the data from field to
block to table when i'm creating a report. whenever i select fields
from these three tables, it won't group properly. if i select tblRadio
and tblBlock fields, the report wizard will group Radio fields then
Block fields. if i select Block and Field fields, it'll group by block
then by fields.

when i select fields from all three tables, i just get one lump of
data, which is not what i want at all. i hope i've stated my problem
clearly. could anyone tell me what i'm doing wrong?

i'm thinking it probably has something to do with the relationships,
since if the relationships are correct, the report wizard will group
them correctly.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/



Hi,

Your relationships seem reasonable, but I can't tell whether they
reflect the real information you are trying to describe.

If you build a report with Radio, RadioBlock and Block, grouped by
Radio, you'll get the same Block appearing underneath EACH Radio to
which it is related. Then, for each Block, you'll get multiple child
Field records. You can do show these relationships either with Sorting
and Grouping, or by building subreports to show the lower levels.

Radio A
Block 1
Field 11
Field 12
Block 2
Field 21
Field 22
Radio B
Block 2 (note - also appears in Radio A above)
Field 21
Field 22

If this doesn't help, try posting a sample format of what you want your
report to look like.

--
Armen Stein
Access 2003 VBA Programmer's Reference
http://www.amazon.com/exec/obidos/AS...jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com