Data Model

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