B
BruceM
Correction: When you try to troll for work "in these groups", not "in this
thread".
thread".
BruceM said: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.
Steve said:.....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)
hughess7 said: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
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