I have a Widget Model W123 that is made up of SubWidgets. Each SubWidget may have multiple model numbers. Take a look at the table below. Widget Model W123 has two different SubWidget Type AA model numbers. Notice that the total number of SubWidget Type AA units is the same as the total number of Widget Model W123 units.

The second table represents a similar relationship between Widget Model W123 and SubWidget Type BB. The difference here is that SubWidget Type BB has three model numbers instead of two. Also notice that the total number of SubWidget Type BB units is the same as the total number of Widget Model W123 units.

Reporting against these relationships is fairly simple if you only want to show one Widget to SubWidget relationship at a time. My question is this – if you wanted to report both of the relationships shown below in the same report how would you go about doing it?

The data is stored in three tables. The Widget table is joined to the SubWidgetAA table via a one-to-many PK/FK relationship. The Widget Table is also joined to the SubWidgetBB table via a one-to-many PK/FK relationship.

SORRY FOR THE UGLY LAYOUT BELOW. TRIED TO PASTE HTML FROM ANOTHER AUTHORING TOOL.

Widget_Model	Widget_Units	Sub_Widget_Type	Sub_Widget_Model	Installation_Percentage	Sub_Widget_Units
W123	100	AA	SW987	25	25
			SW654	75	75
TOTAL	100			100	100

 

Widget_Model	Widget_Units	Sub_Widget_Type	Sub_Widget_Model	Installation_Percentage	Sub_Widget_Units
W123	100	BB	SWABC	20	20
			SWDEF	30	30
			SWGHI	50	50
TOTAL	100			100	100

 

This message has been edited. Last edited by: Kerry,

Dan,

I would use TAGs in the JOIN command to differentiate between the sub-fields in the 2 different paths. Then, ideally, MORE should be used. However, MORE demands that the fields have the SAME names, which is contradictory. So you are left with using 2 TABLE requests and HOLDing in the same file with a FILEDEF and APPEND.

Danny,

I like your idea of appending the data. That may also make it easier for me to deal with the potential multiplicative effect.

I'll give it a try and let you know how it goes.

Thanks!

Dan