Finding empty records

[dlyles]

I have a report that shows a list of room numbers and items inside those room numbers. The report now can show one room number per line with a different item on each line

Item 1 room 113
Item 2 room 113
room 113
item 3 room 113

What I want to do is find all rooms that have NO instances of items. In other words, if one record shows an item in that room, but the others don't, I don't want that one (for example using the above, room 113 shouldn't show up because there are 3 instances of items showing). I want all records with NO items in them.

Any ideas?

Thanks.

 

[synapsevampire]

Rather than showing a report that isn't what you want, show the data (tables involved and example data within), and then give an exaqmple of the intended output.

You state: ", if one record shows an item in that room, but the others don't, I don't want that one" then later you state "I want all records with NO items in them", which is different, which is it? And try to avoid the term record, state specifically what data field you are using to measure an event.

Including your software version and the database/connectivity used are also important.

-k

 

[lbass]

Are room and item from different tables? If so, you would need a left join FROM the room table TO the item table. Insert a group on {table1.room}.Then create a formula like:

//{@item} to be placed in the detail section:
if not isnull({table2.item}) then 1

Then go to report->selection formula->GROUP and enter:

sum({@item},{table1.room}) = 0

This should return only those rooms that contain no items.

-LB

 

[dlyles]

That's exactly what I did. Thanks.