Problem with Link Table and Multiple Calendars

[#1]

I have a problem with the data model that i'm building which is driving me to despair (i've spent countless hours trawling this forum for a flash of insperation but i'm coming to the conclusion that maybe what i want is not actually achievable) .

The background

In its basics I have a one to many link (between Tickets and Ticket_events) which also uses a master calender. This is linked to date fields in both of my tables. In the Tickets table it is linked to the ticket issue date and in the ticket_events table it is linked to an audit date of when a particular action occurs.

I'm using a link table between my Tickets table and Ticket_Events table. These are linked via a Primary/Foreign Key which originates from the Tickets table (t_number). The Ticket_events table has its own PK defined as a simple RowNo(). My PK/FK's are defined in the each table and are used to create the Link Table. The FKs from each table are dropped once the link is added to the link table (Its a variation on Lars Christians Link table that works by defining PK and FK fields to be linked on rather then using the same field name)

This is a very simplistic view of the overall model as it should include a further 30 plus tables, the majority of which can relate to the Ticket PK value, plus can also have there own calendar field which could also be mapped to the master calendar.

Now the issue I have

Within my application I have various calendar filters, one of which is year. If I select Year then it correctly displays the right number of ticket events from 2010, based on the ticket event audit date.

However the object below shows a count of the single tickets (from the ticket table) that have occurred (based on the ticket issue date as the dimension). What I believe is occurring is that the ticket events in 2010 CAN relate to tickets outside of the 2010 year selected. Therefore because the link exists between Ticket and Ticket_events is is also returning those tickets which have an issue date outside of the selected 2010, and at this point ignores the master calendar filtering down to the ticket issue date being filtered to only show 2010 tickets. The totals shown for 2010 are correct.

If i was to replace the first dimension with the actual calendar year/month/day variables then the results I get are even more screwed.

I'm hoping that someone is able to give me that flash of inspiration i need as sorting this would certainly be a welcome Christmas present.

Previously i've tried removing the link table and joining back on matching fields between Ticket and Ticket_events and then linking the Master Calender to both tables but this obviously causes a loop between the tables (this is the main reason i started to look at Link tables to resolve this). Previously i have also used Start and End input boxes with a variable in order to have single date inputs which could then be used over multiple calenders.

Many thanks in advance

Lewis

[#2]

Hi.

What I usually do in cases like this is to drill down to the place where the data is making no sense (let's say selecting year 2010 in one of the objects where the total is wrong, and some other selections in order to minimize the data-set) and then creating a table box with *all* the fields in the scheme. (In your case they're related and there are no stand-alone tables, therefore it should work just fine)

In this way you'll be able to see the number of records, and what is effecting and/or preventing you from getting the results you desire.

Regards,

Montal.

[#3]

Montal - thanks for the suggestion!

Having just done what you suggested i limited my data to 2010 and April. Contained within the table box were all the Ticket_events for April 2010. However as i suspected it also returns all associated Tickets which do fall outside the selected date range, some as far back as 2007.

If i subsequently drop the link between tickets and ticket_events then both correctly filter on the date range

Thanks

Lewis