fields w/no value

[Lorelei]

I am trying to run a report of services received.

The query looks like this:

client id case mgmt counseling home delivered meals medical
referral
35 3
2
40 1
1
44 10
1
55 2 1
1

Not all fields have a value for each client.

Is it possible to get a report that only lists the populated fields for each
client? Right now it is pulling each field for everyone regardless of whether
there is a value - the report is HUGE!!

Any help will be appreciated.

 

[golfinray]

Put Is Not Null in the criteria of all fields in your query.

 

[Arvin Meyer [MVP]]

Use a query, in the criteria box for each field that requires data, use the
expression:

Is Not Null

 

[John Spencer]

What is the table structure? IF the structure is
ClientID
ServiceName

Then you can use a report based on unique client ids and a multi-column
subreport (across then down) to get the results you want. Source query would
look like:
(SELECT Distinct ClientID from TheTable)

The sub report would be linked by clientID and show ServiceName and
ServiceCount. Source query would look like:
(SELECT ClientID, ServiceName, Count(ServiceName) as TheCount
FROM TheTable
GROUP BY ClientID, ServiceName)

If your structure is more like
ClientID
CaseManagement
Counseling
Referral
....

Then this would be much more difficult to accomplish unless you choose to use
a normalizing union query to force the data into a structure like the one above.

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