4 tables, 2 groupings and some math.....

[goldfishhh]

Table 1:
Days - Date

Table 2:
System Name - Text

Table 3:
Event Failure Date
System Name
Job Number
Failure Job ID

Table 4:
Event Sucess Date
System Name
Job Number
Success Job ID

I'm linking:

From Table 1 Date to Table 3 Event Failure Date (left outer join)
From Table 1 Date to Table 4 Event Success Date (Left outer join)

In my report, I'm grouping by Table 1 Date and performing 'distinct count Success ID" and "distinct count Failure Job ID".

Everything works perfectly.

Now. I want to further group by table 2 System Name. I'm confised on the linking. I have tried to link

From Table 2 System Name to Table 3 System Name
From Table 2 System Name to Table 4 System Name

The system keeps complaining that this type of join (inner join or Left outer join)is not allowed.

Any ideas?

 

[CR85user]

From Table 2 System Name to Table 3 System Name
From Table 2 System Name to Table 4 System Name

Reverse that which you have already tried:

From Table 3 System Name to Table 2 System Name (Left outer join)
From Table 4 System Name to Table 2 System Name (Left outer join}

You should then have the following links in place:

[/quote]
From Table 1 Date to Table 3 Event Failure Date (Left outer join)
From Table 1 Date to Table 4 Event Success Date (Left outer join)
From Table 3 System Name to Table 2 System Name (Left outer join)
From Table 4 System Name to Table 2 System Name (Left outer join}[/quote]

Whilst I cannot guarantee that this is the most appropriate way to structure your query, given the information provided it should cater for your needs.

'J

CR8.5 / CRXI - Discovering the impossible

 

[CR85user]

Meh - I messed up the quotes: shoudl have read:

From Table 1 Date to Table 3 Event Failure Date (Left outer join)
From Table 1 Date to Table 4 Event Success Date (Left outer join)
From Table 3 System Name to Table 2 System Name (Left outer join)
From Table 4 System Name to Table 2 System Name (Left outer join}

Please note the logic of this is as follows:

It takes a date from table 1 which may or may not be in table 3 and/or table 4. If the date is populated in table 3 and/or 4 then the relevant table will return information from table 2 if the system name matches that within the originating table.

I hope I have explained that properly and not simply confused matters more

'J

CR8.5 / CRXI - Discovering the impossible

 

[lbass]

When you try CR85User's suggestion, you should be adding the System Name table twice, with the second one automatically appearing witn an alias name "System_Name_1". You cannot have multiple tables pointing TO the same table.

-LB

 

[CR85user]

Apologies - forgot to point that out. Well spotted LB.

'J

CR8.5 / CRXI - Discovering the impossible

 

[goldfishhh]

Ahhhhhhhhhhhhhhhhhhhhh There ya go!

I completely forgot that you couldn't have 2 tables pointing to the same one.