Table and Relationship Structure

[jamen_98]

I’ve looked for similar scenarios on this forum and came close on a few, but it’s still not what I’m looking for.

Scenario:

I have two tables that hold different information. One table is static data elements related to system hierarchy and the other table houses user defined configuration parameters.

Table 1
Field 1:Value 1 (hierarchy level 1)
Field 2:Value 2 (hierarchy level 2)
Field 3:Value 3 (hierarchy level 3)
Field 4:Value 4 (hierarchy level 4)

Table 2
Field 1:Value 1 (user selected parameter)
Field 2:Value 2 (user selected parameter)
Field 3:Value 3 (user selected parameter)
Field 4:Value 4 (user selected parameter)

The combination of Table 1 values determine which configuration parameter I need to use in my Report.

[Table1]![Value1].Value+[Table1]![Value2].Value+[Table1]![Value3].Value+[Table1]![Value4].Value = [Table2]![Fieldx] where x is any one of the 4 fields in table 2 based on the values of Table1 heirarchy.

The desired output in my report should look like:

Table 1 Table 2
HLevel1 HLevel2 HLevel3 HLevel4 Parameter
Value 1 Value 2 Value 3 Value 4 Field3.Value
Value 1 Value 2 Value 3 Value 4 Field1.Value
Value 1 Value 2 Value 3 Value 4 Field2.Value

I could potentially write individual records to a table, but I don’t think that is very efficient. I would rather use tables and relationships first and then temp variables if needed.I’ve also thought about a third association table, but not sure how I would join them.

Any thoughts on how to structure the table and relationships?

 

[HiTechCoach]

It is very difficult to help you with just general names like tbaleX, fieldY, and valueZ.

We really need to see some same data and know what it represents.

 

[jamen_98]

Sure - sorry, I was trying not to be prescriptive but more details would probably help.

Table 1 is a static hierarchy with approximately 1,000 variations.

Table 1 Fields:
Category
Screen
Section
Parameter

Table 2 has user selected parameters.
Color Parameter
Size Parameter
Shape Parameter
Format Parameter

Table 2 has around 200 user selected parameters.

I need the combination in the Hierarchy to be listed for each user selected parameter.

[Category: Appearance | Screen: Colors | Section: Lower | Parameter: Widget Color] is associated with a user selected Table 2 "Color Parameter = Red".

My report that I'm looking for would look something like:

Category | Screen | Section | Parameter | <-Header Row

Appearance | Colors | Lower | Widget Color | Red <-Record
Appearance | Colors | Lower | Widget Color | Blue <-Record
Appearance | Colors | Lower | Widget Color | Green <-Record

---- Variations would be ----

Appearance | Colors | Upper | Widget Shape | Horizontal <-Record
Appearance | Colors | Lower | Widget Shape | Vertical <-Record

Essentially, I'm trying to have static parameters mapped to user defined configuration settings.

Does that help? Am I looking at this backwards? I feel like it's a big puzzle that I'm trying to work backwards.