I'm new to access and report writing. I need to produce a report which is a weekly timetable with columns for monday to friday and rows for 0800 to 1700.
My main table is a list of appointments with a date, start time and end time, therapist name and client name (apart from the date these are all id numbers pointing to tables with lists of those things).
To complicate things slightly, rather than one appointment per hour, they are grouped into hour periods and are placed into a cell according to their start time (the end time and duration are ignored). If an appointment starts after 45 mins past the hour it goes into the next cell. For instance, in the hour period between 1000 and 1100 the cell will contain appointments starting at 0945, 1000, 1015, 1030 etc. There will not usually be more than 3 per hour. The appointment details will be the first name of the therapist and the reason for the appointment e.g. 'Claire Jones/Physiotherapy'.
So, my question is, what would be a good approach? Do report properties lend themselves to putting things in a matrix or do I need 40 queries (one for each of the 8x5 matrix cells)? Can I filter in the report or should I use a query to filter the appointments first? Somehow, the filtering will need to be supplied with a patient name and date (for the monday of the required week).
Any advice, greatly appreciated!