Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009
    Posts
    28

    Unanswered: Multiple Occurences in a table

    My report needs to show occupancy in properties over the 52 weeks of a year. My fields will include location, week_no, tenant_name,
    co-habitant_name, and up to 6 names of dependents.
    But I do not want to have to define ( 52 times 8 ) fields in my table for all the occurences. So is there a way, in my table design, to accomodate this scenario ? I used to program in Cobol, and this was dead easy. But I cannot think how to do this in Access.

    Please also advise how to handle the table design being recommended
    in the actual report ?

    Many thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Off the top of my head I would have something like:
    property {property_id, address, landlord, ....}
    property_letting {property_id, start_date, end_date}
    property_letting_tenants {property_id, start_date, name, tenant_type}
    You work out the weeks from the start and end dates. Instead of 8 columns of tenants you instead have n rows of tenants and indicate the tenant type in the tenant column. This design is normalised and scalable. You would need some additional integrity checks to ensure there are no overlapping letting periods for a property.

  3. #3
    Join Date
    Jul 2009
    Posts
    28

    Multiple Occurences in a table

    thanks PootleFlump : I'll experiment further with this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •