Report with each field showing data from all records

A

ADavis

Hi there,

I want to be able to create a report that will take each field (any
data type) and a place all the data from that field into a space just
beneath the field name on the report. I DON"T want a tabular report
with lots of columns. I could create a report using one field at a
time but I want each field's worth of data to follow in sequence. Man,
am I making any sense? An example:

Table
LName FName Notes
Armstrong Lance This is a very fast rider.
Ullrich Jan This one does drugs.
Bettini Paolo This one is world champion.

Report
LName
Armstrong
Ullrich
Bettini
FName
Lance
Jan
Paolo
Notes
This is a very fast rider.
This one does drugs.
This one is world champion.

Notice each field name is listed then ALL the data from the table or
underlying query for that field.

I also want to know how to reference a field's description in a report.
Can anyone help with that?

Thanks so much for the help.
ADavis
 
A

Allen Browne

How many fields?

If there's only a few as in your example, you could create a UNION query:

SELECT 'Notes' AS TheField, Notes AS TheValue FROM Table1
UNION ALL
SELECT 'LName' AS TheField, LName AS TheValue FROM Table1
UNION ALL
SELECT 'FName' AS TheField, FName AS TheValue FROM Table1;

That would let you create a report that groups on TheField and lists
TheValue.

If Notes is a Memo, I suggest you handle it first in the UNION query. That
way JET will treat the entire set of values as a Memo.
 
A

ADavis

Brilliant Allen!

I am a sometimes user and had not used such a query except in a test
years ago.

See this report:
http://www.foxcreekadventures.com/graphics/qSecondUnionTestQuery.pdf

These are survey results. Notice why we need to get all the field data
in one place. That memo field data needs compared to each other.

Now for the next question. We have many tables with this kind of data.
We will need to create many reports like this. Is there any way to
automate the creation of a report like this? Another question: Can we
take non-memo field data and have them all wrap into what amounts to a
large memo field? (Examples: AgencyNum or Director fields)

Thanks for your efforts here.

Cheers,
Andy Davis
 
A

Allen Browne

You could create this kind of query programamtically.

First step would be to loop through the Fields of the TableDef.
This example shows how:
http://allenbrowne.com/func-06.html

Doing that, create the SQL string that uses the actual fields from the
table, with a UNION ALL between each SELECT. Then assign the complete query
statement to the SQL property of your querydef:
CurrentDb.QueryDefs("Query1").SQL = strSql

Since the report is based on this query, and it still uses the same aliases
(we used TheField and TheValue), you can then run the report based on that
query.

If you want to combine the values from multiple records and combine them
into one memo field, that would need a different approach. Write a VBA
function to OpenRecordset, and loop through the records concatenating the
values of the records into the string. You probably want to write that into
another table.
 
Top