1 Reply Latest reply: Aug 29, 2013 9:02 AM by Fernando Tonial RSS

    Date Tables with duplications

    Phil Speight

      Hi

       

      I have 40 areaswithin my organisation and I have 2 Billing cycles.  Some areas are on cycle 1 and others on cycle 2.

       

      Each cycle has a publish date which is 56 days following the previous publish date.  However both cycles original cycle start date are different.  i.e.  Cycle 1 started on 04/02/13 for 8 weeks, and cycle 2 started on 18/02/13 for 8 weeks

       

      I have created a report which shows the billing date for the customer, however I want to show if the date is published based on todays date.  I have created a table which shows which cycle each area is on based on the billing date, however this will hold duplications as there is more than one cycle. e.g. the table for my billing cycles will have 2 seperate rows for each cycle publish date .

       

      The problem I have is that I only have the following fields to link

       

      Table 1 - Bill amount

      Billing date

      Billing amount

      Customer Ref (PK link)

       

      Table 2 - Customer

      Customer Ref (FK Link)

      Customer name

       

      Table 3 - Division member

      Customer Ref (FK Link)

      Division Name (PK Link)

       

      Table 4 - Cycle

      Cycle Name (PK Link)

      Division Name (FK Link)

       

      Table 5 - Cycle Dates

      Billing date

      Cycle Name (FK Link)

      Publish date

      Cycle start date

      Cycle end date

       

      When I link table 5, I get a duplication as there are 2 rows with the same Billing date.

       

      I am sure there is a simple solution.

       

      Please help

       

       

      Thanks

       

      Phil