Access 2007 Report Grouping

Question

I'm using an access 2007 database to track and report down time and reliability of devices. I've got four different devices in the Devices table. I also track all downtime occurrences in it's own table. We also need to know when the devices are actually in use, so we can tell if the downtime affected our operation at all. We keep track of use in a different table.

My report looks up all the downtime occurrences, groups them via device and then uses the group footer to sub total the downtime, look up use for the period and calculate the up time percentage.

My problem is that if there is no downtime, the access report skips that entire device (obviously because it doesn't know about it from just querying the downtime table). I would like to always see the grouping for all devices and if there is no downtime it should still show the footer with total use and reliability statistics.

I realize access isn't maybe the best tool, but in my work environment that's all I have easy access too. I also realize without getting overly specific it might be hard to give concrete answers but hopefully someone can steer me in the right direction.

Thanks for any insight.

Answer 1

You do not provide details, so here is a somewhat vague answer. The best way to do this is usually to set up your query for the report so that it always contains a full list of devices. This can be achieved by a LEFT JOIN from the main device table to the data table. You can use Nz to return a suitable value if nulls are a problem.

SELECT A.DeviceID, A.Device, A.Col1, B.Col2, B.Col3 
FROM TableWithFullListOfDevices A
LEFT JOIN TableWithDownTimeResults B