Access 2003: Report - Working with Columns/forcing column break

Hi EE,

Hope all is well,

1) My assignment is to create a student progress report.
(They already have one, created with a 3rd party tool that pulls directly from their Oracle prodcution database. This tool is not able to include the student's photo, so have been asked to create in Access)

2) The data behind this report is a query.
     The query pulls the entire table: TBL_transcript, and just sorts it.

     I could have pulled directly from the table but it does not have a primary key so is unsorted,
     so the query sorts the data.

     The reason TBL_transcript does not have a primary key, is bec i have code that
     dynamically builds the table. (i guess i could write EE for vba code how to assign a primary
     key programatically, but i digress)

     The data is pulled from an Oracle data warehouse. (i execute the oracle query using a .bat
     file that exports to excel, and then import into access.... this is all done with vba so it's

3) The data is only for one student (when i execute the .bat file, will send the student's id
as a parm)

4) The report groups by semester:
       for ex:   2008 Fall
                     2009 Spring

                      2009 Fall
                      2010 Spring

                      2010 Fall
                      2011 Spring

 Each semester can have 4 -6 courses under it.
 2 semesters make up an academic year:
                2008 Fall and 2009 Spring = 2009 Academic Year

 Which is why created 2 group bys in the report:
               Academic Year Description
               Academic Period Description

My issue/challenge with the report:
a) report will have a header with lots of students "fixed fields of info"
b) the body of the report will list student's course history (many rows)

c) need the course info to present as 2 columns

d) Below, have uploaded:
   1) test mdb
   2) 2 screen shots

e) i set up the report for 2 columns. The data is flowing vertically down.
  HOWEVER, am not "spilling into" the second column bec. do not have enough data to go to column 2.

f) would like to group by academic year description and academic period description

g) would like to have 3 groups vertically to the left.
   and 3 groups vertically to the right, with a verical line separator.


h) since 2 semesters make up a year, present the semesters horizontally like this

         2009 Fall                                                                   2010 Spring
                course1                                                                          course1
                course2                                                                          course2
                course3                                                                          course3
                course4                                                                          course4

Please see SCREEN SHOT 1  (in mdb: rpt_015_Transcript)

h) Is there a way to force the data to go into column 2?
    i had a similar problem in the past, and an EE expert i modifed the footer to be larger
    i tried that .... the report is close but it has issues, such as
             the group by fields are not printing along with the date (i circled in red the issues)

Please see SCREEN SHOT 2 (in mdb: rpt_015b_Transcript)

tx for your help and ideas,

Who is Participating?
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
I think we are having a communications issue here...

I don't understand what your image is supposed to be telling me...?
mytfeinAuthor Commented:

I did some research on EE and found these links:

Maybe i should:
a) assign the records a code, and create 2 sub reports, 1 for each column and filter by code

pls advise, tx, s
mytfeinAuthor Commented:

I added this field to the query to serve as a column controller:

ColumnCode: IIf(Right([Academic Period Description],4)="Fall","1","2")

Fall = 1   Spring = 2

      then i could go horizontally across

            subreport1 would filter for 1
            subreport1 would filter for  2

tx, s
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

mytfeinAuthor Commented:

I created a Report, put in the Filter ON:

ColumnCode = "1"

It works to show the Falls going vericallly

now i have to create a report for column 2 for Spring
mytfeinAuthor Commented:

So I'm working on (h)

RPT_020_Transcript now has 2 sub reports

Below is a screen shot

Tx, s
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
...You seem to be the only one posting to your own question...

Were do we stand as of now with this?
mytfeinAuthor Commented:
It's true...

a) in case my user does not like the horizontal approach, i also wanted to learn how to
print the columns vertically....

b) i suppose i could post a new question about vertically...

c) back to (a), i have 2 more fields on BOTH reports that i'd like to display:

    they are in the report if you click on design, but are not showing, dont know why?
    i made the font and size smaller, and the fields don't show - they do have data

 d) do the border of a subreport (that grey area) take up space on the report?
 e) what do you think of the approach in general?
      i may have to pre-process to add rows to balance both sides
            for ex: if fall only took 4 classes     and spring took 5 classes
                        i would have to generate a 5th class row that shows up blank on the report
                                         on the fall side, for the report to balance with the spring side
f)  do you know what:  running sum on a data tab for a text field means?
          can i use that property to easily total another field?

i would be glad to open new related posts on any of the above.
if you can answer any of the above points, esp (e), would be glad to close the question.

tx, s
mytfeinAuthor Commented:
oh, left this out:

hi boag200, tx very much for writing....

tx, s
mytfeinAuthor Commented:

Have another question,

for this student there are 24 rows of course info.

(you will wonder why everything is lumped in one table and does not look normalized. Well, that's
bec. am getting the data from a data warehouse, that used de-normalized data. Also, i used an
Oracle tool, that does the joins to other tables, so you are looking at the result of an Oracle query)

when i run the report, the report seems to run 24 times, bec. there are 24 pages.

Can you explain how that happens, and how i can only print 1 page without having to do a
group by student id, bec that would take up real estate on the report, and am already running
out of room.

tx, s
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
OK... Please slow down, ...I am still trying to catch up.

<Have another question,...>
Not yet you don't
Let's please focus on the first question before we complicate things by adding yet another question.

I am leaving work now.

I will take a closer look at this when I get home tonight...
mytfeinAuthor Commented:
Hi Jeff,

chuckle.... tx for your offer to have a look....

i would take any answer to close out the question, and be glad to open smaller questions...
     i just wanted you to get an idea of where i was coming from and where would like to head to...

i have not worked with Access reports in a while, and sub report in particular....

(i have done an unbound report, where i popped all the fields in one gigantic header section,
     but this report has more data, and doing it unbound would take a very long time, to map and
      code everything, so really want a bound report idea to work)

sadly, our new laptop at home is acting up, and am not able to log to the internet at home for now

need to bring it in for service.... tx again, s
mytfeinAuthor Commented:
Hi Jeff,

I posted a latest version of mdb and also a graphic file for "Missing Photo" in the zipped file

Here, the report's header you will see the fixed fields....

It's weird that the fields on the right, and when i moved the picture to the right...
THEY DO NOT display....

i checked visible=yes, and the margins...

don't know why this is happening....

tx again for your time, s
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
1. You have totally confused me.
What is the one question that needs to be addressed here?
mytfeinAuthor Commented:
Hi Jeff,

a) the report is has 24 pages (i have 24 rows in the table)
i just want 1 page, where the 24 rows are showing either in subreport1  or subreport2
depending on the ColumnCode

b) in the header part of the report i have fields on the right that are not showing,
although visible is yes.

tx, s
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
1. Please compact your database before posting it.
It is 54MB as downloaded, when compacted it is 6MB.

2. <i just want 1 page, where the 24 rows are showing either in subreport1  or subreport2
depending on the ColumnCode>
Still confusing, ...can you post a graphic of the *Exact* output you are expecting?

3. <b) in the header part of the report i have fields on the right that are not showing,...>
This is really a different issue.
Let's work through the main issue here, the go from there...

mytfeinAuthor Commented:
Hi Jeff,

I just figured out the fields that are not showing...

i cloned this report from a test report that i created using Access to create columns
via PageSetup/Columns..... it was set to 1 column of 4 inches
so i went to an old report and set it to 1 column of 9 inches

and i see the fields now....

below is a screen shot...

mytfeinAuthor Commented:

I had to rebuild my test table , here is the report with the right side of header section of fixed fields
mytfeinAuthor Commented:
mytfeinAuthor Commented:
Hi Jeff,

so solved (b) by myself as shown in above screen shots

with regard to (a), posted below a zoomed version of report in preview

report generated 24 pages of the same page

i think it's bec i have 24 rows,  each row representing a course with misc. joined info

how can i get access to show just 1 page, and not the extra iterations.....

tx, s
mytfeinAuthor Commented:
Hi Jeff,

It's great that you are asking me questions, bec. you are forcing me to think.... and i think i just
realized the reason for my problem and i think i just realized how to solve it!

I will give you the background and then what i think needs to be done.....

TBL_Transcript has 24 rows all for -one- student.

Each row represents a course that he took, start date, end date, grade + misc info such as his
biographical info.

The last image show page 1 of 24, that means when i read the report it generated 24 pages.
I am looking to print just 1 page.

Now, The main report has 2 sub reports to fake a columnar report.
        So the Fall courses are tagged with a columncode of 1, so they print in subreport 1
        So the Spring courses are tagged with a columncode of 2, so they print in subreport2

        They work kind of like a continuos form, where one course record after another shows

I just realized that the recordsource behind the the main report is reading the whole table, all 24 rows,
which is why 24 pages are generated.

For each row, the fixed fields get populated in the header section.

So i think, i have to create a new query as the record source that does a DISTINCT to select
only the fixed fields to include in the header section....

will get back to you, tx, s
mytfeinAuthor Commented:
Hi Jeff,

tx for making me think... yes that worked,  SELECT DISTINCT as in the code window below to create
a new query:                   qp_010_SortByAcademicPeriod_Parent

and use the first query:  qp_005_SortByAcademicPeriod
                                        for the sub reports

Since qp_010_SortByAcademicPeriod_Parent    uses DISTINCT, it returns one record
so now have a one page report for the one student

Pls see screen shot below that just shows one page, tx, s
SELECT DISTINCT TBL_Transcript.Name, TBL_Transcript.Id, TBL_Transcript.College, TBL_Transcript.Program, TBL_Transcript.[Student Level], TBL_Transcript.[Course Level], TBL_Transcript.[Student Status], TBL_Transcript.[Registration Status], TBL_Transcript.[Birth Date], TBL_Transcript.[Primary Ethnicity Category Description], TBL_Transcript.[Email Address], TBL_Transcript.[Phone Number Combined], TBL_Transcript.[Street Line 1], TBL_Transcript.City, TBL_Transcript.[State Province], TBL_Transcript.[Postal Code], TBL_Transcript.[Nation Description], TBL_Transcript.Calc_AcademicStanding, TBL_Transcript.[Expected Graduation Date], TBL_Transcript.[College Description], TBL_Transcript.[Program Description], TBL_Transcript.[Major Description], TBL_Transcript.[Campus Description], TBL_Transcript.Calc_MCAT_Bio_Score, TBL_Transcript.Calc_MCAT_Phys_Score, TBL_Transcript.Calc_MCAT_Verbal_Score, TBL_Transcript.Calc_MCAT_Writing_Score, TBL_Transcript.[Course Level Description], TBL_Transcript.[Student Population Description], TBL_Transcript.[Student Population Description], TBL_Transcript.[Post Secondary School Description  1], TBL_Transcript.[Post Secondary Degree  1], TBL_Transcript.[Post Secondary Attend From Date  1], TBL_Transcript.[Post Secondary Attend To Date  1], TBL_Transcript.[Post Secondary School Description  2], TBL_Transcript.[Post Secondary Degree  2], TBL_Transcript.[Post Secondary Attend From Date  2], TBL_Transcript.[Post Secondary Attend To Date  2]
FROM TBL_Transcript;

Open in new window

mytfeinAuthor Commented:
Hi EE Public,

The journey of this EE post is unusual, in that i helped myself alot.

You see, the journey began by searching EE for column breaks, and found a solution at EE  to get
me started.  I posted this question originally to learn how to control columns with the data traveling
vertically, and/or the data traveling horizontally.

This post chose the vertical path, by using a trick. Instead of asking Access to control the columns,
created 2 subreports and tagged the data as to which column sub report they would display.

subreport1  displays data with a columncode of 1
subreport2  displays data with a columncode of 2

The columnCode is being assigned in qp_005_SortByAcademicPeriod
            fall       records get a collumncode of 1
            spring  records get a collumncode of  2

The challenges that remained in this post was:
     a) The body/parent report was not showing the fields that i put on the right
          i realized bec. i cloned it from a diff. columnar report, and the column size was set to 4 "
          changed it to 9 ", and fields now show

     b)  i had the same problem in the subREport that 2 fields were not showing

           enlarged the column size

       so EE public, beware of cloning a columnar report, better to clone a simple report to save time...

     c) This table has 24 records for 1 student.
          Wanted only a 1 page transcript report for the student.

          Access was generating 24 pages.

          Realized that needed a diff. record source for the parent report, that would do a SELECT DISTINCT on the fixed fields that wanted to display in the header section, to show one row

         Thus, a 1 page report printed.

If have any questions, will open a related post....

Thx Jeff for being my sounding board, if you would not have questioned me and forced me to think
and write clearly, would not have solved these issues by myself.....

I feel that although i did alot of the problem solving, it's Jeff's credit, and i feel this is a useful post
for someone else in the same situation....

tx again, s
Jeffrey CoachmanMIS LiasonCommented:

OK, but you actually solved your own issue here.

This means that you deserve the points not me.

If my "questions" helped you find the solution, ...great, but you did all the work, ...not me.

Selecting your own post (instead of mine) will actually help other users searching here for the same issue.

Please request that this be changed.


mytfeinAuthor Commented:

could not have done it without you prodding and clarifying..  i would have a writers block/been stuck
and in my own way

you were truly a "Coach - man"   (pls see the books:  Inner game of Tennis, Inner game of work

tx, s
Jeffrey CoachmanMIS LiasonCommented: book:

Inner game of Microsoft Access...?


mytfeinAuthor Commented:
tx.. that's a good one....

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.

All Courses

From novice to tech pro — start learning today.