If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
cannot open any more databases
Sample data and a description of the tables and relationships may help, as I
really don't understand the situation you are describing. "hughess7" wrote in message ... No, that is the prob. The main report is grouped first by Level and then ClaimNo. The subreport with the problem is a final summary which is just grouped on the level and does not include a claimno ref. Although I have tried changing this so it does inc it and linking it properly etc but the error still occurred. I think it might be because of the structure of the main report - it has Level 5 claims first listed on the report, with each claim detail shown below. But within a claim it can have levels 5 and level 4 for example. The claim appears under level 5 but it shows the finances for both levels 5 and 4. Hence I am struggling to do a summary by summing these values and I have done it in a subreport... Sorry this prob confuses and doesn't make sense. I will post data as an example if that will help u try to understand? "BruceM" wrote: If the subreport shows issues per claim, is it not based on a linked table? If it is based on a linked table, is the subreport control linked? Alternatively, you could base the report on a query that includes the main Claims table and the related Issues table. Group by Claim, with Claim information in the group header. "hughess7" wrote in message ... This has been happening for a few days now, we all shutdown our machines every night when we leave work. Tried compacting and repairing database when it first happened but no joy. If I delete the subreport it works ok. It is a complicated report, the message formatting page press ctrl break to stop appears for a short while before the report is first displayed when run. Yes sorry error number is 3048 - it doesn't tell you unless you press the Help button. -- Thanks in advance for any help. Sue "Arvin Meyer MVP" wrote: I think Bruce was looking for the error number, which I think is 3048, at least that's the one I've been looking at. Try rebooting all machines connected to the server. While they are rebooting, make sure all the connections are closed to the database. If any are open, boot them off. Delete the LDB file on the server before reconnecting any databases to the server. If this is all happening on a single machine, just try rebooting and deleting any LDB files left. Repair and compact the database before trying to run the report again. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "hughess7" wrote in message ... Sorry.. it is the subject of this msg. Error: Cannot open any more databases. From reading forums etc it looks like it is to do with number of connections to database or something. I do have linked tables whch I believe makes this issue more likely to occur but I always believed this was the best design for shared db's. It is a complicated report I am trying to print, it has three subreports and the final subreport it is trying to print which causes the error is based on a union query. Tried simplyfying it as much as poss, but I can't get rid of the error and it won't print the last subreport. Possibly because this subreport does not have child/master link to the main report, but it doesn't work properly if I add a relationship. Odd that it looks ok when you preview, it only errors if you try to print it. I can either print it as a seperate report - but then I don't get continuous page numbers - unless anyone knows how to start a report page number based on the last page number of another report? Or I will have to try and store these values in a temp table maybe and try this as the recordsource for the final subreport. Any suggestions? Thanks... BruceM" wrote: Any question about an error needs to be specific. What error are you getting? "hughess7" wrote in message ... Hi, we have SP3 installed so should be ok for that issue I think? This is driving me potty... :-( "Arvin Meyer MVP" wrote: Have a look at the following Microsoft KB article: http://support.microsoft.com/kb/890931/ -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "hughess7" wrote in message ... Hi all Please help!! I can't get rid of an error that keeps coming up when i a report (preview is ok?). It is the final financials summary subreport that is causing the problem, if I delete this from the report it prints ok. If I print the subreport standalone is prints ok. The subreport is not linked (no child / master relationship) but it needs to appear at the bottom of the main report so it has the same headers and footers (page no count mainly). Is this the problem because of not being linked? I have tried all sorts, at first I thought it might be because two of the subreports were using the same query for datasource but I changed this. I also tried simplyfying the queries etc etc. Still no joy :-(. The report is grouped first by Level (5-0) and then with each claim per max level displayed. It has a subreport showing Issues found per claim and also a subreport showing level financial total(s) - there can be a max of 2 levels per claims. Due to this structure I can't sum the level amounts to appear at the bottom of the report, hence the financials subreport! I have now wasted a whole day on trying to get this working and I have a tight deadline to meet for this system. Please can anyone help? Thanks in advance |
#12
|
|||
|
|||
cannot open any more databases
Thanks, I will try to show you a cut down of the tables data structure etc ...
Tables: [DealerClaims] ClaimNo LabourPD PartsPD SubletPD PartialLabour PartialParts PartialSublet [DealerClaimsIssues] ClaimNo IssueCode [IssueCodes] IssueCode Result So, you have a claim header record which has claim values stored in DealerClaims. You then have a one to many relationship with DealerClaimIssues, which stores all the issues found with a claim. Each Issue you can apply is stored in the table IssueCodes and has a result (0-5) against each individual issue. At the end of an audit, the Claims have a MaxResult (Level), which is the highest Issue Result assigned to the claim. The Labour, Parts and Sublet values of the claim get assigned to this level on the report. BUT, if there are any values entered in the Partial amounts fields then a 2nd level for that claim is applied. This is done via queries, I use the MaxResult to find the 2nd highest level using two different queries and I then union the queries together to give you the Claim financials by level. Each claim can have one OR two levels at the most. eg you could have issues against a claim with the levels (Result) 4,4,4,3,3,2,2,1 etc. The Claim 123456 has values of Labour = 200, Parts = 100, Sublet = 50 PartialLabour = 100, Parts 0, Sublet = 0 So in this example Claim 123456 will appear in the report once under the Level heading of 4, but then under the claim header you will have two lines of values - 1 showing 200, 100 and 50 against level 4. 2nd line showing 100, 0 and 0 against level 3. Then under the levels breakdown of finances you have all the Issues listed that were found on the claim. The main report is grouped on Level first (MaxofResult header), then Claim No (ClaimNo header). The Claim details are in the Claim header group. The financials (labour etc) and the Issues are two seperate subreports linked to the ClaimNo on the report. So for this example you would get: Level 4. Claim 123456 - details Level 4. 200, 100, 50 Level 3. 100, 0, 0 Issues - no customer signature found etc etc Then you would get the next Claimno listed if any more under level 4 OR you would get Level 3 heading and the Claimno's found under this max level heading. etc etc. Each claim only ever appears once on the report, under its Max result heading. The summary of the report needs to be all the values grouped by levels 0-5 (therefore both full and partial payments added together per level). I can do this successfully via a subreport in the report footer, but this is what will not print and produces the error. I have tried to do it by not using another subreport, but I cant get it to show the levels and totals correctly. I hope this is clearer? Sorry it is not very easy to explain... "BruceM" wrote: Sample data and a description of the tables and relationships may help, as I really don't understand the situation you are describing. "hughess7" wrote in message ... No, that is the prob. The main report is grouped first by Level and then ClaimNo. The subreport with the problem is a final summary which is just grouped on the level and does not include a claimno ref. Although I have tried changing this so it does inc it and linking it properly etc but the error still occurred. I think it might be because of the structure of the main report - it has Level 5 claims first listed on the report, with each claim detail shown below. But within a claim it can have levels 5 and level 4 for example. The claim appears under level 5 but it shows the finances for both levels 5 and 4. Hence I am struggling to do a summary by summing these values and I have done it in a subreport... Sorry this prob confuses and doesn't make sense. I will post data as an example if that will help u try to understand? "BruceM" wrote: If the subreport shows issues per claim, is it not based on a linked table? If it is based on a linked table, is the subreport control linked? Alternatively, you could base the report on a query that includes the main Claims table and the related Issues table. Group by Claim, with Claim information in the group header. "hughess7" wrote in message ... This has been happening for a few days now, we all shutdown our machines every night when we leave work. Tried compacting and repairing database when it first happened but no joy. If I delete the subreport it works ok. It is a complicated report, the message formatting page press ctrl break to stop appears for a short while before the report is first displayed when run. Yes sorry error number is 3048 - it doesn't tell you unless you press the Help button. -- Thanks in advance for any help. Sue "Arvin Meyer MVP" wrote: I think Bruce was looking for the error number, which I think is 3048, at least that's the one I've been looking at. Try rebooting all machines connected to the server. While they are rebooting, make sure all the connections are closed to the database. If any are open, boot them off. Delete the LDB file on the server before reconnecting any databases to the server. If this is all happening on a single machine, just try rebooting and deleting any LDB files left. Repair and compact the database before trying to run the report again. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "hughess7" wrote in message ... Sorry.. it is the subject of this msg. Error: Cannot open any more databases. From reading forums etc it looks like it is to do with number of connections to database or something. I do have linked tables whch I believe makes this issue more likely to occur but I always believed this was the best design for shared db's. It is a complicated report I am trying to print, it has three subreports and the final subreport it is trying to print which causes the error is based on a union query. Tried simplyfying it as much as poss, but I can't get rid of the error and it won't print the last subreport. Possibly because this subreport does not have child/master link to the main report, but it doesn't work properly if I add a relationship. Odd that it looks ok when you preview, it only errors if you try to print it. I can either print it as a seperate report - but then I don't get continuous page numbers - unless anyone knows how to start a report page number based on the last page number of another report? Or I will have to try and store these values in a temp table maybe and try this as the recordsource for the final subreport. Any suggestions? Thanks... BruceM" wrote: Any question about an error needs to be specific. What error are you getting? "hughess7" wrote in message ... Hi, we have SP3 installed so should be ok for that issue I think? This is driving me potty... :-( "Arvin Meyer MVP" wrote: Have a look at the following Microsoft KB article: http://support.microsoft.com/kb/890931/ -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "hughess7" wrote in message ... Hi all Please help!! I can't get rid of an error that keeps coming up when i a report (preview is ok?). It is the final financials summary subreport that is causing the problem, if I delete this from the report it prints ok. If I print the subreport standalone is prints ok. The subreport is not linked (no child / master relationship) but it needs to appear at the bottom of the main report so it has the same headers and footers (page no count mainly). Is this the problem because of not being linked? I have tried all sorts, at first I thought it might be because two of the subreports were using the same query for datasource but I changed this. I also tried simplyfying the queries etc etc. Still no joy :-(. The report is grouped first by Level (5-0) and then with each claim per max level displayed. It has a subreport showing Issues found per claim and also a subreport showing level financial total(s) - there can be a max of 2 levels per claims. Due to this structure I can't sum the level amounts to appear at the bottom of the report, hence the financials subreport! I have now wasted a whole day on trying to get this working and I have a tight deadline to meet for this system. Please can anyone help? Thanks in advance |
#13
|
|||
|
|||
cannot open any more databases
IssueCode is a lookup table? That is, its only purpose is to provide data
for IssueCode in DealerClaimsIssues? Does DealerClaimsIssues have a primary key? I think I would have stored Result rather than IssueCode in DealerClaimsIssues, as it is the field you need to work with directly. As I understand, you are looking for the highest Result value amongst the Issues associated with a claim. I still do not understand the situation fully, but I think you will need to combine DealerClaims and DealerClaimsIssues into a query, and use that query as the report's Record Source. As I understand, the top level of the grouping hierarchy is Issue. To group by this value, it needs to be available in the main report's Record Source. You want to see Issue 4 at the top, followed by claims that have an Issue of 4. Where I lose you completely is the logic by which "Labour, Parts and Sublet values of the claim get assigned to this level", while other fields are associated with Level 3, nor can I picture how other levels come into play. I do not understand the following statement at all: "Each Issue you can apply is stored in the table IssueCodes and has a result (0-5) against each individual issue". Part of the difficulty in understanding is that I do not see how the tables are related in all cases. I think I can see that DealerClaimsIssues is related to DealerClaims by ClaimNo, but is IssueCodes related to DealerClaimsIssues, or what exactly? At first I thought it was a lookup table, but now I wonder. If you identify primary key fields and linking fields it may help. "hughess7" wrote in message ... Thanks, I will try to show you a cut down of the tables data structure etc ... Tables: [DealerClaims] ClaimNo LabourPD PartsPD SubletPD PartialLabour PartialParts PartialSublet [DealerClaimsIssues] ClaimNo IssueCode [IssueCodes] IssueCode Result So, you have a claim header record which has claim values stored in DealerClaims. You then have a one to many relationship with DealerClaimIssues, which stores all the issues found with a claim. Each Issue you can apply is stored in the table IssueCodes and has a result (0-5) against each individual issue. At the end of an audit, the Claims have a MaxResult (Level), which is the highest Issue Result assigned to the claim. The Labour, Parts and Sublet values of the claim get assigned to this level on the report. BUT, if there are any values entered in the Partial amounts fields then a 2nd level for that claim is applied. This is done via queries, I use the MaxResult to find the 2nd highest level using two different queries and I then union the queries together to give you the Claim financials by level. Each claim can have one OR two levels at the most. eg you could have issues against a claim with the levels (Result) 4,4,4,3,3,2,2,1 etc. The Claim 123456 has values of Labour = 200, Parts = 100, Sublet = 50 PartialLabour = 100, Parts 0, Sublet = 0 So in this example Claim 123456 will appear in the report once under the Level heading of 4, but then under the claim header you will have two lines of values - 1 showing 200, 100 and 50 against level 4. 2nd line showing 100, 0 and 0 against level 3. Then under the levels breakdown of finances you have all the Issues listed that were found on the claim. The main report is grouped on Level first (MaxofResult header), then Claim No (ClaimNo header). The Claim details are in the Claim header group. The financials (labour etc) and the Issues are two seperate subreports linked to the ClaimNo on the report. So for this example you would get: Level 4. Claim 123456 - details Level 4. 200, 100, 50 Level 3. 100, 0, 0 Issues - no customer signature found etc etc Then you would get the next Claimno listed if any more under level 4 OR you would get Level 3 heading and the Claimno's found under this max level heading. etc etc. Each claim only ever appears once on the report, under its Max result heading. The summary of the report needs to be all the values grouped by levels 0-5 (therefore both full and partial payments added together per level). I can do this successfully via a subreport in the report footer, but this is what will not print and produces the error. I have tried to do it by not using another subreport, but I cant get it to show the levels and totals correctly. I hope this is clearer? Sorry it is not very easy to explain... "BruceM" wrote: Sample data and a description of the tables and relationships may help, as I really don't understand the situation you are describing. "hughess7" wrote in message ... No, that is the prob. The main report is grouped first by Level and then ClaimNo. The subreport with the problem is a final summary which is just grouped on the level and does not include a claimno ref. Although I have tried changing this so it does inc it and linking it properly etc but the error still occurred. I think it might be because of the structure of the main report - it has Level 5 claims first listed on the report, with each claim detail shown below. But within a claim it can have levels 5 and level 4 for example. The claim appears under level 5 but it shows the finances for both levels 5 and 4. Hence I am struggling to do a summary by summing these values and I have done it in a subreport... Sorry this prob confuses and doesn't make sense. I will post data as an example if that will help u try to understand? "BruceM" wrote: If the subreport shows issues per claim, is it not based on a linked table? If it is based on a linked table, is the subreport control linked? Alternatively, you could base the report on a query that includes the main Claims table and the related Issues table. Group by Claim, with Claim information in the group header. "hughess7" wrote in message ... This has been happening for a few days now, we all shutdown our machines every night when we leave work. Tried compacting and repairing database when it first happened but no joy. If I delete the subreport it works ok. It is a complicated report, the message formatting page press ctrl break to stop appears for a short while before the report is first displayed when run. Yes sorry error number is 3048 - it doesn't tell you unless you press the Help button. -- Thanks in advance for any help. Sue "Arvin Meyer MVP" wrote: I think Bruce was looking for the error number, which I think is 3048, at least that's the one I've been looking at. Try rebooting all machines connected to the server. While they are rebooting, make sure all the connections are closed to the database. If any are open, boot them off. Delete the LDB file on the server before reconnecting any databases to the server. If this is all happening on a single machine, just try rebooting and deleting any LDB files left. Repair and compact the database before trying to run the report again. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "hughess7" wrote in message ... Sorry.. it is the subject of this msg. Error: Cannot open any more databases. From reading forums etc it looks like it is to do with number of connections to database or something. I do have linked tables whch I believe makes this issue more likely to occur but I always believed this was the best design for shared db's. It is a complicated report I am trying to print, it has three subreports and the final subreport it is trying to print which causes the error is based on a union query. Tried simplyfying it as much as poss, but I can't get rid of the error and it won't print the last subreport. Possibly because this subreport does not have child/master link to the main report, but it doesn't work properly if I add a relationship. Odd that it looks ok when you preview, it only errors if you try to print it. I can either print it as a seperate report - but then I don't get continuous page numbers - unless anyone knows how to start a report page number based on the last page number of another report? Or I will have to try and store these values in a temp table maybe and try this as the recordsource for the final subreport. Any suggestions? Thanks... BruceM" wrote: Any question about an error needs to be specific. What error are you getting? "hughess7" wrote in message ... Hi, we have SP3 installed so should be ok for that issue I think? This is driving me potty... :-( "Arvin Meyer MVP" wrote: Have a look at the following Microsoft KB article: http://support.microsoft.com/kb/890931/ -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "hughess7" wrote in message ... Hi all Please help!! I can't get rid of an error that keeps coming up when i a report (preview is ok?). It is the final financials summary subreport that is causing the problem, if I delete this from the report it prints ok. If I print the subreport standalone is prints ok. The subreport is not linked (no child / master relationship) but it needs to appear at the bottom of the main report so it has the same headers and footers (page no count mainly). Is this the problem because of not being linked? I have tried all sorts, at first I thought it might be because two of the subreports were using the same query for datasource but I changed this. I also tried simplyfying the queries etc etc. Still no joy :-(. The report is grouped first by Level (5-0) and then with each claim per max level displayed. It has a subreport showing Issues found per claim and also a subreport showing level financial total(s) - there can be a max of 2 levels per claims. Due to this structure I can't sum the level amounts to appear at the bottom of the report, hence the financials subreport! I have now wasted a whole day on trying to get this working and I have a tight deadline to meet for this system. Please can anyone help? Thanks in advance |
#15
|
|||
|
|||
cannot open any more databases
Sorry, like I said it is complicated and not easy explaining the structure -
I only gave you a cutdown version to try and show the relationship between Claims and Issues... but there are a few other factors that influence why it is designed this way... All these Issues relate to a table of Standards so it is not just a lookup table, one standard can have many Issues (stdRef being the primary key). Also we are dealing with a multi-country/language system so each of these tables also has a translation table linked to them (one to many). So the IssueCode DOES have to be stored against the claim and not the result (surely this would not be normalised anyway if not?). IssueCode (Primary Key: IssueCode) Currently there are 113 Issues Category (ID of area it falls into eg reception, workshop etc - seperate category table) Result (0-5) (seperate result lookup table giving text desc of result) Readings (True/False) - used in code to capture extra variables by the user) Comments (True/False - used in code to force user to enter further mandatory info req'd) StdRef (ID link to seperate Standards table, one standard can have many Issues ) Standards (Primary Key: StdRef) Currently 40 stds StdRef (autonumber) Std_No (customers text Ref) The reports are produced in local language (+ English) so we have one report per country as all the headings are hard-coded into each report too in local language. The translation tables have a Primary key of Country, Language and Code and are used as lookup tables to get the correct country translations (some countries have more than one language). The translated Issues table, also has a translated Action against them. The reason for all this is to show a dealer which standards he has not complied to, what the actual issues were so he can have an action plan to resolve them, and see the financial consequences (chargeback) broken down by each level. Each level is treated differently, some are more serious than others with level 5 being the most serious. The financial consequences differ depending on what max levels are found. A claim can have a partial chargeback hence the need for two different levels, the max and the 2nd highest level (which defaults to 0 if no 2nd level). You don't need to worry about how I work out these values though as this part is all working and is done via a set of queries (this is the financial subreport, split by claimno). Yes DealerClaimIssues has a key: Country (linked to country table) DealerCode (linked to Dealer table for language etc) ReviewDate (to ensure we have the right activity - there can be more than 1 a year) ClaimNo (linked to DealerClaim table which has same key excluding Issue Code) IssueCode (linked to Issue Codes which in turn links to Issue Translations table) Additional fields a Translated Issue Text (combination of std text plus user comments and possible additional variables) The top level grouping of the report is Level (result), NOT Issue. It is the Max result which comes from the issues found. eg 0 to 5. eg Claim 123456 may have 20 issues assigned to it. Each of these issues will have a corresponding level (0 to 5). If max level of that claim is a level 5, there will be no partial chargeback so the original total amounts of labour etc will be chargeback (100%). If level 4 or below is the max level found, it can have partial chargeback. The end user creates these partial labour, parts and sublet amounts against the claim header (DealerClaim). If these amounts are not 0 then when the report is produced it assigns these partial values to the max level (which could be between a 0 and 4), then the remainder of the original amounts has to be assigned by the system to the next level down. So if a Level 4 is the max Issue, then were Issues found with a level 3 - if so these become the 2nd level, if not was there a level 2 Issue, if not a level 1 issue and if no other issues then the remainder gets assigned to Level 0. To present this on a report a claim eg 123456 can have two levels - eg a level four with partial amounts and a level 3 with the remainder of the original amounts. BOTH of these amounts would be displayed UNDER Level 4 HEADING, with a list of ALL of the translated Issue text below (result desc sort order but not shown against each Issue). Another way of doing this would be to display Claim 123456 TWICE on the report - once under Level 4 heading and again under Level 3 heading. I suspect that way I could summarise the report properly without any errors. BUT i was trying to avoid doing this to save paper (I would have to repeat all the Issues found twice too) and also to easily identify to the dealer how any one claim has been broken down if it is a partial chargeback. It works perfectly as desired in print preview, but an error occurs if you try to print and it does not print the summary subreport. For now I have deleted the summary from the main report and will have to print them as two seperate reports. I just don't get continuous page numbers this way... "BruceM" wrote: IssueCode is a lookup table? That is, its only purpose is to provide data for IssueCode in DealerClaimsIssues? Does DealerClaimsIssues have a primary key? I think I would have stored Result rather than IssueCode in DealerClaimsIssues, as it is the field you need to work with directly. As I understand, you are looking for the highest Result value amongst the Issues associated with a claim. I still do not understand the situation fully, but I think you will need to combine DealerClaims and DealerClaimsIssues into a query, and use that query as the report's Record Source. As I understand, the top level of the grouping hierarchy is Issue. To group by this value, it needs to be available in the main report's Record Source. You want to see Issue 4 at the top, followed by claims that have an Issue of 4. Where I lose you completely is the logic by which "Labour, Parts and Sublet values of the claim get assigned to this level", while other fields are associated with Level 3, nor can I picture how other levels come into play. I do not understand the following statement at all: "Each Issue you can apply is stored in the table IssueCodes and has a result (0-5) against each individual issue". Part of the difficulty in understanding is that I do not see how the tables are related in all cases. I think I can see that DealerClaimsIssues is related to DealerClaims by ClaimNo, but is IssueCodes related to DealerClaimsIssues, or what exactly? At first I thought it was a lookup table, but now I wonder. If you identify primary key fields and linking fields it may help. "hughess7" wrote in message ... Thanks, I will try to show you a cut down of the tables data structure etc ... Tables: [DealerClaims] ClaimNo LabourPD PartsPD SubletPD PartialLabour PartialParts PartialSublet [DealerClaimsIssues] ClaimNo IssueCode [IssueCodes] IssueCode Result So, you have a claim header record which has claim values stored in DealerClaims. You then have a one to many relationship with DealerClaimIssues, which stores all the issues found with a claim. Each Issue you can apply is stored in the table IssueCodes and has a result (0-5) against each individual issue. At the end of an audit, the Claims have a MaxResult (Level), which is the highest Issue Result assigned to the claim. The Labour, Parts and Sublet values of the claim get assigned to this level on the report. BUT, if there are any values entered in the Partial amounts fields then a 2nd level for that claim is applied. This is done via queries, I use the MaxResult to find the 2nd highest level using two different queries and I then union the queries together to give you the Claim financials by level. Each claim can have one OR two levels at the most. eg you could have issues against a claim with the levels (Result) 4,4,4,3,3,2,2,1 etc. The Claim 123456 has values of Labour = 200, Parts = 100, Sublet = 50 PartialLabour = 100, Parts 0, Sublet = 0 So in this example Claim 123456 will appear in the report once under the Level heading of 4, but then under the claim header you will have two lines of values - 1 showing 200, 100 and 50 against level 4. 2nd line showing 100, 0 and 0 against level 3. Then under the levels breakdown of finances you have all the Issues listed that were found on the claim. The main report is grouped on Level first (MaxofResult header), then Claim No (ClaimNo header). The Claim details are in the Claim header group. The financials (labour etc) and the Issues are two seperate subreports linked to the ClaimNo on the report. So for this example you would get: Level 4. Claim 123456 - details Level 4. 200, 100, 50 Level 3. 100, 0, 0 Issues - no customer signature found etc etc Then you would get the next Claimno listed if any more under level 4 OR you would get Level 3 heading and the Claimno's found under this max level heading. etc etc. Each claim only ever appears once on the report, under its Max result heading. The summary of the report needs to be all the values grouped by levels 0-5 (therefore both full and partial payments added together per level). I can do this successfully via a subreport in the report footer, but this is what will not print and produces the error. I have tried to do it by not using another subreport, but I cant get it to show the levels and totals correctly. I hope this is clearer? Sorry it is not very easy to explain... "BruceM" wrote: Sample data and a description of the tables and relationships may help, as I really don't understand the situation you are describing. "hughess7" wrote in message ... No, that is the prob. The main report is grouped first by Level and then ClaimNo. The subreport with the problem is a final summary which is just grouped on the level and does not include a claimno ref. Although I have tried changing this so it does inc it and linking it properly etc but the error still occurred. I think it might be because of the structure of the main report - it has Level 5 claims first listed on the report, with each claim detail shown below. But within a claim it can have levels 5 and level 4 for example. The claim appears under level 5 but it shows the finances for both levels 5 and 4. Hence I am struggling to do a summary by summing these values and I have done it in a subreport... Sorry this prob confuses and doesn't make sense. I will post data as an example if that will help u try to understand? "BruceM" wrote: If the subreport shows issues per claim, is it not based on a linked table? If it is based on a linked table, is the subreport control linked? Alternatively, you could base the report on a query that includes the main Claims table and the related Issues table. Group by Claim, with Claim information in the group header. "hughess7" wrote in message ... This has been happening for a few days now, we all shutdown our machines every night when we leave work. Tried compacting and repairing database when it first happened but no joy. If I delete the subreport it works ok. It is a complicated report, the message formatting page press ctrl break to stop appears for a short while before the report is first displayed when run. Yes sorry error number is 3048 - it doesn't tell you unless you press the Help button. -- Thanks in advance for any help. Sue "Arvin Meyer MVP" wrote: I think Bruce was looking for the error number, which I think is 3048, at least that's the one I've been looking at. Try rebooting all machines connected to the server. While they are rebooting, make sure all the connections are closed to the database. If any are open, boot them off. Delete the LDB file on the server before reconnecting any databases to the server. If this is all happening on a single machine, just try rebooting and deleting any LDB files left. Repair and compact the database before trying to run the report again. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "hughess7" wrote in message ... Sorry.. it is the subject of this msg. Error: Cannot open any more databases. From reading forums etc it looks like it is to do with number of connections to database or something. I do have linked tables whch I believe makes this issue more likely to occur but I always believed this was the best design for shared db's. It is a complicated report I am trying to print, it has three subreports and the final subreport it is trying to print which causes the error is based on a union query. Tried simplyfying it as much as poss, but I can't get rid of the error and it won't print the last subreport. Possibly because this subreport does not have child/master link to the main report, but it doesn't work properly if I add a relationship. Odd that it looks ok when you preview, it only errors if you try to print it. I can either print it as a seperate report - but then I don't get continuous page numbers - unless anyone knows how to start a report page number based on the last page number of another report? Or I will have to try and store these values in a temp table maybe and try this as the recordsource for the final subreport. Any suggestions? Thanks... BruceM" wrote: Any question about an error needs to be specific. What error are you getting? "hughess7" wrote in message ... Hi, we have SP3 installed so should be ok for that issue I think? This is driving me potty... :-( "Arvin Meyer MVP" wrote: Have a look at the following Microsoft KB article: http://support.microsoft.com/kb/890931/ -- Arvin Meyer, MCP, MVP http://www.datastrat.com |
#16
|
|||
|
|||
cannot open any more databases
I'm sorry, but I cannot sort out what is going on, and have a big project
through the rest of the week, so will not have much time to put into this for a while. There are several more tables and a lot more variables than in previous postings, and I can't tell what is relevant. For instance, I can't see if translations enter into the current problem or not. I suspect that the problem occurs because you are trying to print a subreport with a Record Source that does not relate to the main report's Record Source. You can print the subreport by itself, and you can print the main report without the subreport, so it seems the two are not communicating with each other when they are set up as report/subreport. This may be getting overcomplicated. I think the main question is: What is the link between the main report and the subreport? A related question: Can you build a query that includes the main report's data and the subreport's data, and have it present the data in a way that makes sense? I'm not saying this query is necessarily to be anything other than an experiment, but rather trying to determine if the two sets of data can interact properly. "hughess7" wrote in message ... Sorry, like I said it is complicated and not easy explaining the structure - I only gave you a cutdown version to try and show the relationship between Claims and Issues... but there are a few other factors that influence why it is designed this way... All these Issues relate to a table of Standards so it is not just a lookup table, one standard can have many Issues (stdRef being the primary key). Also we are dealing with a multi-country/language system so each of these tables also has a translation table linked to them (one to many). So the IssueCode DOES have to be stored against the claim and not the result (surely this would not be normalised anyway if not?). IssueCode (Primary Key: IssueCode) Currently there are 113 Issues Category (ID of area it falls into eg reception, workshop etc - seperate category table) Result (0-5) (seperate result lookup table giving text desc of result) Readings (True/False) - used in code to capture extra variables by the user) Comments (True/False - used in code to force user to enter further mandatory info req'd) StdRef (ID link to seperate Standards table, one standard can have many Issues ) Standards (Primary Key: StdRef) Currently 40 stds StdRef (autonumber) Std_No (customers text Ref) The reports are produced in local language (+ English) so we have one report per country as all the headings are hard-coded into each report too in local language. The translation tables have a Primary key of Country, Language and Code and are used as lookup tables to get the correct country translations (some countries have more than one language). The translated Issues table, also has a translated Action against them. The reason for all this is to show a dealer which standards he has not complied to, what the actual issues were so he can have an action plan to resolve them, and see the financial consequences (chargeback) broken down by each level. Each level is treated differently, some are more serious than others with level 5 being the most serious. The financial consequences differ depending on what max levels are found. A claim can have a partial chargeback hence the need for two different levels, the max and the 2nd highest level (which defaults to 0 if no 2nd level). You don't need to worry about how I work out these values though as this part is all working and is done via a set of queries (this is the financial subreport, split by claimno). Yes DealerClaimIssues has a key: Country (linked to country table) DealerCode (linked to Dealer table for language etc) ReviewDate (to ensure we have the right activity - there can be more than 1 a year) ClaimNo (linked to DealerClaim table which has same key excluding Issue Code) IssueCode (linked to Issue Codes which in turn links to Issue Translations table) Additional fields a Translated Issue Text (combination of std text plus user comments and possible additional variables) The top level grouping of the report is Level (result), NOT Issue. It is the Max result which comes from the issues found. eg 0 to 5. eg Claim 123456 may have 20 issues assigned to it. Each of these issues will have a corresponding level (0 to 5). If max level of that claim is a level 5, there will be no partial chargeback so the original total amounts of labour etc will be chargeback (100%). If level 4 or below is the max level found, it can have partial chargeback. The end user creates these partial labour, parts and sublet amounts against the claim header (DealerClaim). If these amounts are not 0 then when the report is produced it assigns these partial values to the max level (which could be between a 0 and 4), then the remainder of the original amounts has to be assigned by the system to the next level down. So if a Level 4 is the max Issue, then were Issues found with a level 3 - if so these become the 2nd level, if not was there a level 2 Issue, if not a level 1 issue and if no other issues then the remainder gets assigned to Level 0. To present this on a report a claim eg 123456 can have two levels - eg a level four with partial amounts and a level 3 with the remainder of the original amounts. BOTH of these amounts would be displayed UNDER Level 4 HEADING, with a list of ALL of the translated Issue text below (result desc sort order but not shown against each Issue). Another way of doing this would be to display Claim 123456 TWICE on the report - once under Level 4 heading and again under Level 3 heading. I suspect that way I could summarise the report properly without any errors. BUT i was trying to avoid doing this to save paper (I would have to repeat all the Issues found twice too) and also to easily identify to the dealer how any one claim has been broken down if it is a partial chargeback. It works perfectly as desired in print preview, but an error occurs if you try to print and it does not print the summary subreport. For now I have deleted the summary from the main report and will have to print them as two seperate reports. I just don't get continuous page numbers this way... "BruceM" wrote: IssueCode is a lookup table? That is, its only purpose is to provide data for IssueCode in DealerClaimsIssues? Does DealerClaimsIssues have a primary key? I think I would have stored Result rather than IssueCode in DealerClaimsIssues, as it is the field you need to work with directly. As I understand, you are looking for the highest Result value amongst the Issues associated with a claim. I still do not understand the situation fully, but I think you will need to combine DealerClaims and DealerClaimsIssues into a query, and use that query as the report's Record Source. As I understand, the top level of the grouping hierarchy is Issue. To group by this value, it needs to be available in the main report's Record Source. You want to see Issue 4 at the top, followed by claims that have an Issue of 4. Where I lose you completely is the logic by which "Labour, Parts and Sublet values of the claim get assigned to this level", while other fields are associated with Level 3, nor can I picture how other levels come into play. I do not understand the following statement at all: "Each Issue you can apply is stored in the table IssueCodes and has a result (0-5) against each individual issue". Part of the difficulty in understanding is that I do not see how the tables are related in all cases. I think I can see that DealerClaimsIssues is related to DealerClaims by ClaimNo, but is IssueCodes related to DealerClaimsIssues, or what exactly? At first I thought it was a lookup table, but now I wonder. If you identify primary key fields and linking fields it may help. "hughess7" wrote in message ... Thanks, I will try to show you a cut down of the tables data structure etc ... Tables: [DealerClaims] ClaimNo LabourPD PartsPD SubletPD PartialLabour PartialParts PartialSublet [DealerClaimsIssues] ClaimNo IssueCode [IssueCodes] IssueCode Result So, you have a claim header record which has claim values stored in DealerClaims. You then have a one to many relationship with DealerClaimIssues, which stores all the issues found with a claim. Each Issue you can apply is stored in the table IssueCodes and has a result (0-5) against each individual issue. At the end of an audit, the Claims have a MaxResult (Level), which is the highest Issue Result assigned to the claim. The Labour, Parts and Sublet values of the claim get assigned to this level on the report. BUT, if there are any values entered in the Partial amounts fields then a 2nd level for that claim is applied. This is done via queries, I use the MaxResult to find the 2nd highest level using two different queries and I then union the queries together to give you the Claim financials by level. Each claim can have one OR two levels at the most. eg you could have issues against a claim with the levels (Result) 4,4,4,3,3,2,2,1 etc. The Claim 123456 has values of Labour = 200, Parts = 100, Sublet = 50 PartialLabour = 100, Parts 0, Sublet = 0 So in this example Claim 123456 will appear in the report once under the Level heading of 4, but then under the claim header you will have two lines of values - 1 showing 200, 100 and 50 against level 4. 2nd line showing 100, 0 and 0 against level 3. Then under the levels breakdown of finances you have all the Issues listed that were found on the claim. The main report is grouped on Level first (MaxofResult header), then Claim No (ClaimNo header). The Claim details are in the Claim header group. The financials (labour etc) and the Issues are two seperate subreports linked to the ClaimNo on the report. So for this example you would get: Level 4. Claim 123456 - details Level 4. 200, 100, 50 Level 3. 100, 0, 0 Issues - no customer signature found etc etc Then you would get the next Claimno listed if any more under level 4 OR you would get Level 3 heading and the Claimno's found under this max level heading. etc etc. Each claim only ever appears once on the report, under its Max result heading. The summary of the report needs to be all the values grouped by levels 0-5 (therefore both full and partial payments added together per level). I can do this successfully via a subreport in the report footer, but this is what will not print and produces the error. I have tried to do it by not using another subreport, but I cant get it to show the levels and totals correctly. I hope this is clearer? Sorry it is not very easy to explain... "BruceM" wrote: Sample data and a description of the tables and relationships may help, as I really don't understand the situation you are describing. "hughess7" wrote in message ... No, that is the prob. The main report is grouped first by Level and then ClaimNo. The subreport with the problem is a final summary which is just grouped on the level and does not include a claimno ref. Although I have tried changing this so it does inc it and linking it properly etc but the error still occurred. I think it might be because of the structure of the main report - it has Level 5 claims first listed on the report, with each claim detail shown below. But within a claim it can have levels 5 and level 4 for example. The claim appears under level 5 but it shows the finances for both levels 5 and 4. Hence I am struggling to do a summary by summing these values and I have done it in a subreport... Sorry this prob confuses and doesn't make sense. I will post data as an example if that will help u try to understand? "BruceM" wrote: If the subreport shows issues per claim, is it not based on a linked table? If it is based on a linked table, is the subreport control linked? Alternatively, you could base the report on a query that includes the main Claims table and the related Issues table. Group by Claim, with Claim information in the group header. "hughess7" wrote in message ... This has been happening for a few days now, we all shutdown our machines every night when we leave work. Tried compacting and repairing database when it first happened but no joy. If I delete the subreport it works ok. It is a complicated report, the message formatting page press ctrl break to stop appears for a short while before the report is first displayed when run. Yes sorry error number is 3048 - it doesn't tell you unless you press the Help button. -- Thanks in advance for any help. Sue "Arvin Meyer MVP" wrote: I think Bruce was looking for the error number, which I think is 3048, at least that's the one I've been looking at. Try rebooting all machines connected to the server. While they are rebooting, make sure all the connections are closed to the database. If any are open, boot them off. Delete the LDB file on the server before reconnecting any databases to the server. If this is all happening on a single machine, just try rebooting and deleting any LDB files left. Repair and compact the database before trying to run the report again. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "hughess7" wrote in message ... Sorry.. it is the subject of this msg. Error: Cannot open any more databases. From reading forums etc it looks like it is to do with number of connections to database or something. I do have linked tables whch I believe makes this issue more likely to occur but I always believed this was the best design for shared db's. It is a complicated report I am trying to print, it has three subreports and the final subreport it is trying to print which causes the error is based on a union query. Tried simplyfying it as much as poss, but I can't get rid of the error and it won't print the last subreport. Possibly because this subreport does not have child/master link to the main report, but it doesn't work properly if I add a relationship. Odd that it looks ok when you preview, it only errors if you try to print it. I can either print it as a seperate report - but then I don't get continuous page numbers - unless anyone knows how to start a report page number based on the last page number of another report? Or I will have to try and store these values in a temp table maybe and try this as the recordsource for the final subreport. Any suggestions? Thanks... BruceM" wrote: Any question about an error needs to be specific. What error are you getting? "hughess7" wrote in message ... Hi, we have SP3 installed so should be ok for that issue I think? This is driving me potty... :-( "Arvin Meyer MVP" wrote: Have a look at the following Microsoft KB article: http://support.microsoft.com/kb/890931/ -- Arvin Meyer, MCP, MVP http://www.datastrat.com |
#17
|
|||
|
|||
cannot open any more databases
That is why I didn't put it all in the original posting as I didn't want to
confuse the issue but I was trying to show you that the Issues are not just a simple lookup table. Yes as I said at the beginning I think that is partly my problem - that the two don't relate exactly, as in not the same structure / layout format and could be causing the issue. BUT, as also previously mentioned I have already tried your suggestion of doing a query so they can relate in the same way but the error still occurred anyway! I think to be honest it is not to do with layout... I think it is because the report becomes too complicated and uses too many open connections (?) - different subreports using different queries based on the same table data etc maybe, I don't know. Anyway, thanks for your time and trying to understand it. It is very frustrating but I'll just stick with printing them seperately for now... Thanks again... "BruceM" wrote: I'm sorry, but I cannot sort out what is going on, and have a big project through the rest of the week, so will not have much time to put into this for a while. There are several more tables and a lot more variables than in previous postings, and I can't tell what is relevant. For instance, I can't see if translations enter into the current problem or not. I suspect that the problem occurs because you are trying to print a subreport with a Record Source that does not relate to the main report's Record Source. You can print the subreport by itself, and you can print the main report without the subreport, so it seems the two are not communicating with each other when they are set up as report/subreport. This may be getting overcomplicated. I think the main question is: What is the link between the main report and the subreport? A related question: Can you build a query that includes the main report's data and the subreport's data, and have it present the data in a way that makes sense? I'm not saying this query is necessarily to be anything other than an experiment, but rather trying to determine if the two sets of data can interact properly. "hughess7" wrote in message ... Sorry, like I said it is complicated and not easy explaining the structure - I only gave you a cutdown version to try and show the relationship between Claims and Issues... but there are a few other factors that influence why it is designed this way... All these Issues relate to a table of Standards so it is not just a lookup table, one standard can have many Issues (stdRef being the primary key). Also we are dealing with a multi-country/language system so each of these tables also has a translation table linked to them (one to many). So the IssueCode DOES have to be stored against the claim and not the result (surely this would not be normalised anyway if not?). IssueCode (Primary Key: IssueCode) Currently there are 113 Issues Category (ID of area it falls into eg reception, workshop etc - seperate category table) Result (0-5) (seperate result lookup table giving text desc of result) Readings (True/False) - used in code to capture extra variables by the user) Comments (True/False - used in code to force user to enter further mandatory info req'd) StdRef (ID link to seperate Standards table, one standard can have many Issues ) Standards (Primary Key: StdRef) Currently 40 stds StdRef (autonumber) Std_No (customers text Ref) The reports are produced in local language (+ English) so we have one report per country as all the headings are hard-coded into each report too in local language. The translation tables have a Primary key of Country, Language and Code and are used as lookup tables to get the correct country translations (some countries have more than one language). The translated Issues table, also has a translated Action against them. The reason for all this is to show a dealer which standards he has not complied to, what the actual issues were so he can have an action plan to resolve them, and see the financial consequences (chargeback) broken down by each level. Each level is treated differently, some are more serious than others with level 5 being the most serious. The financial consequences differ depending on what max levels are found. A claim can have a partial chargeback hence the need for two different levels, the max and the 2nd highest level (which defaults to 0 if no 2nd level). You don't need to worry about how I work out these values though as this part is all working and is done via a set of queries (this is the financial subreport, split by claimno). Yes DealerClaimIssues has a key: Country (linked to country table) DealerCode (linked to Dealer table for language etc) ReviewDate (to ensure we have the right activity - there can be more than 1 a year) ClaimNo (linked to DealerClaim table which has same key excluding Issue Code) IssueCode (linked to Issue Codes which in turn links to Issue Translations table) Additional fields a Translated Issue Text (combination of std text plus user comments and possible additional variables) The top level grouping of the report is Level (result), NOT Issue. It is the Max result which comes from the issues found. eg 0 to 5. eg Claim 123456 may have 20 issues assigned to it. Each of these issues will have a corresponding level (0 to 5). If max level of that claim is a level 5, there will be no partial chargeback so the original total amounts of labour etc will be chargeback (100%). If level 4 or below is the max level found, it can have partial chargeback. The end user creates these partial labour, parts and sublet amounts against the claim header (DealerClaim). If these amounts are not 0 then when the report is produced it assigns these partial values to the max level (which could be between a 0 and 4), then the remainder of the original amounts has to be assigned by the system to the next level down. So if a Level 4 is the max Issue, then were Issues found with a level 3 - if so these become the 2nd level, if not was there a level 2 Issue, if not a level 1 issue and if no other issues then the remainder gets assigned to Level 0. To present this on a report a claim eg 123456 can have two levels - eg a level four with partial amounts and a level 3 with the remainder of the original amounts. BOTH of these amounts would be displayed UNDER Level 4 HEADING, with a list of ALL of the translated Issue text below (result desc sort order but not shown against each Issue). Another way of doing this would be to display Claim 123456 TWICE on the report - once under Level 4 heading and again under Level 3 heading. I suspect that way I could summarise the report properly without any errors. BUT i was trying to avoid doing this to save paper (I would have to repeat all the Issues found twice too) and also to easily identify to the dealer how any one claim has been broken down if it is a partial chargeback. It works perfectly as desired in print preview, but an error occurs if you try to print and it does not print the summary subreport. For now I have deleted the summary from the main report and will have to print them as two seperate reports. I just don't get continuous page numbers this way... "BruceM" wrote: IssueCode is a lookup table? That is, its only purpose is to provide data for IssueCode in DealerClaimsIssues? Does DealerClaimsIssues have a primary key? I think I would have stored Result rather than IssueCode in DealerClaimsIssues, as it is the field you need to work with directly. As I understand, you are looking for the highest Result value amongst the Issues associated with a claim. I still do not understand the situation fully, but I think you will need to combine DealerClaims and DealerClaimsIssues into a query, and use that query as the report's Record Source. As I understand, the top level of the grouping hierarchy is Issue. To group by this value, it needs to be available in the main report's Record Source. You want to see Issue 4 at the top, followed by claims that have an Issue of 4. Where I lose you completely is the logic by which "Labour, Parts and Sublet values of the claim get assigned to this level", while other fields are associated with Level 3, nor can I picture how other levels come into play. I do not understand the following statement at all: "Each Issue you can apply is stored in the table IssueCodes and has a result (0-5) against each individual issue". Part of the difficulty in understanding is that I do not see how the tables are related in all cases. I think I can see that DealerClaimsIssues is related to DealerClaims by ClaimNo, but is IssueCodes related to DealerClaimsIssues, or what exactly? At first I thought it was a lookup table, but now I wonder. If you identify primary key fields and linking fields it may help. "hughess7" wrote in message ... Thanks, I will try to show you a cut down of the tables data structure etc ... Tables: [DealerClaims] ClaimNo LabourPD PartsPD SubletPD PartialLabour PartialParts PartialSublet [DealerClaimsIssues] ClaimNo IssueCode [IssueCodes] IssueCode Result So, you have a claim header record which has claim values stored in DealerClaims. You then have a one to many relationship with DealerClaimIssues, which stores all the issues found with a claim. Each Issue you can apply is stored in the table IssueCodes and has a result (0-5) against each individual issue. At the end of an audit, the Claims have a MaxResult (Level), which is the highest Issue Result assigned to the claim. The Labour, Parts and Sublet values of the claim get assigned to this level on the report. BUT, if there are any values entered in the Partial amounts fields then a 2nd level for that claim is applied. This is done via queries, I use the MaxResult to find the 2nd highest level using two different queries and I then union the queries together to give you the Claim financials by level. Each claim can have one OR two levels at the most. eg you could have issues against a claim with the levels (Result) 4,4,4,3,3,2,2,1 etc. The Claim 123456 has values of Labour = 200, Parts = 100, Sublet = 50 PartialLabour = 100, Parts 0, Sublet = 0 So in this example Claim 123456 will appear in the report once under the Level heading of 4, but then under the claim header you will have two lines of values - 1 showing 200, 100 and 50 against level 4. 2nd line showing 100, 0 and 0 against level 3. Then under the levels breakdown of finances you have all the Issues listed that were found on the claim. The main report is grouped on Level first (MaxofResult header), then Claim No (ClaimNo header). The Claim details are in the Claim header group. The financials (labour etc) and the Issues are two seperate subreports linked to the ClaimNo on the report. So for this example you would get: Level 4. Claim 123456 - details Level 4. 200, 100, 50 Level 3. 100, 0, 0 Issues - no customer signature found etc etc Then you would get the next Claimno listed if any more under level 4 OR you would get Level 3 heading and the Claimno's found under this max level heading. etc etc. Each claim only ever appears once on the report, under its Max result heading. The summary of the report needs to be all the values grouped by levels 0-5 (therefore both full and partial payments added together per level). I can do this successfully via a subreport in the report footer, but this is what will not print and produces the error. I have tried to do it by not using another subreport, but I cant get it to show the levels and totals correctly. I hope this is clearer? Sorry it is not very easy to explain... "BruceM" wrote: Sample data and a description of the tables and relationships may help, as I really don't understand the situation you are describing. "hughess7" wrote in message ... |
#18
|
|||
|
|||
cannot open any more databases
The main table has a primary key, which is related to a foreign key in a
child table, just as your EmployeeID in your individual payroll records relates to EmployeeID in your main employee record. If your employee record is 9999, each individual record of your pay will contain the number 9999 in the related field, thus associating the record of each of your paychecks to you and nobody else. I have been trying to determine if a similar situation exists in your report/subreport. If the two are based on unrelated recordsets I don't see how it could produce a meaningful report, even if it doesn't crash. If they are related properly, try substituting a simplified version of the subform, with just one or two controls. If it runs properly, keep building the subreport. Conversely, use a copy of the existing report. Remove all but one bound control. If that works, use another copy that has just a few controls. Keep building until you find the problem. Good luck. "hughess7" wrote in message ... That is why I didn't put it all in the original posting as I didn't want to confuse the issue but I was trying to show you that the Issues are not just a simple lookup table. Yes as I said at the beginning I think that is partly my problem - that the two don't relate exactly, as in not the same structure / layout format and could be causing the issue. BUT, as also previously mentioned I have already tried your suggestion of doing a query so they can relate in the same way but the error still occurred anyway! I think to be honest it is not to do with layout... I think it is because the report becomes too complicated and uses too many open connections (?) - different subreports using different queries based on the same table data etc maybe, I don't know. Anyway, thanks for your time and trying to understand it. It is very frustrating but I'll just stick with printing them seperately for now... Thanks again... "BruceM" wrote: I'm sorry, but I cannot sort out what is going on, and have a big project through the rest of the week, so will not have much time to put into this for a while. There are several more tables and a lot more variables than in previous postings, and I can't tell what is relevant. For instance, I can't see if translations enter into the current problem or not. I suspect that the problem occurs because you are trying to print a subreport with a Record Source that does not relate to the main report's Record Source. You can print the subreport by itself, and you can print the main report without the subreport, so it seems the two are not communicating with each other when they are set up as report/subreport. This may be getting overcomplicated. I think the main question is: What is the link between the main report and the subreport? A related question: Can you build a query that includes the main report's data and the subreport's data, and have it present the data in a way that makes sense? I'm not saying this query is necessarily to be anything other than an experiment, but rather trying to determine if the two sets of data can interact properly. "hughess7" wrote in message ... Sorry, like I said it is complicated and not easy explaining the structure - I only gave you a cutdown version to try and show the relationship between Claims and Issues... but there are a few other factors that influence why it is designed this way... All these Issues relate to a table of Standards so it is not just a lookup table, one standard can have many Issues (stdRef being the primary key). Also we are dealing with a multi-country/language system so each of these tables also has a translation table linked to them (one to many). So the IssueCode DOES have to be stored against the claim and not the result (surely this would not be normalised anyway if not?). IssueCode (Primary Key: IssueCode) Currently there are 113 Issues Category (ID of area it falls into eg reception, workshop etc - seperate category table) Result (0-5) (seperate result lookup table giving text desc of result) Readings (True/False) - used in code to capture extra variables by the user) Comments (True/False - used in code to force user to enter further mandatory info req'd) StdRef (ID link to seperate Standards table, one standard can have many Issues ) Standards (Primary Key: StdRef) Currently 40 stds StdRef (autonumber) Std_No (customers text Ref) The reports are produced in local language (+ English) so we have one report per country as all the headings are hard-coded into each report too in local language. The translation tables have a Primary key of Country, Language and Code and are used as lookup tables to get the correct country translations (some countries have more than one language). The translated Issues table, also has a translated Action against them. The reason for all this is to show a dealer which standards he has not complied to, what the actual issues were so he can have an action plan to resolve them, and see the financial consequences (chargeback) broken down by each level. Each level is treated differently, some are more serious than others with level 5 being the most serious. The financial consequences differ depending on what max levels are found. A claim can have a partial chargeback hence the need for two different levels, the max and the 2nd highest level (which defaults to 0 if no 2nd level). You don't need to worry about how I work out these values though as this part is all working and is done via a set of queries (this is the financial subreport, split by claimno). Yes DealerClaimIssues has a key: Country (linked to country table) DealerCode (linked to Dealer table for language etc) ReviewDate (to ensure we have the right activity - there can be more than 1 a year) ClaimNo (linked to DealerClaim table which has same key excluding Issue Code) IssueCode (linked to Issue Codes which in turn links to Issue Translations table) Additional fields a Translated Issue Text (combination of std text plus user comments and possible additional variables) The top level grouping of the report is Level (result), NOT Issue. It is the Max result which comes from the issues found. eg 0 to 5. eg Claim 123456 may have 20 issues assigned to it. Each of these issues will have a corresponding level (0 to 5). If max level of that claim is a level 5, there will be no partial chargeback so the original total amounts of labour etc will be chargeback (100%). If level 4 or below is the max level found, it can have partial chargeback. The end user creates these partial labour, parts and sublet amounts against the claim header (DealerClaim). If these amounts are not 0 then when the report is produced it assigns these partial values to the max level (which could be between a 0 and 4), then the remainder of the original amounts has to be assigned by the system to the next level down. So if a Level 4 is the max Issue, then were Issues found with a level 3 - if so these become the 2nd level, if not was there a level 2 Issue, if not a level 1 issue and if no other issues then the remainder gets assigned to Level 0. To present this on a report a claim eg 123456 can have two levels - eg a level four with partial amounts and a level 3 with the remainder of the original amounts. BOTH of these amounts would be displayed UNDER Level 4 HEADING, with a list of ALL of the translated Issue text below (result desc sort order but not shown against each Issue). Another way of doing this would be to display Claim 123456 TWICE on the report - once under Level 4 heading and again under Level 3 heading. I suspect that way I could summarise the report properly without any errors. BUT i was trying to avoid doing this to save paper (I would have to repeat all the Issues found twice too) and also to easily identify to the dealer how any one claim has been broken down if it is a partial chargeback. It works perfectly as desired in print preview, but an error occurs if you try to print and it does not print the summary subreport. For now I have deleted the summary from the main report and will have to print them as two seperate reports. I just don't get continuous page numbers this way... "BruceM" wrote: IssueCode is a lookup table? That is, its only purpose is to provide data for IssueCode in DealerClaimsIssues? Does DealerClaimsIssues have a primary key? I think I would have stored Result rather than IssueCode in DealerClaimsIssues, as it is the field you need to work with directly. As I understand, you are looking for the highest Result value amongst the Issues associated with a claim. I still do not understand the situation fully, but I think you will need to combine DealerClaims and DealerClaimsIssues into a query, and use that query as the report's Record Source. As I understand, the top level of the grouping hierarchy is Issue. To group by this value, it needs to be available in the main report's Record Source. You want to see Issue 4 at the top, followed by claims that have an Issue of 4. Where I lose you completely is the logic by which "Labour, Parts and Sublet values of the claim get assigned to this level", while other fields are associated with Level 3, nor can I picture how other levels come into play. I do not understand the following statement at all: "Each Issue you can apply is stored in the table IssueCodes and has a result (0-5) against each individual issue". Part of the difficulty in understanding is that I do not see how the tables are related in all cases. I think I can see that DealerClaimsIssues is related to DealerClaims by ClaimNo, but is IssueCodes related to DealerClaimsIssues, or what exactly? At first I thought it was a lookup table, but now I wonder. If you identify primary key fields and linking fields it may help. "hughess7" wrote in message ... Thanks, I will try to show you a cut down of the tables data structure etc ... Tables: [DealerClaims] ClaimNo LabourPD PartsPD SubletPD PartialLabour PartialParts PartialSublet [DealerClaimsIssues] ClaimNo IssueCode [IssueCodes] IssueCode Result So, you have a claim header record which has claim values stored in DealerClaims. You then have a one to many relationship with DealerClaimIssues, which stores all the issues found with a claim. Each Issue you can apply is stored in the table IssueCodes and has a result (0-5) against each individual issue. At the end of an audit, the Claims have a MaxResult (Level), which is the highest Issue Result assigned to the claim. The Labour, Parts and Sublet values of the claim get assigned to this level on the report. BUT, if there are any values entered in the Partial amounts fields then a 2nd level for that claim is applied. This is done via queries, I use the MaxResult to find the 2nd highest level using two different queries and I then union the queries together to give you the Claim financials by level. Each claim can have one OR two levels at the most. eg you could have issues against a claim with the levels (Result) 4,4,4,3,3,2,2,1 etc. The Claim 123456 has values of Labour = 200, Parts = 100, Sublet = 50 PartialLabour = 100, Parts 0, Sublet = 0 So in this example Claim 123456 will appear in the report once under the Level heading of 4, but then under the claim header you will have two lines of values - 1 showing 200, 100 and 50 against level 4. 2nd line showing 100, 0 and 0 against level 3. Then under the levels breakdown of finances you have all the Issues listed that were found on the claim. The main report is grouped on Level first (MaxofResult header), then Claim No (ClaimNo header). The Claim details are in the Claim header group. The financials (labour etc) and the Issues are two seperate subreports linked to the ClaimNo on the report. So for this example you would get: Level 4. Claim 123456 - details Level 4. 200, 100, 50 Level 3. 100, 0, 0 Issues - no customer signature found etc etc Then you would get the next Claimno listed if any more under level 4 OR you would get Level 3 heading and the Claimno's found under this max level heading. etc etc. Each claim only ever appears once on the report, under its Max result heading. The summary of the report needs to be all the values grouped by levels 0-5 (therefore both full and partial payments added together per level). I can do this successfully via a subreport in the report footer, but this is what will not print and produces the error. I have tried to do it by not using another subreport, but I cant get it to show the levels and totals correctly. I hope this is clearer? Sorry it is not very easy to explain... "BruceM" wrote: Sample data and a description of the tables and relationships may help, as I really don't understand the situation you are describing. "hughess7" wrote in message ... |
#19
|
|||
|
|||
cannot open any more databases
I understand relational design etc, been using Access for years believe it or
not! I understand about primary keys and foreign keys and normalisation. Yes there is a pattern in this case - it is the level assigned to an issue, assigned to a claim. Anyway, not to worry - I spent ages just writing these emails to try and explain the situation and I have wasted a good proportion of today too trying to redo the report in a slightly different way but the end result is always the same error. What I did discover in the process of doing so though is it is not the fact that I put the subreport in the main report footer that causes the problem, or anything to do with having a master / child link specified. If I simplify the subreport to just list the claims with maxoflevel grouping, this prints ok from the main reports footer - without any link. It is when I try to add the 2nd level of results that the error occurs. So, I have come to the conclusion it is this causing the problem. I think I know why too... it is because both levels are derived from the payment claim details (same table record) using queries to find max level and 2nd level and then joining them together with a union query. I think if I split the database design further and actually have a foreign table just for the claim financial info then I can perhaps achieve what I want. This database was inherited from a previous employee and I have tweaked it as best I can to make changes the company want, which has been a huge re-development to be honest, but timescales are tight and there was not time to start from scratch... I still have lots to do and it needs to be 'live' by the end of next month, including testing! It seems overkill to make these changes just to get this report working, when every other aspect of the database (which is a lot of work!) is functioning as desired so far... especially when there is no guarantee my idea would work. So, I just wanted to tell you my findings and say thanks again for trying to help... "BruceM" wrote: The main table has a primary key, which is related to a foreign key in a child table, just as your EmployeeID in your individual payroll records relates to EmployeeID in your main employee record. If your employee record is 9999, each individual record of your pay will contain the number 9999 in the related field, thus associating the record of each of your paychecks to you and nobody else. I have been trying to determine if a similar situation exists in your report/subreport. If the two are based on unrelated recordsets I don't see how it could produce a meaningful report, even if it doesn't crash. If they are related properly, try substituting a simplified version of the subform, with just one or two controls. If it runs properly, keep building the subreport. Conversely, use a copy of the existing report. Remove all but one bound control. If that works, use another copy that has just a few controls. Keep building until you find the problem. Good luck. "hughess7" wrote in message ... That is why I didn't put it all in the original posting as I didn't want to confuse the issue but I was trying to show you that the Issues are not just a simple lookup table. Yes as I said at the beginning I think that is partly my problem - that the two don't relate exactly, as in not the same structure / layout format and could be causing the issue. BUT, as also previously mentioned I have already tried your suggestion of doing a query so they can relate in the same way but the error still occurred anyway! I think to be honest it is not to do with layout... I think it is because the report becomes too complicated and uses too many open connections (?) - different subreports using different queries based on the same table data etc maybe, I don't know. Anyway, thanks for your time and trying to understand it. It is very frustrating but I'll just stick with printing them seperately for now... Thanks again... "BruceM" wrote: I'm sorry, but I cannot sort out what is going on, and have a big project through the rest of the week, so will not have much time to put into this for a while. There are several more tables and a lot more variables than in previous postings, and I can't tell what is relevant. For instance, I can't see if translations enter into the current problem or not. I suspect that the problem occurs because you are trying to print a subreport with a Record Source that does not relate to the main report's Record Source. You can print the subreport by itself, and you can print the main report without the subreport, so it seems the two are not communicating with each other when they are set up as report/subreport. This may be getting overcomplicated. I think the main question is: What is the link between the main report and the subreport? A related question: Can you build a query that includes the main report's data and the subreport's data, and have it present the data in a way that makes sense? I'm not saying this query is necessarily to be anything other than an experiment, but rather trying to determine if the two sets of data can interact properly. "hughess7" wrote in message ... Sorry, like I said it is complicated and not easy explaining the structure - I only gave you a cutdown version to try and show the relationship between Claims and Issues... but there are a few other factors that influence why it is designed this way... All these Issues relate to a table of Standards so it is not just a lookup table, one standard can have many Issues (stdRef being the primary key). Also we are dealing with a multi-country/language system so each of these tables also has a translation table linked to them (one to many). So the IssueCode DOES have to be stored against the claim and not the result (surely this would not be normalised anyway if not?). IssueCode (Primary Key: IssueCode) Currently there are 113 Issues Category (ID of area it falls into eg reception, workshop etc - seperate category table) Result (0-5) (seperate result lookup table giving text desc of result) Readings (True/False) - used in code to capture extra variables by the user) Comments (True/False - used in code to force user to enter further mandatory info req'd) StdRef (ID link to seperate Standards table, one standard can have many Issues ) Standards (Primary Key: StdRef) Currently 40 stds StdRef (autonumber) Std_No (customers text Ref) The reports are produced in local language (+ English) so we have one report per country as all the headings are hard-coded into each report too in local language. The translation tables have a Primary key of Country, Language and Code and are used as lookup tables to get the correct country translations (some countries have more than one language). The translated Issues table, also has a translated Action against them. The reason for all this is to show a dealer which standards he has not complied to, what the actual issues were so he can have an action plan to resolve them, and see the financial consequences (chargeback) broken down by each level. Each level is treated differently, some are more serious than others with level 5 being the most serious. The financial consequences differ depending on what max levels are found. A claim can have a partial chargeback hence the need for two different levels, the max and the 2nd highest level (which defaults to 0 if no 2nd level). You don't need to worry about how I work out these values though as this part is all working and is done via a set of queries (this is the financial subreport, split by claimno). Yes DealerClaimIssues has a key: Country (linked to country table) DealerCode (linked to Dealer table for language etc) ReviewDate (to ensure we have the right activity - there can be more than 1 a year) ClaimNo (linked to DealerClaim table which has same key excluding Issue Code) IssueCode (linked to Issue Codes which in turn links to Issue Translations table) Additional fields a Translated Issue Text (combination of std text plus user comments and possible additional variables) The top level grouping of the report is Level (result), NOT Issue. It is the Max result which comes from the issues found. eg 0 to 5. eg Claim 123456 may have 20 issues assigned to it. Each of these issues will have a corresponding level (0 to 5). If max level of that claim is a level 5, there will be no partial chargeback so the original total amounts of labour etc will be chargeback (100%). If level 4 or below is the max level found, it can have partial chargeback. The end user creates these partial labour, parts and sublet amounts against the claim header (DealerClaim). If these amounts are not 0 then when the report is produced it assigns these partial values to the max level (which could be between a 0 and 4), then the remainder of the original amounts has to be assigned by the system to the next level down. So if a Level 4 is the max Issue, then were Issues found with a level 3 - if so these become the 2nd level, if not was there a level 2 Issue, if not a level 1 issue and if no other issues then the remainder gets assigned to Level 0. To present this on a report a claim eg 123456 can have two levels - eg a level four with partial amounts and a level 3 with the remainder of the original amounts. BOTH of these amounts would be displayed UNDER Level 4 HEADING, with a list of ALL of the translated Issue text below (result desc sort order but not shown against each Issue). Another way of doing this would be to display Claim 123456 TWICE on the report - once under Level 4 heading and again under Level 3 heading. I suspect that way I could summarise the report properly without any errors. BUT i was trying to avoid doing this to save paper (I would have to repeat all the Issues found twice too) and also to easily identify to the dealer how any one claim has been broken down if it is a partial chargeback. It works perfectly as desired in print preview, but an error occurs if you try to print and it does not print the summary subreport. For now I have deleted the summary from the main report and will have to print them as two seperate reports. I just don't get continuous page numbers this way... "BruceM" wrote: IssueCode is a lookup table? That is, its only purpose is to provide data for IssueCode in DealerClaimsIssues? Does DealerClaimsIssues have a primary key? I think I would have stored Result rather than IssueCode in DealerClaimsIssues, as it is the field you need to work with directly. As I understand, you are looking for the highest Result value amongst the Issues associated with a claim. I still do not understand the situation fully, but I think you will need to combine DealerClaims and DealerClaimsIssues into a query, and use that query as the report's Record Source. As I understand, the top level of the grouping hierarchy is Issue. To group by this value, it needs to be available in the main report's Record Source. You want to see Issue 4 at the top, followed by claims that have an Issue of 4. Where I lose you completely is the logic by which "Labour, Parts and Sublet values of the claim get assigned to this level", while other fields are associated with Level 3, nor can I picture how other levels come into play. I do not understand the following statement at all: "Each Issue you can apply is stored in the table IssueCodes and has a result (0-5) against each individual issue". Part of the difficulty in understanding is that I do not see how the tables are related in all cases. I think I can see that DealerClaimsIssues is related to DealerClaims by ClaimNo, but is IssueCodes related to DealerClaimsIssues, or what exactly? At first I thought it was a lookup table, but now I wonder. If you identify primary key fields and linking fields it may help. "hughess7" wrote in message ... Thanks, I will try to show you a cut down of the tables data structure etc ... Tables: [DealerClaims] ClaimNo LabourPD PartsPD SubletPD PartialLabour PartialParts PartialSublet [DealerClaimsIssues] ClaimNo IssueCode [IssueCodes] IssueCode Result So, you have a claim header record which has claim values stored in |
#20
|
|||
|
|||
cannot open any more databases
OK. Sorry I couldn't have been more helpful. Good luck with the project.
"hughess7" wrote in message ... I understand relational design etc, been using Access for years believe it or not! I understand about primary keys and foreign keys and normalisation. Yes there is a pattern in this case - it is the level assigned to an issue, assigned to a claim. Anyway, not to worry - I spent ages just writing these emails to try and explain the situation and I have wasted a good proportion of today too trying to redo the report in a slightly different way but the end result is always the same error. What I did discover in the process of doing so though is it is not the fact that I put the subreport in the main report footer that causes the problem, or anything to do with having a master / child link specified. If I simplify the subreport to just list the claims with maxoflevel grouping, this prints ok from the main reports footer - without any link. It is when I try to add the 2nd level of results that the error occurs. So, I have come to the conclusion it is this causing the problem. I think I know why too... it is because both levels are derived from the payment claim details (same table record) using queries to find max level and 2nd level and then joining them together with a union query. I think if I split the database design further and actually have a foreign table just for the claim financial info then I can perhaps achieve what I want. This database was inherited from a previous employee and I have tweaked it as best I can to make changes the company want, which has been a huge re-development to be honest, but timescales are tight and there was not time to start from scratch... I still have lots to do and it needs to be 'live' by the end of next month, including testing! It seems overkill to make these changes just to get this report working, when every other aspect of the database (which is a lot of work!) is functioning as desired so far... especially when there is no guarantee my idea would work. So, I just wanted to tell you my findings and say thanks again for trying to help... "BruceM" wrote: The main table has a primary key, which is related to a foreign key in a child table, just as your EmployeeID in your individual payroll records relates to EmployeeID in your main employee record. If your employee record is 9999, each individual record of your pay will contain the number 9999 in the related field, thus associating the record of each of your paychecks to you and nobody else. I have been trying to determine if a similar situation exists in your report/subreport. If the two are based on unrelated recordsets I don't see how it could produce a meaningful report, even if it doesn't crash. If they are related properly, try substituting a simplified version of the subform, with just one or two controls. If it runs properly, keep building the subreport. Conversely, use a copy of the existing report. Remove all but one bound control. If that works, use another copy that has just a few controls. Keep building until you find the problem. Good luck. "hughess7" wrote in message ... That is why I didn't put it all in the original posting as I didn't want to confuse the issue but I was trying to show you that the Issues are not just a simple lookup table. Yes as I said at the beginning I think that is partly my problem - that the two don't relate exactly, as in not the same structure / layout format and could be causing the issue. BUT, as also previously mentioned I have already tried your suggestion of doing a query so they can relate in the same way but the error still occurred anyway! I think to be honest it is not to do with layout... I think it is because the report becomes too complicated and uses too many open connections (?) - different subreports using different queries based on the same table data etc maybe, I don't know. Anyway, thanks for your time and trying to understand it. It is very frustrating but I'll just stick with printing them seperately for now... Thanks again... "BruceM" wrote: I'm sorry, but I cannot sort out what is going on, and have a big project through the rest of the week, so will not have much time to put into this for a while. There are several more tables and a lot more variables than in previous postings, and I can't tell what is relevant. For instance, I can't see if translations enter into the current problem or not. I suspect that the problem occurs because you are trying to print a subreport with a Record Source that does not relate to the main report's Record Source. You can print the subreport by itself, and you can the main report without the subreport, so it seems the two are not communicating with each other when they are set up as report/subreport. This may be getting overcomplicated. I think the main question is: What is the link between the main report and the subreport? A related question: Can you build a query that includes the main report's data and the subreport's data, and have it present the data in a way that makes sense? I'm not saying this query is necessarily to be anything other than an experiment, but rather trying to determine if the two sets of data can interact properly. "hughess7" wrote in message ... Sorry, like I said it is complicated and not easy explaining the structure - I only gave you a cutdown version to try and show the relationship between Claims and Issues... but there are a few other factors that influence why it is designed this way... All these Issues relate to a table of Standards so it is not just a lookup table, one standard can have many Issues (stdRef being the primary key). Also we are dealing with a multi-country/language system so each of these tables also has a translation table linked to them (one to many). So the IssueCode DOES have to be stored against the claim and not the result (surely this would not be normalised anyway if not?). IssueCode (Primary Key: IssueCode) Currently there are 113 Issues Category (ID of area it falls into eg reception, workshop etc - seperate category table) Result (0-5) (seperate result lookup table giving text desc of result) Readings (True/False) - used in code to capture extra variables by the user) Comments (True/False - used in code to force user to enter further mandatory info req'd) StdRef (ID link to seperate Standards table, one standard can have many Issues ) Standards (Primary Key: StdRef) Currently 40 stds StdRef (autonumber) Std_No (customers text Ref) The reports are produced in local language (+ English) so we have one report per country as all the headings are hard-coded into each report too in local language. The translation tables have a Primary key of Country, Language and Code and are used as lookup tables to get the correct country translations (some countries have more than one language). The translated Issues table, also has a translated Action against them. The reason for all this is to show a dealer which standards he has not complied to, what the actual issues were so he can have an action plan to resolve them, and see the financial consequences (chargeback) broken down by each level. Each level is treated differently, some are more serious than others with level 5 being the most serious. The financial consequences differ depending on what max levels are found. A claim can have a partial chargeback hence the need for two different levels, the max and the 2nd highest level (which defaults to 0 if no 2nd level). You don't need to worry about how I work out these values though as this part is all working and is done via a set of queries (this is the financial subreport, split by claimno). Yes DealerClaimIssues has a key: Country (linked to country table) DealerCode (linked to Dealer table for language etc) ReviewDate (to ensure we have the right activity - there can be more than 1 a year) ClaimNo (linked to DealerClaim table which has same key excluding Issue Code) IssueCode (linked to Issue Codes which in turn links to Issue Translations table) Additional fields a Translated Issue Text (combination of std text plus user comments and possible additional variables) The top level grouping of the report is Level (result), NOT Issue. It is the Max result which comes from the issues found. eg 0 to 5. eg Claim 123456 may have 20 issues assigned to it. Each of these issues will have a corresponding level (0 to 5). If max level of that claim is a level 5, there will be no partial chargeback so the original total amounts of labour etc will be chargeback (100%). If level 4 or below is the max level found, it can have partial chargeback. The end user creates these partial labour, parts and sublet amounts against the claim header (DealerClaim). If these amounts are not 0 then when the report is produced it assigns these partial values to the max level (which could be between a 0 and 4), then the remainder of the original amounts has to be assigned by the system to the next level down. So if a Level 4 is the max Issue, then were Issues found with a level 3 - if so these become the 2nd level, if not was there a level 2 Issue, if not a level 1 issue and if no other issues then the remainder gets assigned to Level 0. To present this on a report a claim eg 123456 can have two levels - eg a level four with partial amounts and a level 3 with the remainder of the original amounts. BOTH of these amounts would be displayed UNDER Level 4 HEADING, with a list of ALL of the translated Issue text below (result desc sort order but not shown against each Issue). Another way of doing this would be to display Claim 123456 TWICE on the report - once under Level 4 heading and again under Level 3 heading. I suspect that way I could summarise the report properly without any errors. BUT i was trying to avoid doing this to save paper (I would have to repeat all the Issues found twice too) and also to easily identify to the dealer how any one claim has been broken down if it is a partial chargeback. It works perfectly as desired in print preview, but an error occurs if you try to print and it does not print the summary subreport. For now I have deleted the summary from the main report and will have to print them as two seperate reports. I just don't get continuous page numbers this way... "BruceM" wrote: IssueCode is a lookup table? That is, its only purpose is to provide data for IssueCode in DealerClaimsIssues? Does DealerClaimsIssues have a primary key? I think I would have stored Result rather than IssueCode in DealerClaimsIssues, as it is the field you need to work with directly. As I understand, you are looking for the highest Result value amongst the Issues associated with a claim. I still do not understand the situation fully, but I think you will need to combine DealerClaims and DealerClaimsIssues into a query, and use that query as the report's Record Source. As I understand, the top level of the grouping hierarchy is Issue. To group by this value, it needs to be available in the main report's Record Source. You want to see Issue 4 at the top, followed by claims that have an Issue of 4. Where I lose you completely is the logic by which "Labour, Parts and Sublet values of the claim get assigned to this level", while other fields are associated with Level 3, nor can I picture how other levels come into play. I do not understand the following statement at all: "Each Issue you can apply is stored in the table IssueCodes and has a result (0-5) against each individual issue". Part of the difficulty in understanding is that I do not see how the tables are related in all cases. I think I can see that DealerClaimsIssues is related to DealerClaims by ClaimNo, but is IssueCodes related to DealerClaimsIssues, or what exactly? At first I thought it was a lookup table, but now I wonder. If you identify primary key fields and linking fields it may help. "hughess7" wrote in message ... Thanks, I will try to show you a cut down of the tables data structure etc ... Tables: [DealerClaims] ClaimNo LabourPD PartsPD SubletPD PartialLabour PartialParts PartialSublet [DealerClaimsIssues] ClaimNo IssueCode [IssueCodes] IssueCode Result So, you have a claim header record which has claim values stored in |
Thread Tools | |
Display Modes | |
|
|