weekly timetable report format

[merlin777]

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!

[June7]

You want to pivot the data. Several methods to pivot data.

1. CROSSTAB query

2. emulate CROSSTAB with expressions

3. VBA and writing records to a temp table

Review https://www.accessforums.net/sample-...ase-45375.html

[merlin777]

Thanks June7 - I'm working my way through those. I can't find how to display a text result rather than a sum/average etc. It might also be causing me a type mismatch when I try to label the columns with a weekday name.

[June7]

Would have to use First, Last, Max, Min with text values.

[merlin777]

I tried this but I got 'data type mismatch in criteria expression'

field: firstname
table: tbltherapists
total: first
crosstab: value

Please could you give me an example? I tried googling 'crosstab queries with text' and can't find much to help.

[June7]

Did you use the CROSSTAB query wizard?

An example sql:

TRANSFORM First(Games.[Plate]) AS FirstOfPlate
SELECT Games.[Field]
FROM Games
GROUP BY Games.[Field]
PIVOT Games.[Division];

[merlin777]

I did - and then I tried to modify it. I'm afraid I don't understand your example sql. I was hoping I could change these:
field: firstname
table: tbltherapists
total: first
crosstab: value
to something that would allow a text value to display.

[June7]

Here is same advice given in your other threads:

Advise not to use lookups in table http://access.mvps.org/access/lookupfields.htm

If you want to show the associated descriptive text, will have to be a query that joins tables.

[merlin777]

Here is same advice given in your other threads:

Advise not to use lookups in table http://access.mvps.org/access/lookupfields.htm

If you want to show the associated descriptive text, will have to be a query that joins tables.

I promise I'm not ignoring your advice but I don't think I am using a lookup field - you've seen my db so if you can see otherwise please point them out to me?

I thought I had joined tables with the query. In the query design view with the table relationships visible, i can see see tblappointments and tbltherapists and they are joined from the therapist field to the id field by a many to one relationship.

[June7]

I lose track of what databases I have seen. Doubt I still have it. Want to provide it again or post link to thread it can be found in?

[merlin777]

i've provided a more recent version of my experimentation db.

tblappointment is the form i'm trying to get working. if you type 14/1/15 into the leftmost of the long boxes it will pass to the filter and return a few records in the query. the listbox i'm having trouble with is underneath.

TIMETABLE TEST QUERY is the relevant query.

[June7]

DO HAVE lookups set in table. See the Therapist field in tblAppointment. It's set for a combobox and has a RowSource that includes the ID and name fields from tblTherapists. This obscures the actual value in the Therapist field. You see the FirstName but the actual value is ID. Same for Patient, appstartid, appendid.

The listbox (why is it named Combo18?) on the form has a RowSource that is a query object. That query does not include the LastName and FirstName fields.

Then the listbox ColumnWidths property is not set. Might want to control the widths of the columns and hide the ID column: 0";0.5";0.5";0.5".

Need code in the textbox AfterUpdate event to requery the listbox: Me.Combo18.Requery

If you are going to use non-U.S. non-Access standard dates, should probably review http://allenbrowne.com/ser-36.html

[merlin777]

thanks once again for that... looks like i've got a busy afternoon ahead of me!

it's called combo18 because when i first inserted a control it was a combobox and I used the change-to option to change it to a list box.

[merlin777]

OK - i've started on it earlier than I expected to.

when i first set up the table i entered text into these fields directly. then i created other tables and used the lookup wizard. I discoved that wasn't good practice so I changed the fields to a number field and created relationships with the relevant tables.

So I'm surprised to find they are still lookups and I don't know what I should do as an alternative. going back to entering text would be bad! What should i have done?

[June7]

Change to textbox in the table, still number type field. Set the 'lookup' with combobox on form.

Might give controls more meaningful names.