Reporting null values or blank sheets

[david]

Hi

I have created some reports to show equipment within rooms of a hospital
project, and all is working as I would like, apart from the rooms with no
equipment in are not reporting.

Am I right in assuming this is because they are 'null' values (sorry for my
limited knowledge of null values).

I would like these rooms to report, with the text 'No Equipment Within Room'.

Can anyone please advise on how I go about this.

 

[John Spencer MVP]

Do you have a table that shows just the rooms and information about the rooms?
You should have. If you do, you need to add that to your query and join it
to the table that shows the equipment in each room. If you don't have a table
listing just the rooms and information on the room, you need to create one.

Your table might look something like the following
tblRooms (with something like the following fields)
RoomID
RoomNumber
FloorNumber
Capacity (Single, Double, Multi-bed)
Window
RoomType: (Store Room, Patient Treatment area, Patient, etc.


You would click on the line joining the two tables and select option 2 or 3 -
whichever one says something like ALL records in the Rooms table and only
matching records in the equipment table.



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[david]

John

I have the expanded room table in the query, but there are also 5 other
tables in the query, with 4 of the tables between the equipment and expanded
room data table. And I can see no obvious link between the equipment and room
data tables.

When you refer to options 1,2,3 these only offer all from one table and thos
matching from the other, there is nothing that suggests all from one table,
including those that do not match, ie report those rooms with no equipment in
them.

Apologies if I seem a bit vague or confused, I am not the worlds best at
access, but if I can get this working it can save us so much time and money
on the speed of these reports.

 

[John Spencer MVP]

Well the option that says all from one table and only matching from the other
table means that if you get all the records from the room table they will all
be there. If there is no equipment in the room then the room will still show
up, but all the fields from the table that shows the equipment in the room
will be blank for that row.

You should have a link from the ROOM table to the EquipmentInRoom table. The
EquipmentInRoom table should link to the Room Table and to an Equipment table.

Without knowing more about your data structure, all I can say is this is
possible, but the table structure needs to be set up correctly.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County