Hi,
I have a data modelling issue I am hoping some can help with.
I have 4 Tables:
A Date Table
A look up table containing the Building and Area
And Two other separate Tables (Table 1 & Table 2)
Table 1 Includes the building, the date and a number of different total fields.
Table 2 includes the building (different buildings from table 1) the date and a completely different set of totals from the first table.
I want to be able to use the Totals from both tables (table 1 and Table 2) in one report page. I have connected both tables to the look up table (using the building field) to get other information e.g Area. I have then used the Area and Building fields (from the look up table) to control the visuals on my report. I have set the to cross filter both ways.
The problem I have is when connecting the Date table. As when I try to connect it to the Date feilds in Table 1 and then Table 2 I get the following Error.
“You can’t create a direct relationship between Table 2 and Date because that would introduce ambiguity between tables Date and Table 1. To make this relationship active deactivate or delete one of the current relationships between Date and Table 1 first”.
Essentially, I want to be able to control my report by using the Area and building fields in the look up table and then a date field (from the date table) that controls the Totals fields from both table 1 and 2. How do I achive this?
Thank,
Boycie92
