request for suggestions about how best to display some information

Please can I ask for some suggestions about how to display the following information about patients in hospital who have infections?

Firstly for all the patients in hospital with an infection (aprox 50 max at any one time) I want to show for the last few weeks how many patients were on each ward each day and a breakdown of the different types of infection.

Also I want to show the ward each patient who had an infection was on each day during their stay in order to spot when patients where on the same ward at the same time where cross-infection may have occurred.

All ideas thoughts suggestions appreciated, i can't think what approach to take.

thank you very much for going to so much trouble, i like the concept although there are about 15-20 different infections so VBA would probably be needed to do the colouring.

i am going to mull it over and show colleagues so may come back later with more comments or questions but just want to thank you again it is very appreciated.

i showed the nurses and they asked if the wards could be along the top and dates down the side but the total number of infections isn't needed. the infections are:

MRSA
Campy
TB
MSSA
Salmonella
Scabies
E.Coli0157
Shingles
Listeria
Strep A
Strep B
Klebsiella pneumoniae
Norovirus
Aspergillus
Swine Flu

i admit that i am blatently trying to take advantage of your kindness by posting this in the hope you still need a project and i will fully understand and expect you to tell me where to go although if it is where to find some VBA that might by useful anything would help. have a good Easter.