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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
phil2freeCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.