Table and Relationship Structure

jamen_98

New member
Local time
Today, 02:03
Joined
May 2, 2017
Messages
6
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?
 
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.
 
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.
 

Users who are viewing this thread

Back
Top Bottom