Access Trusted Location
Windows 8 
Access 2016
In case the structure in post #10 is really what you have:
a) in case the database must be used for several years, a separate database must be created for every year;
b) in case for current year database at some point a new week must be added, you have to redesign the database, and all reports in it;
c) In case a new activity is added at some time, you have to redesign the database, and all reports in it.
My advice is to design tables like as:
tblCampers: CamperID, FirstName, LastName, CamperSex, DOB, ... (table contains all info which doesn't change over years);
tblCampingStuff: CampingStuffID, StuffType (e.g. Shirt);
tblYearCampers: YearCampersID, YearNo, CamperID, CamperLevel;
tblYearCamperStuff: YearCamperStuffID, YearCampersID, CampingStuffID, CampingStuffCost;
tblCampingWeeks: CampingWeek, CampingYear (Campingweek is a number in format yyyyww and is a PK. CampingYear is calculated from CampingWeek and the result is stored into field, and is used as source in case you have to select the year from combo - e.g. in form where campers are registered for every year, i.e. the form based on tblYearCampers, and to filter combos to select campng weeks in forms);
[tblCalendary]: CalendaryDate, [CalendaryYear], [CalendaryMonth], CalendaryWeek, CalendaryWD (An optional helper table with CalendaryDate as PK. It may be useful as source of combos, and for case you need easily get a date of certain weekday of certain week.);
tblYearCamperWeek: YearCamperWeekID, YearCamperID, CampingWeek;
tblCampingActivities: CampingActivityID, Campingactivity;
tblYearCampingActivities: YearCampingActivityID, CampingYear, CampingActivityID, [CampingActivityCost],... (CampingActivityCost is included, when the cost is same for all camping weeks of camping year);
tblWeekCampingActivities: WeekCampingActivitiesID, CampingWeek, [CampingActivityCost], ... (CampingActivityCost is included, when the cost may be different for different camping weeks of camping year);
etc.
Reply With Quote
