Fields with Data only

[DNuding]

I have a very ugly and long query that I would like to only pull out the
fields with data for each record. The query is a combination of 3 tables.
There are text, number and currency fields in the query. I tried using
IsNotNull in the Criteria lines of each field, but that did not work.

From this query, I wanted to create a report that lists only the relevant
fields from each record with data in them.

Any suggestions would be appreciated.

 

[Michel Walsh]

You want to keep the records which have a value for each of the fields?

Use NOT IS NULL for each field, ANDed together:


( NOT field1 IS NULL) AND ( NOT field2 IS NULL) AND ...


You can do it as a computed expression and add the criteria: <> 0

or modify the WHERE clause appropriately, in SQL view.




Vanderghast, Access MVP

 

[DNuding]

Ok, I will give this a try and let you know the results.
Thanks!

 

[John Spencer MVP]

Use
Is Not Null
as the criteria (note the spaces)

This will pull RECORDS where the field is not null. In a query there is no
way to pull different fields for each record depending on whether the field is
null or not.

You always get the same columns for the result.

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

 

[DNuding]

That makes sense. So how would I accomplish this if not using a query?

For each record of Back#, I only want the fields that have data.

Thanks.

 

[John Spencer MVP]

I really don't know of a method to return only the fields in a row of data
that are not null and then in the next row return a different set of fields
where the data is not null.

You might be satisfied with an output of fields like the following (slashes
are breaks between fields)

PK Field / 0 / 1 / 2
Rec 1 / FieldA: 1 / FieldB: 82 / blank
Rec 2 / FieldB: 22 / Blank / Blank
Rec 3 / FieldA: 1 / FieldB: 82 / FieldC: Lessons Learned
Rec 4 / FieldA: 1 / FieldC: Lessons Learned / Blank

IF so, you could try something like the following (4 field table example):

SELECT PrimaryKeyField, FieldA as FldVal, "FieldA" as FldName, 1 As ColNumber
FROM SomeTable
WHERE FieldA is Not Null
UNION ALL
SELECT PrimaryKeyField, FieldB, "FieldB",2
FROM SomeTable
WHERE FieldB is Not Null
UNION ALL
SELECT PrimaryKeyField, FieldC, "FieldC",3
FROM SomeTable
WHERE FieldC is Not Null
Order By PrimaryKeyField, fldName

Using a UNION QUERY like the above would allow you to get a vertical
representation of the data you seem to want.

Turning that into a horizontal layout could be a challenge involving a ranking
query and then a crosstab query.

SELECT Q1.PrimaryKeyField, Q1.fldName, Q1.fldVal
, Count(Q2.PrimaryKeyField) as Rank
FROM qUnion as Q1 LEFT JOIN qUnion as Q2
ON Q1.PrimaryKeyField = Q2.PrimaryKeyField
AND Q1.ColNumber < Q2.ColNumber
GROUP BY Q1.PrimaryKeyField, Q1.fldName, Q1.fldVal

Now use that saved ranking query in a Crosstab query
TRANSFORM First(FldName & ":" & fldVal) as Result
SELECT PrimaryKeyField
FROM qRankedValues
GROUP BY PrimaryKeyField
PIVOT Rank

Good luck. With any fairly large set of data this is likely to be slow since
there will be no indexes available in the 2nd and 3rd query to help speed
things up.

As a matter of fact, it might be wise to use the UNION query to populate a
table with indexes on the fields to increase the speed up the process.


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

 

[DNuding]

This seems like a very difficult task. Is there a way to create a report
that would suppress fields that are null?

 

[John Spencer MVP]

Yes, but if you want them to align to the left and not leave gaps
horizontally, then you will be writing some fairly complex VBA code to check
--if you have nulls or not
--set controls visible property and move the controls with values to the
proper spot (along with their associated labels

OR
--set the value of controls and the associated label to match the associated
field.

If you can accept a vertical orientation, then you can use the can shrink
property of the controls to hide/show the fields depending on whether or not
the values are null or not.

You could do this by setting up the query to include the field name and value
in one calculated field -

SELECT PKField
, "FieldA:" + FieldA as FldA
, "FieldB:" + FieldB as FldB
, "FieldC:" + FieldC as FldC
FROM SomeTable

Or you could use the formula in the controls source on the report
= "FieldA:" + FieldA

Or you could write VBA code to show/hide any associated label for each field.

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