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
408562	2005-11-29 09:12: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! Ah, that Wayyyyy before I came to PressF1, I was only here when they where using the Java version..... I was one of the people who keep reporting the forum crashers :D :p P.S Don't say anything bad about Bruce - I've seen him in action and he's likely ban me! ;-) hehe :D	stu161204 (123)
408563	2005-11-29 09:24:00	I now see why you have the subreports - there are differing layouts for various questions. Yep - Spot on. Does this mean that if you move the subreports to the detail section, then they appear many times? Yes - each subreport appears once for each record in the tblAnalysis table (which is the one bound to the master report). I.E the subreports all appear 400 times (there are 400 records in tblAnalysis). Furthermore, I tried moving the subreports into the detail section to confirm this, and tried setting the LinkChildFields and LinkMasterFields to "ID" (which is the common field between the master report and subreports. The subreports STILL come up with only on record - as they do when they're in the report footer of the main report. So no difference there. What I mean by this, is that it is evident that the subreport is reporting only on a single record, because I get something like the following in the subreport when the subreport and master-report are linked: OPTIONS, COUNT Option 1, 1 Option 2, 0 Option 3, 0 Option 4, 0 TOTAL, 1 Whereas when I unlink them I get something like: OPTIONS, COUNT Option 1, 40 Option 2, 60 Option 3, 250 Option 4, 150 TOTAL, 400 It's the same for any subreports that I link. Any that I don't link continue to be OK (ignoring the duplicates at the moment, although there is a 'hide duplicate' property) Interesting - I couldn't see one. Where does it hide? images/icons/icon10.gif Cheers Mark	marke (457)
408564	2005-11-29 09:31:00	If you right click a field on your report and get into the properties, then 'Hide Duplicates' should be about the 4th item down in the Format Tab. Are the queries supporting the subreports totals queries? And is the query supporting the main report also a totals query? A	andrew93 (249)
408565	2005-11-29 09:42:00	Hi Andrew, The queries supporting the subreports are just queries. They're not "totals" queries .. I'm using sorting and grouping in the subreport to handle the totalling. So I am using a straight query for the subreport (and have the ID field from the tblAnalysis table as one of the fields in the recordset). For example, it might be something like "SELECT ID, q2 FROM tblAnalysis ORDER by q2;" ... something like that anyway. Sometimes they might have expressions in them - for example "IIF(Revenue>0,1,0) As CountRev" for example (so that my resultset has a 1 in it for every record that has a revenue value .. then I can use Sum([CountRev]) in my report to get a count of all records with revenue > 0. That sort of thing. But mostly pretty straightforward on the whole. The main report's query is just "SELECT ID from tblAnalysis;" so it's not a total query either. It was "SELECT * FROM tblAnalysis" but I thought I'd reduce it to just the ID field to be less "wasteful"! ;-). Not that it matters since the tblAnalysis table only has 400 records in it! Does that help? Cheers Mark	marke (457)
408566	2005-11-29 09:46:00	I haven't tested this but I would think that if you used totals queries for both the main report and the subreports (group by ID etc) then your duplicate record problem *should* disappear. I would assume that all you want on the report is summaries anyway, and totals queries are the way to go (they are better at doing this than relying on the grouping and sorting in the report). If you go into the query design screen, click View -> Totals and you should see a number of new options for grouping, adding counting, min, max etc. Try it and see if it makes a difference with the linked subreports. HTH, Andrew	andrew93 (249)
408567	2005-11-29 10:17:00	Unfortunately there's a reason why I don't think that won't work ... Here's an example: Say Q1 has 3 possible options "Is your company under 5 employees, 5-19 employees, or 20+ employees"), so its subreport has the following: SQL query: "SELECT ID, Q1 FROM tblAnalysis;" (returns 400 rows) Q1 OPTIONS, COUNT < 5 Employees, 10 5-19 Employees, 90 20+ Employees, 300 Total, 400 Q2 on the other hand was a multiple option question ... (EG which of the following types of products do you sell, tick all that apply) SQL query: "SELECT tblProduct.Description, tblProductJoin.AnalysisID FROM tblProduct JOIN tblProductJoin ON tblProduct.ID = tblProductJoin.ProductID;" (Returns 1054 rows) Q2 OPTIONS, COUNT Gadgets, 150 Widgets, 250 Watsits, 300 Whammos, 354 So I can't use the same query for both questions ... 'cause returning 1054 rows would screw up the counts for Q1. Or am I misunderstanding you? What are you suggesting the main report query should look like? And the subreport (as per my second example above)? I'm not sure how grouping by ID would help? Wouldn't you then STILL get a record for every record in the table ... since the ID is unique? Sorry, I'm confused ... Cheers Mark	marke (457)
408568	2005-11-29 18:41:00	Ok. Now I think I understand. (Boy this is hard without seeing the database). The penny dropped when you mentioned the unique ID in the main table. You have a unique ID for each survey question response. This is why it works when you unlink it - you are already showing the totals for each question through the 30 queries (albeit in the footer). You put it into the footer because each record in the main table has a unique ID and it would show many many times if you include it in the details. But if you link it, it is trying to show the summary results for each unique ID, which in the eyes of Access is the correct way to handle it. I think you are back to your original thoughts that you will have to do the filtering in each of the subreport queries. Let me know if you need help with that. HTH, Andrew P.S. There was a misunderstanding - I meant a totals query for the main report and separate totals queries for the subreports, but after the penny dropped I'm not sure that will make a difference.	andrew93 (249)
1 2