The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.
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!
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!
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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.
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
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.
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
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
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
I understand the problem. I would suggest normalizing Program Fact Sheet after you import all the data. You'll need 1 make table query and 16 append queries. The normalized table would look like:
tblAnswers
Department (PK field 1)
ProgramName (PK field 2)
ProgramDesc
QuestionNum (numeric)
Answer (Yes/No)
Create a make table query that makes the the above table. You will have to hard code QuestionNum. The first one will be 1.
The 16 append queries are essentially the same as the make table but each append query selects a diffferent question and hard codes a different questionNum.
Once the data is normalized, you can use a crosstab query (use the wizard) to display question numbers across the top and departments down - makes more sense that way since you have only 16 questions which will easily fit across and 60 departments down.
If you have to do departments across, make the crosstab that way but export the result set to Excel. You won't be able to create an Access report with 60 columns. Access doesn't have the ability to fine tune the layout or fit to page.
Although, you would have less work to do if your template had the questions vertical so question 1 is on row 1 and 2 is on row 2. The spreadsheet needs only two columns. Question and Answer and the Department should be in the file name. If you need to capture the Dept, name , and desc, use 5 columns and 16 rows as the spreadsheet. Then you won't have to normalize. You can just import them directly.
tblAnswers
Department (PK field 1)
ProgramName (PK field 2)
ProgramDesc
QuestionNum (numeric)
Answer (Yes/No)
Create a make table query that makes the the above table. You will have to hard code QuestionNum. The first one will be 1.
The 16 append queries are essentially the same as the make table but each append query selects a diffferent question and hard codes a different questionNum.
Once the data is normalized, you can use a crosstab query (use the wizard) to display question numbers across the top and departments down - makes more sense that way since you have only 16 questions which will easily fit across and 60 departments down.
If you have to do departments across, make the crosstab that way but export the result set to Excel. You won't be able to create an Access report with 60 columns. Access doesn't have the ability to fine tune the layout or fit to page.
Although, you would have less work to do if your template had the questions vertical so question 1 is on row 1 and 2 is on row 2. The spreadsheet needs only two columns. Question and Answer and the Department should be in the file name. If you need to capture the Dept, name , and desc, use 5 columns and 16 rows as the spreadsheet. Then you won't have to normalize. You can just import them directly.
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.
If you have control over creating the survey then go with doing it the right way. You can export spreadsheets for the users to fill in or if you are all on the same network, they can use the Access app to fill in the answers and then you won't have to consolidate the data.
If this is a one-off project and you want to spend the minimum amount of time and not worry about ever having to do it again, just normalize the spreadsheet to make the data easier to import and don't worry about the flexibility to create multiple surveys.
If this is a one-off project and you want to spend the minimum amount of time and not worry about ever having to do it again, just normalize the spreadsheet to make the data easier to import and don't worry about the flexibility to create multiple surveys.
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
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
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
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.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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