Multiple Occurences Of Fields

[grinder332518]

My report needs to showi occupancy in residences over the 52 weeks of a year. My fields will include location, week_number, tenant_name, co-habitant_name, and up to 6 dependents.
As I do not want to have to define 52 * 8 fields in my table for all the occurences, is there a neat way, in table design, to accomodate such a requirement ?

Although this post is in table design, can you further advise in my report how best to handle the table design being recommended ?

Many thanks

 

[JamesMcS]

You could use a crosstab query to do this, and base your report on it too.

 

[Galaxiom]

If you have a record for each week then you should only need to define one set of controls and then print a series of records covering the weeks of interest.

BTW. Just an inkling but the "up to six dependents" suggests you might have a flat structure tenant table with six field for dependents?

If so this is better done using another table related to the main Tenant table by TenantID. It has one record for each dependent and a field to record which tenant they relate to. One tenant can have unlimited dependents.

 

[grinder332518]

thanks GalaxiomAtHome & JamesMcS.
I have got a solution to this.
Best Wishes