Re: Report with each field showing data from all records

Tech-Archive recommends: Fix windows errors by optimizing your registry


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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ADavis" <ri_tech@xxxxxxxxxxx> wrote in message
news:1168226440.414075.283770@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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

Allen Browne wrote:
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.

"ADavis" <ri_tech@xxxxxxxxxxx> wrote in message
news:1168219626.628384.232920@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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

.

Relevant Pages

  • Re: Truncating of memo fields
    ... Robin wrote: ... >The data is not being compared in the memo field although ... >the query in a list box. ... >to run this report with many records, ...
    (microsoft.public.access.reports)
  • RE: Text Box Not Large Enough
    ... Can you post the SQL of the query that is the RecordSource for your report? ... Are you seeing about 255 characters from the memo field? ... On a report that lists the Instructions for a particular PM on a machine ...
    (microsoft.public.access.reports)
  • Re: Report issue
    ... The query now selects fields from 2 tables joined by a filed. ... The resulting list which contains the caption fields and memo field forms the ... code so that the report with one record will print as seperate printout. ... Below it the memo field ReportsMemo.crreport. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • Re: Fields in Label dont give correct information
    ... You need to create a query based on tblRegEvent and tblRegPayment. ... -If your DCountis to count values in your report, ... half the time with very few errors (other than that label problem I had.) ... For example in the Room assignment on the report an idividual will be ...
    (microsoft.public.access.reports)