Access Report

[Delia]

Please can someone help with my report:

I have a report listing desk usage on our site and who is at whatever desk
in whatever room and whatever building.

If there is an unoccupied desk the table field 'surname' contains the word
SPARE.

How can I run a report without all the SPARE lines showing up?

 

[KARL DEWEY]

Try this --
SELECT Sum(IIf([surname]="SPARE",1,[building])) AS [Bldg Num],
IIf([surname]="SPARE",Null,[room]) AS RM_Num,
IIf([surname]="SPARE",Null,[desk]) AS Desk_Num, delia.surname
FROM delia
GROUP BY IIf([surname]="SPARE",Null,[room]),
IIf([surname]="SPARE",Null,[desk]), delia.surname
ORDER BY Sum(IIf([surname]="SPARE",1,[building])),
IIf([surname]="SPARE",Null,[room]), IIf([surname]="SPARE",Null,[desk]);

Use your table name instead of Delia.

 

[John Spencer]

Probably as simple as applying criteria against the Surname field.

Field: Surname
Criteria: <> "Spare"

That criteria will screen out records that are null (Blank) and those that
have a value of "Spare"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..