Index Report Queries/Report not functioning as expected

[LeslieJ via AccessMonster.com]

I have a database that records the documents held by my department.

We have a number o fdifferent binders in our department and the database
captures the location of each document.

Some documents also have associated documents. This means that the
associated/related documents are not filed in the binder on their own, they
are filed with the main/master document. The database indicates if a
document is filed with another by typing the master document's number in a
field called [FiledWith] on the related documents record. So for example: A
document type SOP could have a CS and/or a Dir and/or Traning filed with it.

I have created a report that acts as an index for each binder.

The SQL for the main/master documents query called qryIndices is listed below.
The SQL for the associated document information called Related Documents for
Indicies is also listed below.

The report, Index Report, has a report header that has the binder's
information in it. A page header that has the labels for "Number," and
"Title," as well as the fields [Master Doc ID] and [Master Ver ID]. Next I
have the report sorted by document type using the [DocumentType] field, and a
switch function that puts the SOPs,CSs,Training, and Dirs first and
everything else to follow. In the detail I have the [DocumentNumber] and
[Document Title] fields. Also in the detail is the subreport for the
associated documents. The subreport is linked tot he main report using the
document ID's and the version ID's (the two factors that make the document
unique).

When the Index Report is run the SOP, CS, and Training for a specific number
are showing up as "filed" together. However, the directives that should be
with the SOP are at the bottom of the list, and it is showing all the copies
of that directive that we hold, regardless if it's filed in that binder or
not. Also it seems to be duplicating the number of copies that we hold.

I need to find a way to have the directives properly dispensed within the
index report.

If anyone has any suggestion I would be forever grateful!

Merry Christmas!

SQL for qryIndices:
SELECT ControlledDocuments.[Document Identifier] AS [Master Doc ID],
VersionInfo.VerID AS [Master Ver ID], ControlledDocuments.DocumentType,
ControlledDocuments.DocumentNumber, [Document Location Information].[Holder
Number], VersionInfo.DocumentTitle, [Document Location Information].[Filed
With], DocumentLocations.[LocationOfDocument Identifier], [Document Location
Information].Location, [Document Location Information].Recalled, VersionInfo.
Status
FROM ((ControlledDocuments INNER JOIN VersionInfo ON ControlledDocuments.
[Document Identifier] = VersionInfo.NumID) INNER JOIN [Document Location
Information] ON VersionInfo.VerID = [Document Location Information].VersionID)
INNER JOIN DocumentLocations ON [Document Location Information].Location =
DocumentLocations.LocationOfDocument
WHERE (((DocumentLocations.[LocationOfDocument Identifier])=[Forms]![Preview
Index Report]![txtBinder]) AND (([Document Location Information].Recalled)=No)
AND ((VersionInfo.Status)="Current"))
ORDER BY DocumentLocations.[LocationOfDocument Identifier];

SQL for Related Documents for Indices is:
SELECT VersionInfo.NumID AS [Master NumID], VersionInfo.VerID AS [Master
VerID], VersionInfo_1.NumID AS [Child NumID], VersionInfo_1.VerID AS [Child
VerID], ControlledDocuments_1.DocumentNumber, VersionInfo_1.DocumentTitle,
ControlledDocuments_1.DocumentType, qryIndices.[LocationOfDocument Identifier]
, VersionInfo.Status, qryIndices.[Filed With]
FROM ((ControlledDocuments INNER JOIN VersionInfo ON ControlledDocuments.
[Document Identifier] = VersionInfo.NumID) INNER JOIN qryIndices ON
ControlledDocuments.DocumentNumber = qryIndices.[Filed With]) INNER JOIN
(VersionInfo AS VersionInfo_1 INNER JOIN ControlledDocuments AS
ControlledDocuments_1 ON VersionInfo_1.NumID = ControlledDocuments_1.
[Document Identifier]) ON qryIndices.[Master Doc ID] = ControlledDocuments_1.
[Document Identifier]
WHERE (((qryIndices.[LocationOfDocument Identifier])=[Forms]![Preview Index
Report]![txtBinder]) AND ((VersionInfo.Status)="Current"));

 

[Allen Browne]

The structure you have is very flexible, but (as you found) not easy to
report on.

One if the issues is that if a documents is 'FiledWith' its parent document,
it is also possible that the parent is 'FileWith' a grandparent, and so on,
through many possible generations. There is also the possiblity of infinite
recursion (e.g. where a document is recorded its own grandparent), so you
can never resolve that for the report.

One approach is to set a limit on the number of generations you permit, and
write code the resolves the documents into a non-normalized table that
tracks them back. When someone requests this report, you exeute the code the
populate this temporary table, and display the report if everything resolved
or list the ones that don't resolve and ask the user to fix them.

The SQL language is not good at handing this kind of data, though I hear
that the most recent SQL Server (Express?) has some mechanisms for handing
it that Access doesn't.

If you want to read further on ways to handle this kind of data with SQL,
here's some links:

http://www.intelligententerprise.com/001020/celko.shtml
http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html
http://www.dbmsmag.com/9606d06.html

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a database that records the documents held by my department.

We have a number o fdifferent binders in our department and the database
captures the location of each document.

Some documents also have associated documents. This means that the
associated/related documents are not filed in the binder on their own,
they
are filed with the main/master document. The database indicates if a
document is filed with another by typing the master document's number in a
field called [FiledWith] on the related documents record. So for example:
A
document type SOP could have a CS and/or a Dir and/or Traning filed with
it.

I have created a report that acts as an index for each binder.

The SQL for the main/master documents query called qryIndices is listed
below.
The SQL for the associated document information called Related Documents
for
Indicies is also listed below.

The report, Index Report, has a report header that has the binder's
information in it. A page header that has the labels for "Number," and
"Title," as well as the fields [Master Doc ID] and [Master Ver ID]. Next
I
have the report sorted by document type using the [DocumentType] field,
and a
switch function that puts the SOPs,CSs,Training, and Dirs first and
everything else to follow. In the detail I have the [DocumentNumber] and
[Document Title] fields. Also in the detail is the subreport for the
associated documents. The subreport is linked tot he main report using
the
document ID's and the version ID's (the two factors that make the document
unique).

When the Index Report is run the SOP, CS, and Training for a specific
number
are showing up as "filed" together. However, the directives that should
be
with the SOP are at the bottom of the list, and it is showing all the
copies
of that directive that we hold, regardless if it's filed in that binder or
not. Also it seems to be duplicating the number of copies that we hold.

I need to find a way to have the directives properly dispensed within the
index report.

If anyone has any suggestion I would be forever grateful!

Merry Christmas!

SQL for qryIndices:
SELECT ControlledDocuments.[Document Identifier] AS [Master Doc ID],
VersionInfo.VerID AS [Master Ver ID], ControlledDocuments.DocumentType,
ControlledDocuments.DocumentNumber, [Document Location
Information].[Holder
Number], VersionInfo.DocumentTitle, [Document Location Information].[Filed
With], DocumentLocations.[LocationOfDocument Identifier], [Document
Location
Information].Location, [Document Location Information].Recalled,
VersionInfo.
Status
FROM ((ControlledDocuments INNER JOIN VersionInfo ON ControlledDocuments.
[Document Identifier] = VersionInfo.NumID) INNER JOIN [Document Location
Information] ON VersionInfo.VerID = [Document Location
Information].VersionID)
INNER JOIN DocumentLocations ON [Document Location Information].Location =
DocumentLocations.LocationOfDocument
WHERE (((DocumentLocations.[LocationOfDocument
Identifier])=[Forms]![Preview
Index Report]![txtBinder]) AND (([Document Location
Information].Recalled)=No)
AND ((VersionInfo.Status)="Current"))
ORDER BY DocumentLocations.[LocationOfDocument Identifier];

SQL for Related Documents for Indices is:
SELECT VersionInfo.NumID AS [Master NumID], VersionInfo.VerID AS [Master
VerID], VersionInfo_1.NumID AS [Child NumID], VersionInfo_1.VerID AS
[Child
VerID], ControlledDocuments_1.DocumentNumber, VersionInfo_1.DocumentTitle,
ControlledDocuments_1.DocumentType, qryIndices.[LocationOfDocument
Identifier]
, VersionInfo.Status, qryIndices.[Filed With]
FROM ((ControlledDocuments INNER JOIN VersionInfo ON ControlledDocuments.
[Document Identifier] = VersionInfo.NumID) INNER JOIN qryIndices ON
ControlledDocuments.DocumentNumber = qryIndices.[Filed With]) INNER JOIN
(VersionInfo AS VersionInfo_1 INNER JOIN ControlledDocuments AS
ControlledDocuments_1 ON VersionInfo_1.NumID = ControlledDocuments_1.
[Document Identifier]) ON qryIndices.[Master Doc ID] =
ControlledDocuments_1.
[Document Identifier]
WHERE (((qryIndices.[LocationOfDocument Identifier])=[Forms]![Preview
Index
Report]![txtBinder]) AND ((VersionInfo.Status)="Current"));