cannot open any more databases

[BruceM]

Correction: When you try to troll for work "in these groups", not "in this
thread".

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

 

[hughess7]

What post? Wasn't aware you had made one...

I gave up on trying to sort this anyway, trouble is it is now affecting my
final report too! I am trying to produce the one very large report in access,
consisting of lots of subreports. The ONLY reason is to get continuous page
numbers. I have had to split into three reports to get this all to work
without the 'cannot open any more database error' occurring. I also get an
OLE server error too with these reports if I try to combine into one report.

So I will work without pages numbering for now, or look at using adobe maybe
to do batch processing and page numbering maybe at a later date. I don't have
time to look at this issue any more... and for reference - I don't wish to
pay anyone to solve it before anyone asks.

 

[Gigamite]

Did you ever read my post?

No, almost no one saw any of your posts for nearly two weeks. Look for
yourself. You only have one post in this thread, the one I'm replying to.

http://www.microsoft.com/office/community/en-us/default.mspx?&lang=en&cr=US
&sloc=en-us&dg=microsoft.public.access&p=1&tid=619b51a6-8e96-49e9-9b68-87f8
2f522576

http://www.microsoft.com/office/community/en-us/default.mspx?pg=2&p=1&tid=61
9b51a6-8e96-49e9-9b68-87f82f522576&dg=microsoft.public.access&lang=en&cr=US&
sloc=en-us

Almost all posts soliciting products and services (commonly referred to
as spam) never showed up on Microsoft's news server,
msnews.microsoft.com, or in Microsoft's Office Discussion Groups
(http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.p
ublic.access&lang=en&cr=US) during the test period between 6 PM Eastern
on June 18, 2009 and 8 PM Eastern on July 1, 2009, although you'll find
these posts on every other news server that doesn't filter out spam.

Do you think it's a coincidence none of your posts showed up on
Microsoft's servers during the test? Which btw most of us use for
posting questions and answers for the Access newsgroups.

You may want to alter your posts in these newsgroups so you aren't
soliciting your services. You know, in case Microsoft makes its new
spam filter test permanent, cuz it looks pretty successful to me. ;^}

BruceM
wasted alot of your time!!!

That's uncalled for. These newsgroups are the place to freely discuss
technical issues with our peers, and we don't always guess what's wrong
and how to fix it right the first time. By discussing the technical
issues here in a group atmosphere, we can all learn, even if it's what
doesn't work.

 

[John... Visio MVP]

Steve has dillusions of adequacy and that people want his help. I do beieve
he thinks his posts are being ignored, but I do not believe he actually
posted anything.

Microsoft is very relucant to delete posts, so that I doubt that Microsoft
actually deleted any of his posts. Just consider how may posts from AK and
the SQL choir that got through. Microsoft leaves it up to the users to
monitor the newsgroups and except for one person, requests to desist are
usually respected when the situation is explained.

John... Visio MVP

 

[Gigamite]

I do
beieve he thinks his posts are being ignored, but I do not believe he
actually posted anything.

Steve is referring to his post, #26 in this thread here

http://groups.google.com/group/microsoft.public.access/browse_frm/thread/e
6768b75cc0131e8/627dc43c271e8c67?tvc=1#627dc43c271e8c67

msgid (e-mail address removed)

You don't see it on your news server, right? You're looking at either
msnews.microsoft.com or news.microsoft.com. Use a different news server
and you'll find it. (Not Google's. It's gone south. Not dependable
anymore.)

Microsoft is very relucant to delete posts, so that I doubt that
Microsoft actually deleted any of his posts.

Microsoft didn't delete any of Steve's posts. His posts between 6 P.M.
Eastern June 18, 2009 and 8 P.M. July 1, 2009 never arrived on
Microsoft's servers. Try to find Steve's posts during that period on
Microsoft's news servers or in Microsoft's Office Discussion Groups.
None of his posts were he solicited his services are there. You'll only
find his posts during the test period where he didn't solicit. How many
do you think that is? (I'm sure you'll get it on your first guess.) ;^}

Here are several examples of threads with his solicitation posts that
made it to Google Groups and other news servers but not to Microsoft's
news server or to Microsoft's Office Discussion Groups. Can you find
any of his posts in these threads on Microsoft's servers when he was
soliciting?

http://groups.google.com/group/microsoft.public.access.tablesdbdesign/brows
e_frm/thread/831f1b6ca23fa490/1dcbf68bedaffab6#1dcbf68bedaffab6

http://groups.google.com/group/microsoft.public.access.tablesdbdesign/brows
e_frm/thread/3f25964a845eebf7/beab698d800c752c#beab698d800c752c

http://groups.google.com/group/microsoft.public.access/browse_frm/thread/ed
5fa3095cb4b5bf/53daf9ce0038e710#53daf9ce0038e710

http://groups.google.com/group/microsoft.public.access.gettingstarted/brows
e_frm/thread/5cf4457c97f7cfed/8cf299a27065ef8f#8cf299a27065ef8f

http://groups.google.com/group/microsoft.public.access/browse_frm/thread/1a
ca1d196395c506/7586c304b71435ec#7586c304b71435ec

http://groups.google.com/group/microsoft.public.access.tablesdbdesign/brows
e_frm/thread/a1628c302b9ff7e8/adf7d14e452d9d43#adf7d14e452d9d43

http://groups.google.com/group/microsoft.public.access.tablesdbdesign/brows
e_frm/thread/cfdf19ebe9f46bc3/6aef1625dd17c837#6aef1625dd17c837

http://groups.google.com/group/microsoft.public.access.tablesdbdesign/brows
e_frm/thread/74aff256cbc8dc13/de04f0d72a1ebc05#de04f0d72a1ebc05

Just consider how may posts
from AK and the SQL choir that got through.

AK didn't post to the Access newsgroups during the test period. Sorry,
I don't know what the SQL choir is so I can't address anything about
their posts.

 

[o;;]

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

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.