Forum Home
Press F1
Thread ID: 63983	2005-11-29 03:55:00	One for MS Access Gurus ...	marke (457)	Press F1

Post ID	Timestamp	Content	User
408552	2005-11-29 03:55:00	I suspect the answer to this one is simple ... but it appears I'm simpler! ;-) I have a main report with its recordset set to a query from a table selecting "ID". The main report has a number (about 30) sub reports in the report footer. These are all bound to queries in which the ID field from the table above is one of the fields. I was hoping to be able to link the master report to the subreports by linking on the ID field, so that when I filter the master report's recordset the subreports all filter as well. Instead, what happens is that when I link the subreports and main report on the ID field, the subreport shows only one record. Any suggestions? Chocolate fish are going begging! ;-) Help appreciated, Mark	marke (457)
408553	2005-11-29 06:12:00	Hi Mark and welcome to PressF1! First up, why so many subreports? In any case, check the design of your main report has allowed the subreports to grow. The way to get to this is to go into the design screen of the main report -> right click the border of the subreport -> Properties -> Format Tab -> Can Grow -> make sure this is set to Yes. I take it there is a one-to-many relationship between the data in the main report and the data in all of the subreports? And the relationships are set up correctly? Does every subreport have more than 1 record, or do some always have just the one record? If some always have one then you could incorporate those items into the query the main report is based on. Lastly, why are the subreports in the main report footer? Does it make any difference if you put the subreports into the detail section of the main report? There is an article from MS here (office.microsoft.com) but I'm not sure if that is much more help. If you are stuck, can you post a screen shot of the report? Search the chat section for a hosting images website so that you can post your screen shot in there. HTH, Andrew	andrew93 (249)
408554	2005-11-29 06:41:00	>Hi Mark and welcome to PressF1! Chuckles ... I used to be the CTO at IDG Communications ... and set up the original PressF1(s). This last iteration is much better though (so well done James). A roundabout way of saying I'm not new to PressF1 .. it's just been a while! ;-) Thanks for your reply! The scenario is this. The database this report comes from is to analyse the results of a survey. There are about 30 questions in the survey. What I've done is to build a subreport for each question ... so I can include them all into one report to give a master report for all the questions in the survey. Main data is in a results table with some m-2-m tables linked in where needed for multiple-choice type questions. What I was hoping to do is provide a report that runs across all records in the results table (tblAnalysis) - and that currently works perfectly as it stands. However I also wanted to be able to "filter" the set of records from the main table to allow users to select a subset (all those who answered "x" to question 5 for example). The theory was that I should be able to link the main report and child subreports by the ID field (since both are based either directly on the tblAnalysis table or queries which include the ID field from that table). That way if I filtered (using a form-generated SQL query with WHERE clause) the main report, the subreports would also filter. The subreports are in the report footer of the main report, 'cause I only want them to show once ... not for each record in the result table. They use sorting and grouping in the report to produce the cross-tab-style reports. Making sense so far? Anyway, what happens when I link the master report and the subreports on the ID field, is that the subreports only show results from a single record. Unlinked, they work perfectly! Stumped at present, so any help appreciated. Can screenshot if you think it will help - hopefully this more lengthy description will elucidate the problem a little better. Cheers Mark P.S It's nice to be back on Pressf1 again (and this time NOT be in charge of it!)	marke (457)
408555	2005-11-29 07:05:00	Hi Mark, sorry if Ive missed it in your explanation but have you set both the LinkChildFields and LinkMasterFields properties?	Parry (5696)
408556	2005-11-29 07:21:00	>Hi Mark and welcome to PressF1! Chuckles ... I used to be the CTO at IDG Communications ... and set up the original PressF1(s). This last iteration is much better though (so well done James). A roundabout way of saying I'm not new to PressF1 .. it's just been a while! ;-) hey mark, Its nice to have you back :) (Where you the one set-up the java version of pressf1? or another version back when this site first started?) P.S It's nice to be back on Pressf1 again (and this time NOT be in charge of it!) hehe :D its now all up to Bruce etc.. :)	stu161204 (123)
408557	2005-11-29 07:30:00	Hi Parry - >sorry if Ive missed it in your explanation but have you set both the LinkChildFields and LinkMasterFields properties? I probably wasn't clear actually, reading back . They are currently NOT set, and the report is working AOK for the set of _all_ records from the tblAnalysis table . However with those LinkChildFields and LinkMasterFields properties not set, I won't be able to filter the master report and have the child subreports filter appropriately . And when I DO set them, what I get is only a single record reported in the subreports . So setting thse fields to link on "ID" (present in both child and master) doesn't work, but unsetting them (to blank) does . . . it just won't allow me to filter that way . I think (if necessary) I could set the recordsources of the subforms in code, but it would be a major hassle having to build and set 30-odd SQL clauses with the WHERE clause I'm building up in the "Filter" form that I'm intending to use to set the recordsource property of the master form . Hope that clarifies?? Cheers (and thanks for the help and the welcome all!) Mark	marke (457)
408558	2005-11-29 07:32:00	I wrote the original one(s) .. there were two - way back when (on Domino), then project-managed the selection and installation of the java one. This new puppy is Jame's baby, and he did a mighty fine job getting it up and running. Much slicker than the java one - better feature set! Thanks for the welcome back! Cheers Mark P.S Don't say anything bad about Bruce - I've seen him in action and he's likely ban me! ;-)	marke (457)
408559	2005-11-29 07:41:00	Hi Mark Apologies for assuming you were new to PF! :blush: Thanks for the explanation . I think I better understand what you are trying to achieve, but it has resulted in more questions . I hate to ask this but what tables do you have? In particular, what tables and what fields within the tables? This might exaplin why you have 30 subreports . . . . . . because if it was correctly normalised you wouldn't have 30+ tables or need 30 or so queries to generate the report . As you may well know a database live and dies on it's data structures . . . . and battling with a database that isn't quite right is not only frustrating but also tease you to make you think you are almost there . . . . . Anyway, is this the sort of layout you seek? Report : Survey 1 Results: Question1 #A, #B, #C, #D, #E Question2 #A, #B, #C, #D, #E Question3 #A, #B, #C, #D, #E . . . Question30 #A, #B, #C, #D, #E Survey 1 Total : Sum(#A) . . . . Sum(#E) where #A = the number of responses to teh question that were ticked (or crossed) . Is this the sort of thing you want? Andrew	andrew93 (249)
408560	2005-11-29 08:02:00	Hey no apologies necessary! It just tickled me images/icons/icon7.gif. There's really only one main table - tblAnalysis. This consists of one record per survey response. However because some of the questions allowed for multiple responses (e.g .. without giving away too much about the survey which is confidential ... "What regions do you sell your products/services in?") I've ended up with 4 or 5 tables (plus join tables) to represent those many-to-many relationships. In general the format of the report needs to look like this (although there are some questions that are more complex than this): Question 1 OPTIONS,COUNT Option One, 20 Option Two, 25 Option Three, 15 TOTAL, 70 A more complex one might look like this: Question X OPTIONS, COUNT, COUNT WITH REVENUE, REVENUE TOT, AVERAGE Option One, 14, 10, 1000, 100 Option Two, 10, 5, 10000, 2000 Option Three, 20, 15, 30000, 2000 .... Does that give you some idea? I can't be too specific, 'cause the survey itself is very confidential. Cheers and thanks for your help/interest. Mark P.S The database is reasonably "semi-normalised". If there are m-2-m relationships, then there are tables to represent that. It's probably not to 4th Normal form, but it's not TOO bad either images/icons/icon12.gif	marke (457)
408561	2005-11-29 09:07:00	I now see why you have the subreports - there are differing layouts for various questions. So getting back to the original problem, the report works fine if you unlink the subreports but then you can't do your filtering. If you add the links, then you mentioned that the report only shows the one record. What do you mean by one record? Is this one record for each question? (i.e. subreport?) Like I mentioned earlier, I think the reason that might be happening is because the subreports are in the report footer (although I could be wrong). You mentioned that the subreports are in the footer because "I only want them to show once ... not for each record in the result table". Does this mean that if you move the subreports to the detail section, then they appear many times? If so, is this not an issue with either the sorting and grouping on the report or a case of using a totals query for your main report? Assuming you put the subreports into the detail section of the report (ignoring the duplicates at the moment, although there is a 'hide duplicate' property), can you set the links and get all records to appear? I don't think we can resolve this until we resolve the links / number of records appearing problem so if we focus on that part first, then we worry about the duplicate responses later. Andrew	andrew93 (249)
1 2