You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
So imagine you have a (very long) .csv file listing the student name and date, time (am or pm) and title of each of their exams (about 8 each on average) for about 400 students. Then imagine you want to manipulate that data and give each one a customised printout of their 5 week exam period in a sort of vertical calendar format with their exams printed in when they have one and blanks when they don't. The idea is they use it for planning and telling me when they are going to be in to study.
How would you go about it? I thought I was relatively conversant with mail merges but I'm struggling. Maybe it's easier with a little access database. Thoughts?
Current format of data:-
1234 Joe Bloggs 21-5-19 am GCE History Paper DT3
1234 Joe Blogges 25-6-16 pm GCE English Lang Paper 1
1234 Joe Bloggs 29-5-19 am GCE Maths Pure 1
1234 Joe Blogges 3-6-16 pm GCE English Lang Paper 2
1234 Joe Bloggs 6-5-19 am GCE History Paper DB9
1234 Joe Blogges 14-6-16 pm GCE Maths Applied 1
1345 John Smith 21-5-19 am GCE History Paper DT3
1345 John Smith 29-5-19 am GCE Maths Pure 1
etc
If you can use Access then that might be easiest. Your problem is that you need to add extra info for the times that they don't have exams. This is relatively easy in Access with a left join to a table with the full list of dates and times. A mail merge by itself probably isn't enough
but.... what generated that csv list? Is it not possible to get it to generate the same list but also include the times when people don't have exams? That might make your mail merge way easier
but…. what generated that csv list? Is it not possible to get it to generate the same list but also include the times when people don’t have exams? That might make your mail merge way easier
Sadly a quite clunky school admin and exam entry database that I don't have access to play with.
You might not be allowed to use the database but quite often you can link in to the raw data to report in the way that you want? (at least in my experience of being frustrated with some of the stuff that I get given)
anyway, if you can use Access yourself it should be easy enough to read in the data and either do the whole left join thing or just add blank records to fill in the dates/times you don't have. There does appear to be some fancy grouping thing in MailMerge that might also do it but from the looks of things it is way worse than using Access - which is quite difficult to believe really
I'd be looking at a Pivot Table.
(Haven't used one for years but it would give you the output you want),
I was wondering about pivot tables (you could add blank lines for every date at the start to make sure they were all there) but I could see how we would print one page per student out
Do you know (or have any interest in learning) python or another language? On the surface it looks like it would be quite straightforward to write a little script that reads from the csv and then writes to a template blank calendar in word (e.g. using the mailmerge python package).
I'd do it in Excel and a macro. (Not because it's the best way but because I know I could do it)
Create a sheet by hand of all the time slots. Load the csv file into another sheet, and write the macro to copy the time slot sheet into a new sheet, and change the name to the student. Repeat for all students. Then for each student search in 'their' sheet for the time slot in the csv list, and copy in the exam details.
Getting the details to the students depends on what details you have, but you could then export each sheet and do a Word mailmerge thing.
@convert - did you find a solution ? I'll happily help you with an Access solution if you want to go down that route
leffeboy, with no easy obvious excel based solution coming from the hive mind I am going to go access. But.......I've actually wanted to make a lot more comprehensive database to do a whole bunch of things (all equally tedious) so I'm using this need as a starting point.
I've made some pretty whizzy databases in the past but not for a long while. In a very sad way I'm looking forward to it - starts this afternoon.
Excellent. If you are comfy with Access then it really shouldn't be too bad. PM if you want some help. Can't guarantee to be able to but I spend a reasonable amount of time in Access so happy to try