Solved

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

Posted on 2011-03-01
26
763 Views
Last Modified: 2012-05-11
Hi EE,

Hope all is well,

Background:
===========
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
     automated)

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.

                             -OR-

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

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

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,
sandra
       



2011-03-01-Columns.GIF
2011-03-01b-Columns.GIF
zEE-ARG-13-Student-Progress-fron.zip
0
Comment
Question by:mytfein
  • 19
  • 7
26 Comments
 

Author Comment

by:mytfein
Comment Utility
Hi,

I did some research on EE and found these links:


http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_10299271.html?sfQueryTermInfo=1+10+30+break+column


http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21704636.html?sfQueryTermInfo=1+10+30+break+column

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
0
 

Author Comment

by:mytfein
Comment Utility
HI,

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
0
 

Author Comment

by:mytfein
Comment Utility
Hi,

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
0
 

Author Comment

by:mytfein
Comment Utility
Hi,

So I'm working on (h)

RPT_020_Transcript now has 2 sub reports
   RPT_020_Transcript_subCol1
   RPT_020_Transcript_subCol2

Below is a screen shot


Tx, s
2011-03-01d-Columns.GIF
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 total points
Comment Utility
...You seem to be the only one posting to your own question...
;-)

Were do we stand as of now with this?
0
 

Author Comment

by:mytfein
Comment Utility
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:
      Credits
      Points

    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
         
0
 

Author Comment

by:mytfein
Comment Utility
oh, left this out:

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

tx, s
0
 

Author Comment

by:mytfein
Comment Utility
Hi,

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
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 total points
Comment Utility
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...
0
 

Author Comment

by:mytfein
Comment Utility
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
0
 

Author Comment

by:mytfein
Comment Utility
Hi Jeff,

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

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
zEE-b-ARG-13-Student-Progress-fr.zip
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 total points
Comment Utility
1. You have totally confused me.
What is the one question that needs to be addressed here?
0
 

Author Comment

by:mytfein
Comment Utility
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
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 total points
Comment Utility
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...

0
 

Author Comment

by:mytfein
Comment Utility
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...


2011-03-02-Columns.GIF
0
 

Author Comment

by:mytfein
Comment Utility

I had to rebuild my test table , here is the report with the right side of header section of fixed fields
showing
0
 

Author Comment

by:mytfein
Comment Utility
0
 

Author Comment

by:mytfein
Comment Utility
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
2011-03-02c-Columns.GIF
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
Comment Utility
I think we are having a communications issue here...

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

Author Comment

by:mytfein
Comment Utility
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
0
 

Author Comment

by:mytfein
Comment Utility
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

2011-03-02d-Columns.GIF
0
 

Author Comment

by:mytfein
Comment Utility
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
                   credits
                   points

           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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Ummmm.

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.

;-)

JeffCoachman
0
 

Author Comment

by:mytfein
Comment Utility
ummm,

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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
...next book:

Inner game of Microsoft Access...?

;-)

JeffCoachman
0
 

Author Comment

by:mytfein
Comment Utility
tx.. that's a good one....

0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now