cannot open any more databases

[hughess7]

Hi all

Please help!! I can't get rid of an error that keeps coming up when i print
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

 

[Arvin Meyer MVP]

Have a look at the following Microsoft KB article:

http://support.microsoft.com/kb/890931/

 

[hughess7]

Hi, we have SP3 installed so should be ok for that issue I think?

This is driving me potty... :-(

 

[BruceM]

Any question about an error needs to be specific. What error are you
getting?

 

[hughess7]

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...

 

[Arvin Meyer MVP]

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.

 

[hughess7]

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.

 

[BruceM]

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.

 

[Crossh]

Hi all

Please help!! I can't get rid of an error that keeps coming up when i print
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. IfI
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 appearat
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

I'm not sure if this is the same, but I had this problem once when I
created a report with 8 or so sub reports. The work around was to
delete some of the subreports and put them all in one subreport,
called from the main report.

 

[hughess7]

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]

Sample data and a description of the tables and relationships may help, as I
really don't understand the situation you are describing.

 

[hughess7]

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]

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.

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...

Sample data and a description of the tables and relationships may help,
as I
really don't understand the situation you are describing.

 

[hughess7]

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 are:
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...

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.

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...

Sample data and a description of the tables and relationships may help,
as I
really don't understand the situation you are describing.

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?


:

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.

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


:

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


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?

Hi, we have SP3 installed so should be ok for that issue I
think?

This is driving me potty... :-(


:

Have a look at the following Microsoft KB article:

http://support.microsoft.com/kb/890931/

 

[BruceM]

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.

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 are:
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...

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.

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...

:

Sample data and a description of the tables and relationships may
help,
as I
really don't understand the situation you are describing.

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?


:

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.

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


:

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


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?

message
Hi, we have SP3 installed so should be ok for that issue I
think?

This is driving me potty... :-(


:

Have a look at the following Microsoft KB article:

http://support.microsoft.com/kb/890931/

 

[hughess7]

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...

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.

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 are:
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...

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.

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...

:

Sample data and a description of the tables and relationships may
help,
as I
really don't understand the situation you are describing.

news:2AB80BC9-B35A-4AAF-B312-A825C5ED680A@microsoft.com...

 

[BruceM]

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.

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...

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.

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 are:
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...

:

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.

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...

:

Sample data and a description of the tables and relationships may
help,
as I
really don't understand the situation you are describing.

news:2AB80BC9-B35A-4AAF-B312-A825C5ED680A@microsoft.com...

 

[hughess7]

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...

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.

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...

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.

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 are:
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...

:

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.

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

 

[BruceM]

OK. Sorry I couldn't have been more helpful. Good luck with the project.

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...

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.

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...

:

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.

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 are:
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...

:

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.

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

 

[BruceM]

You've got a lot of damned nerve sniping at me. First of all, I do not see
your posting in this thread. Second, I did in fact suggest looking at the
queries. In the end the OP decided not to pursue it further, which is why I
did not pursue it further, unlike you who now see this as an opportunity to
take the OP's money in exchange for your help. Third, I offer a lot of help
in these groups. I don't always get it right, but often I do provide
assistance, and I have never attempted to bill for my services here.

I think you're upset with me for jumping in when you try to troll for work
in this thread. You have not helped your cause with this sophomoric attack,
which comes across as nothing more than a vain and pathetic attempt to
burnish your own corroded image.

.....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.

Did you ever read my post? You're on the right path now looking at the
queries that go into your report. If you can eliminate the queries you
describe above, maybe that will be enough to get your report to run.
BruceM wasted alot of your time!!!

Steve
(e-mail address removed)

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...

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.

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...

:

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.

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 are:
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...

:

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.

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