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:
[email protected]...