Link to home
Start Free TrialLog in
Avatar of Cecelia Rieb
Cecelia RiebFlag for United States of America

asked on

Access 2016 Subreport with Multiple Columns not Working

I have an Access report that I want to display 16 questions down the side, and the answers for various programs in multiple columns.  I created a report and embedded a subreport (against the same table as the main report) to display the data in multiple columns.  I have the subreport set to 2 columns, the Column Layout to Across then Down, and the subreport section in the main report is more than wide enough to handle the 2 columns of data.

The problem seems to come with the master/child fields.  Since the main report and the subreport have the same source table, I want the master/child relationships to be one-to-one.  In this case, that means two fields: Department and Program.  If I set the relationships this way, my subreport, which displays my desired 2 columns on its own, now only displays one column when viewing the main report in Print Preview mode.  If I change the master/child relationship to many-to many (i.e., on Department only, excluding Program), I then can see my 2 columns of data.  However, since it's a many-to-many relationship, instead of getting data for just the 9 programs, I get those same 9 programs repeated 9 times.

The fact that the many-to-many relationship shows me my multiple columns as desired tells me that the subreport itself is set up correctly, and that I've allowed enough width in the main report to accommodate two columns of data.  What is it about the one-to-one relationship that precludes me from seeing multiple columns?  I'm working on using a different source for the main report, which would make the master/child relationship one-to-many, but am running into the same problem.

Please help!
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

To me at least, ...your design is very confusing.

Perhaps it would be better if you posed a sample of your database and explained what you are after,

It may be that a different design would give you what you need...more easily...

JeffCoachman
Avatar of Cecelia Rieb

ASKER

Hi, thanks!  I've attached a scaled down and scrubbed version of my database below.  I've Googled up and down and sideways, so while I'm disappointed that my explanation was confusing, I'm not entirely surprised.

rptProgram_Fact_Sheet is my main report, I think the rest is pretty self-explanatory.  I truly appreciate any help you can provide.DummyDeptDB.accdb
Your schema is incorrect.  A simplistic solution is:
tblQuestions
QuestionID (autonumber, PK)
Text (Short Text)  -- Long Text if you might need more than 255 characters.

tblSurvey
SurveyID (autonumber PK)
Desc (Long Text)

tblSurveyQuestions
SurveyQuesID (autonumber PK)
SurveyID (FK to tblSurvey)
QuestionID (FK to tblQuestions)
SeqNum (integer)

tblPerson
PersonID (autonumber PK)
FirstName
LastName
etc.

tblPersonSurvey
PersonSurveyID (autonumber PK
SurveyID (FK to tblSurvey)
DateTaken (DateTime)
etc.

tblPersonAnswers
PersonSurveyID (FK to tblPersonSurvey)
SurveyQuesID (FK to tblSurveyQuestions)
Answer (Yes/No)

This basic schema will eliminate all the hardcoded DLookups().  Your survey can have an infinite number of questions.  The schema supports an infinite number of questions. And, the survey can be taken y an infinite number of people.  It also allows a single question to be used in  any number of surveys.
Are the DLOOKUPs impacting the results in the report?  At first glance, your simplistic solution seems more complex than what I have now.  How will restructuring help my report?

Thanks!
Cece
The problem with the DLookups() is that you need to create a new one for each question.  As your design is currently, there is no point to even having tblFactSheetQuestions.  You might just as well create labels on the unbound main form and fill them with the questions.  

As to the Fact Sheet, it violates first normal form.  Since all you have is test data, I can't tell how bad this is going to be  All I can say is that you've locked yourself into 16 questions and if you need more questions you will have to redesign the forms and queries in addition to the table.  This is symptomatic of a spreadsheet rather than a relational database.  With the design I suggested, you can have any number of questions.

Your design also limits you to a single survey.  Surveys are handy things.  Do you think it might be nice to be able to create an entire new one by simply adding a bunch of new questions?  As it stands, if you need a new survey or if you want to run this one more than once, you are SOL and will have to create a new database.

Yes, the design I proposed is more complex but not if you spend some time trying to understand what it will ultimately do for you and we haven't even gotten to doing analysis on the answers yet.  tblSurvey and tblSurvey are there so the application can support multiple surveys.  If you are 100% certain that you would never, ever need another survey for anything else, don't implement them but planning ahead and including them has the potential to make you a star.

Even with the fully normalized schema, it is possible to produce Excel-like reports.  You can use the crosstab wizard to create a query of the normalized schema that actually mimics Program Fact Sheet.

Having a non-normalized schema means that you would need to create separate queries to analyze each question's responses.  You can spend your time fighting with a bad design or you can learn about normalization and create a flexible design.  In fact, if you want to stick with what you've got, you would be far better off doing it in Excel.  Excel is a flat file rather than a relational database and since you have essentially restricted your Access app design to mimic only a SINGLE worksheet, you will be a lot more productive continuing on in Excel.

I "fixed" the report.  You MUST open it in PRINT preview, NOT report preview.  It is very unstable and crashed Access 3 times while I was trying to fix it.  There is a bug in the current version of Access that is causing a problem with subreports that have multiple columns. You cannot bind the main form to the same table as the subform.  That will multiply the data by the number of rows in the table.  I think you have 9 so you would get 1 main form page for each row in the table.  You can make a query and pull out just the Department (Select Distinct ....) and bind the main form to that if you want to.  I also removed the master/child links.  There is no reason for them.

This is a bad design and completely inflexible.  You will spend hours/days fighting with it.  Don't waste your time.  Spend your time trying to understand the normalized schema.
DummyDeptDB_Pat.accdb
SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I have complete control over how the data will look between Excel and importing into Access, so I can normalize it as needed prior to import.  I'm thinking the answer lies somewhere between your first response and your latest and will include advocating to management for a report that is different than they currently want.  Thanks for the info.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I don't have control over creating the survey, that was done before I was brought in on the project.  I do have control over the layout of the data when I write the code that pulls the data out of all 60 files.

This is a one-off project.  My intent is to grab the data in a more normalized fashion (not simply a straight import like my initial attempt) and go from there.

Thanks,
Cece
You're welcome.
Cecelia thanked me but forgot to close