asked on

format access report so that records will run horizontally instead of vertically

Is there any way to format access report so that records will run horizontally instead of vertically? I am putting together a report which will appear in a book. Each ancestor has multiple children. The query behind the report looks like this:
ANCESTOR NUMBER      FIRST NAME      SURNAME      CHILD NAME
1034      David      Allen      Robert
1034      David      Allen      John
1034      David      Allen      Thomas
1034      David      Allen      David
1034      David      Allen      James Joseph
1034      David      Allen      Mary
1034      David      Allen      Benjamin
1098      Richard      Aycock      Edward
1098      Richard      Aycock      Burwell
1098      Richard      Aycock      Henry
1098      Richard      Aycock      Joel
1098      Richard      Aycock      Rebecca
1098      Richard      Aycock      Richard, Jr.
1098      Richard      Aycock      Winifred "Polly"
1144      Roger      Barton      Hugh
1144      Roger      Barton      Clark Talbot
1144      Roger      Barton      Gilbert
1144      Roger      Barton      Armstead

They want the report to look like this in the book but showing all the children instead of just one:
ANCESTOR NUMBER      List
7100      Abernathy, John Jr, (PS VA) (c1743 - c1824) Spouses: m Molly ; m c1762/65 Mary; Children: Nnancy b c1763
7101      Abernathy, John Sr, (PS VA) (1723 - 1812) Spouses: m 1744 Lucy; Children: Frederick b 1745
7102      Abney, Paul, (Pvt VA) (1755-60 - 1815) Spouses: m 1785 Rhoda; Children: Thomas b 1785
7103      Adams, Drury, (Pvt SC) (c1755 - 1814) Spouses: m Sarah; m Elizabeth; Children: Mary
7104      Adamson, Basil, (Pvt/PS MD) (1728 - 1785) Spouses: m 1747 Nancy; Children: John

I need a way to get the children records in the report to run across the page instead of the default vertical orientation.
If this is impossible, then is there a way to format a query to help make this possible.
Thanks

clarkscott

No. You'll have to create a temp table with one long text field and then code to populate.
Sorry.

Scott C

ksilvoso

ASKER

For further clarification, this is how the report looks now:

Monday, September 13, 2010      0.362708333
ANCESTOR NUMBER      FIRST NAME      SURNAME      CHILD NAME
1034      David      Allen
                  John
                  Thomas
                  David
                  James Joseph
                  Mary
                  Benjamin
                  Robert
1098      Richard      Aycock
                  Joel
                  Edward
                  Henry
                  Rebecca
                  Richard, Jr.
                  Winifred "Polly"
                  Burwell
1144      Roger      Barton
                  Elizabeth
                  Hugh
                  Clark Talbot
                  Gilbert
                  Armstead
                  Hanna

Jim Dettman (EE MVE)

No need for a temp table, just some code in the report. Have you written any code before?
JimD.

Jim Dettman (EE MVE)

BTW, I should have added that without resorting to code in some way, you can't do what you want with the built-in tools. A cross tab query comes closest to what you need, but even that doesn't quite fit the bill.
JimD.

ksilvoso

ASKER

I have copied and tweaked code. Could anyone give me a code example?

als315

Open report from example
dbtest.zip

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

You might also be able to address this with a subform for the children. You can define your subform for multiple columns that run horizontally, then wrap to the next line.

ksilvoso

ASKER

Jim,
Could you take a look ath this? For some reason my string variable isn't picking up the names
ForJim2.accdb

Jim Dettman (EE MVE)

<<Could you take a look ath this? For some reason my string variable isn't picking up the names>>
You did perfect! And the answer is simple as to why it is not working; use Print Preview and not Report View. In Report View, no code is executed (have no idea of what they were thinking of when they did this).
I would make a couple of small changes however. In your table, don't name the fields starting with the 'txt' prefix. The whole idea of that prefix is to indicate a text control. By naming a control that way in a form or report and not doing that for fields, it's very easy to tell in code if your dealing with a control or a field (and there are times you want to know that). Access also doesn't have to guess what your referring to because the names are different, so it will be faster.
There are a whole bunch of standardized prefixes that you can use. One you might want to look at is:
http://www.mvps.org/access/general/gen0012.htm
which has been around for ages. There is nothing saying you can't use your own pre-fixes if you want. The important thing is that your consistent in naming everything. This makes it easier when you go back to the app after a time and want to make changes.
Also, your names look a bit funny in the report, but that's because you have so many nulls in for the last name. When you plug in the values, it will look fine (there are comma's in to seperate the names, as well as the first and last names, which you might want to change).
One last thing; how I trouble shooted this was to place a STOP in the code at the start of each procedure. This is how I discovered that the procedures were not executing. Took me a minute to figure out what was going on (I don't use 2007).
The other thing that lets you do is actually step through the code line by line by pressing F8. You can also hover the mouse over a variable and see it's value. You can continue code execution by pressing F5.
There are other things you can do when stepping through the code, but I'll stop there.
JimD.

ksilvoso

ASKER

Thanks so much for the time you put into helping me with this and for solving my problem for me!!