There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
Solved
format access report so that records will run horizontally instead of vertically
Posted on 2010-09-13
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
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
4-
4
- +2
11 Comments
No. You'll have to create a temp table with one long text field and then code to populate.
Sorry.
Scott C
Sorry.
Scott C
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
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
Active today
No need for a temp table, just some code in the report. Have you written any code before?
JimD.
JimD.
Active today
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.
JimD.
Active today
Open report from example
dbtest.zip
dbtest.zip
Active today
<<I have copied and tweaked code. Could anyone give me a code example?>>
Sure, I just wasn't quite sure where to start. First thing we need to get right though is the datasource for the report. You are going to want one record per child/ancestor pair. Do that with a query ( I think your already there unless what you posted above was done with a subreport).
As far as the logic, there are a few ways to do this and I'm going to show you the one that uses the least amount of code possible.
0. Add a string variable, strChildrensNames to the reports declaration section.
1. Add a report group on the ancestorID and indicate that you want a header and a footer.
2. Set the ancestor group header's visible property to false
3. Add the following code to the OnFormat event of the header:
strChildrensNames = ""
4. Set the detail's section visible property to false
5. Add two controls for the childs name (first and last) to the detail section. These will be the only two controls in that section. Call them txtChildsFirstName and txtChildsLastName (if you already have controls there, select them and move then down to the report footer).
6. Add the following code to the Detail section's OnFormat event:
If FormatCount = 1 Then
strChildrensNames = strChildrensNames & ", " & Me.txtChildsLastName & ", " & Me.txtChildsFirstName
End If
7. Make sure the group footers visible property is True
8. Place all the controls you need here for the ancestor info.
9. Add one un-bound control to hold the childern's names were collecting in the OnFormat event in the detail section. Call it txtChildrensNames
10. Add the following code to the OnFormat event of the group footer:
Me.txtChildrensNames = Mid$(strChildrensNames, 3)
All right...that's it. Here's what is happening:
Were getting one record per child/ancestor pair and we've set the grouping on the ancestor, with the header for that group and the detail section set so they won't print. What we'll get then is one line on the report (the group footer) for each ancestor.
But even though the group header and detail section are not visible, any code we have there still get's done. The report engine will do group header when the ancestor changes and the detail section for each record in the recordsource.
In the instructions above, the code clears the variable were using to hold the names, and the detail section code collects each childs name and saves it in strChildrensNames.
When the report engine hits the last child record for an ancestor, it will do the report footer. It's here that you print the ancestor info required and where we take the children's names we've collected and place them in a control in the group footer.
The Mid$() function is stripping out the comma and space that appears at the start of the names.
There are other methods that you can do to do this (utilizing MoveLayout, NextRecord, and PrintSection properties), but that is more complex and overkill for what you're trying to do here.
Give it a swing and if you get stuck, let me know and I can whip up a sample.
JimD.
Sure, I just wasn't quite sure where to start. First thing we need to get right though is the datasource for the report. You are going to want one record per child/ancestor pair. Do that with a query ( I think your already there unless what you posted above was done with a subreport).
As far as the logic, there are a few ways to do this and I'm going to show you the one that uses the least amount of code possible.
0. Add a string variable, strChildrensNames to the reports declaration section.
1. Add a report group on the ancestorID and indicate that you want a header and a footer.
2. Set the ancestor group header's visible property to false
3. Add the following code to the OnFormat event of the header:
strChildrensNames = ""
4. Set the detail's section visible property to false
5. Add two controls for the childs name (first and last) to the detail section. These will be the only two controls in that section. Call them txtChildsFirstName and txtChildsLastName (if you already have controls there, select them and move then down to the report footer).
6. Add the following code to the Detail section's OnFormat event:
If FormatCount = 1 Then
strChildrensNames = strChildrensNames & ", " & Me.txtChildsLastName & ", " & Me.txtChildsFirstName
End If
7. Make sure the group footers visible property is True
8. Place all the controls you need here for the ancestor info.
9. Add one un-bound control to hold the childern's names were collecting in the OnFormat event in the detail section. Call it txtChildrensNames
10. Add the following code to the OnFormat event of the group footer:
Me.txtChildrensNames = Mid$(strChildrensNames, 3)
All right...that's it. Here's what is happening:
Were getting one record per child/ancestor pair and we've set the grouping on the ancestor, with the header for that group and the detail section set so they won't print. What we'll get then is one line on the report (the group footer) for each ancestor.
But even though the group header and detail section are not visible, any code we have there still get's done. The report engine will do group header when the ancestor changes and the detail section for each record in the recordsource.
In the instructions above, the code clears the variable were using to hold the names, and the detail section code collects each childs name and saves it in strChildrensNames.
When the report engine hits the last child record for an ancestor, it will do the report footer. It's here that you print the ancestor info required and where we take the children's names we've collected and place them in a control in the group footer.
The Mid$() function is stripping out the comma and space that appears at the start of the names.
There are other methods that you can do to do this (utilizing MoveLayout, NextRecord, and PrintSection properties), but that is more complex and overkill for what you're trying to do here.
Give it a swing and if you get stuck, let me know and I can whip up a sample.
JimD.
Active today
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.
Jim,
Could you take a look ath this? For some reason my string variable isn't picking up the names
ForJim2.accdb
Could you take a look ath this? For some reason my string variable isn't picking up the names
ForJim2.accdb
Active today
<<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.
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.
Featured Post
There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple. The final solution for this task involved creating a custom text parser and then reading…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other.
As you build tables in Microsoft Ac…
Suggested Courses
Course of the Month5 days, 9 hours left to enroll
579 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.