Duplicate Report Data

I have a main report that is sorted and grouped by LastName, FirstName, MiddleInitial, and SSN.  The SSN is the primary key on the table.  I also have a subreport printing in the detail section of the main report, linked by SSN.  The problem I am having is that each person's set of data is printed 3 times.  How do I structure my report/subreport to print only one set of data for each person?

Also, if the subreport spans multiple pages, how do I print the person's name and SSN on all pages?

I don't have to worry about the name on the first page because the main report data is printing it.

Sheila
baileysAsked:
Who is Participating?
 
phil2freeConnect With a Mentor Commented:
To get the grouping to work in your report I think you may want to change your query to something like this one:

SELECT Students.*, Curriculums.Description AS CurrentCurriculum, Curriculums_1.Description AS CurriculumGoal
FROM (Students LEFT JOIN Curriculums ON Students.CurrentCurriculum = Curriculums.code) LEFT JOIN Curriculums AS Curriculums_1 ON Students.CurriculumGoal = Curriculums_1.code;

Will this query return the correct data for you?

Phil
0
 
DedushkaCommented:
So you have three level of grouping:
by SSN within By FirstName within LastName. You need only one level by LastName+FirstName+SSN
0
 
phil2freeCommented:
As dedushka said, I think you should only need one level of grouping.

If you want the name to print on all the pages you can set the Repeat Section property to true on the section that prints the name - this should be in the main report, probably on a group header.

Hope this helps

Phil
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
tomk120999Commented:
Hi, baileys, I'm supposing that you have textbox controls (fields) in the group headers that duplicate information shown in the detail section.

If that's so eliminate the fields on the report where they show as duplicates,  For instance, have nothing in the first three headers, and then put |FirstName| |LastName| |MiddleI| |SSN| in the SSN headder.  Then take all that out of the detail report section.  Yes, and also make the SSN header repeat as has been suggested.  The other suggestion will work as well.  This is just another option which might be easier to implement.  

Like the fellow said, "How you stand depends on where you sit."  :)
good luck as usual...
0
 
baileysAuthor Commented:
I don't have any group headers or footers.  I can't create them because I have two subqueries.

Dedushka, I tried to create one group level thru the view/Sort-Grouping menu (lastname+firstname+middleinit+ssn).  When I tried to run the report, I am prompted to enter a parameter.

Sheila
0
 
phil2freeCommented:
I'm confused, what are subqueries and I thought you had them grouped already?  What exactly is your datasource - can you paste SQL or explain it a little bit? Thanks.

Phil
0
 
baileysAuthor Commented:
My datasource is an Access query.  Two of the fields are selected from another table.  In my main table I have a code.  That code is used to select the decription that I want to appear on the report.

Here is my query:

SELECT Students.ssn, Students.firstname, Students.lastname, Students.middleinit, Students.street, Students.city, Students.state, Students.zipcode, Students.homephone, Students.workphone, Students.highschool, Students.College1, Students.College2, Students.College3, Students.entryterm, Students.entrytermyear, Students.testdate, Students.reading, Students.writing, Students.math_I, Students.math_II, Students.math_III, Students.bio, Students.chm, Students.placement, Students.eng01, Students.eng03, Students.eng04, Students.eng06, Students.mth01, Students.mth02, Students.mth03, Students.mth04, (select curriculums.description from curriculums where students.curriculumgoal=curriculums.code) AS curriculumgoal, (select curriculums.description from curriculums where students.currentcurriculum=curriculums.code) AS currentcurriculum
FROM Students, Curriculums;


0
 
DedushkaCommented:
You need not to concatenate this filds, just locate them on the same level
0
 
baileysAuthor Commented:
Dedushka, how do you put them on the same level?

Sheila
0
 
baileysAuthor Commented:
phil2free, your suggestion worked.  What was wrong with the way I had it?

Sheila
0
 
phil2freeCommented:
Not real sure, as you read before What is a subquery?  I've never really used queries in that way before.  I think I've learned something new here, thank you for that.

All I can think of is that the report has trouble with grouping a query with subqueries.  I used joins, which are tried and true.  The only part that is unusual is that you need the Curriculum table twice which is why there is the wonderful word "AS" in SQL.

How is the report looking now?

Happy to help,
Phil
0
 
baileysAuthor Commented:
phil2free, the report is looking good except that there is not a page beak at the end of the subreport.

Sheila
0
 
phil2freeCommented:
If the subreport is at the end of the section, then you can change that sections Force New Page property to After Section.

You can also drop a page break control on the report just after the subreport.  This will work no matter where it is.

Phil
0
 
tomk120999Commented:
baileys, thanks for the SQL, that seemed to help eveyone.  To All, subqueries can be quite useful at the right time.  To avoid dupes, using a GROUP BY clause in the subquery can be of use. (...GROUP BY Students.Students;?) :o)  Often, it is easier to let the report do that dirty work.  Looks like you folks are off to the races.  Good going!  See ya' later.

good luck as usual...

0
All Courses

From novice to tech pro — start learning today.