Grouping two fields together ?

[LeslieJ via AccessMonster.com]

Hi, I've been trying to search for an answer, and haven't come up with
anything. I'm hoping it's possible.

I have three fields on a report: [DocumentNumber], [DocumentTitle], and
[Filed With]. The [Filed With] field will be a document number that will
coincide with another record's [DocumentNumber]. I am hoping that there is a
way that I can group these two fields together, so that when the user prints
the report, the documents that are all filed together will appear together in
the list.

Thanks in advance.

 

[bhicks11 via AccessMonster.com]

Hi LeslieJ,

How are you relating them to each other. What do the first DocumentNumber
and the second DocumentNumber have in common that you can relate?

Bonnie
http://www.dataplus-svc.com

Hi, I've been trying to search for an answer, and haven't come up with
anything. I'm hoping it's possible.

I have three fields on a report: [DocumentNumber], [DocumentTitle], and
[Filed With]. The [Filed With] field will be a document number that will
coincide with another record's [DocumentNumber]. I am hoping that there is a
way that I can group these two fields together, so that when the user prints
the report, the documents that are all filed together will appear together in
the list.

Thanks in advance.

 

[LeslieJ via AccessMonster.com]

Hi Bonnie,

Right now the two fields are not related at all.

When a document is filed with another document, it is just typed into the
field [Filed With], however, it is typed exactly the same as the
[DocumentNumber] that is it associated with looks.

If they are to be related, would there be problems because not every document
is filed with another?

Thank you!

Hi LeslieJ,

How are you relating them to each other. What do the first DocumentNumber
and the second DocumentNumber have in common that you can relate?

Bonnie
http://www.dataplus-svc.com

Hi, I've been trying to search for an answer, and haven't come up with
anything. I'm hoping it's possible.

[quoted text clipped - 7 lines]

Thanks in advance.

 

[KARL DEWEY]

I suggest making [DocumentNumber] the primary key. Then open relationships
and place two copies of the table in the window (the second copy will have a
sufix '_1' on its name). Click on the [DocumentNumber] in the first table
and drag to the [Filed With] field of the second table. Select Enforce
Referential Integerity and Cascade Update Related Fields. Save.

In your query for the report you will need two copies of the table like in
relationships.
SELECT Documents.DocumentNumber, Documents.DocumentTitle,
Documents_1.DocumentNumber, Documents_1.DocumentTitle
FROM Documents LEFT JOIN Documents AS Documents_1 ON Documents.[Filed With]
= Documents_1.DocumentNumber;

 

[LeslieJ via AccessMonster.com]

Thanks for your help Karl,

When I did what you suggested, the query only returned the records that have
a document filed with it. Unfortunately, I need to see all of the records, I
just want to group those that are associated wtih each other together.

This is the SQL statement I have for the query in question:

SELECT ControlledDocuments.DocumentType, ControlledDocuments.DocumentNumber,
VersionInfo.DocumentTitle, [Document Location Information].[Filed With],
[Document Location Information].Location, ControlledDocuments_1.
DocumentNumber
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 ControlledDocuments AS ControlledDocuments_1 ON [Document Location
Information].[Filed With] = ControlledDocuments_1.DocumentNumber
WHERE ((([Document Location Information].Location)="B03") AND (([Document
Location Information].Recalled)=No))
ORDER BY ControlledDocuments.DocumentNumber;

Thanks again.

I suggest making [DocumentNumber] the primary key. Then open relationships
and place two copies of the table in the window (the second copy will have a
sufix '_1' on its name). Click on the [DocumentNumber] in the first table
and drag to the [Filed With] field of the second table. Select Enforce
Referential Integerity and Cascade Update Related Fields. Save.

In your query for the report you will need two copies of the table like in
relationships.
SELECT Documents.DocumentNumber, Documents.DocumentTitle,
Documents_1.DocumentNumber, Documents_1.DocumentTitle
FROM Documents LEFT JOIN Documents AS Documents_1 ON Documents.[Filed With]
= Documents_1.DocumentNumber;

Hi, I've been trying to search for an answer, and haven't come up with
anything. I'm hoping it's possible.

[quoted text clipped - 7 lines]

Thanks in advance.

 

[KARL DEWEY]

I did not review your query throughly but you need to use LEFT JOIN instead
of INNER. With INNER both table have to have a match but with a LEFT only
the left must have data and the other can match or be null.
--
KARL DEWEY
Build a little - Test a little

Thanks for your help Karl,

When I did what you suggested, the query only returned the records that have
a document filed with it. Unfortunately, I need to see all of the records, I
just want to group those that are associated wtih each other together.

This is the SQL statement I have for the query in question:

SELECT ControlledDocuments.DocumentType, ControlledDocuments.DocumentNumber,
VersionInfo.DocumentTitle, [Document Location Information].[Filed With],
[Document Location Information].Location, ControlledDocuments_1.
DocumentNumber
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 ControlledDocuments AS ControlledDocuments_1 ON [Document Location
Information].[Filed With] = ControlledDocuments_1.DocumentNumber
WHERE ((([Document Location Information].Location)="B03") AND (([Document
Location Information].Recalled)=No))
ORDER BY ControlledDocuments.DocumentNumber;

Thanks again.

I suggest making [DocumentNumber] the primary key. Then open relationships
and place two copies of the table in the window (the second copy will have a
sufix '_1' on its name). Click on the [DocumentNumber] in the first table
and drag to the [Filed With] field of the second table. Select Enforce
Referential Integerity and Cascade Update Related Fields. Save.

In your query for the report you will need two copies of the table like in
relationships.
SELECT Documents.DocumentNumber, Documents.DocumentTitle,
Documents_1.DocumentNumber, Documents_1.DocumentTitle
FROM Documents LEFT JOIN Documents AS Documents_1 ON Documents.[Filed With]
= Documents_1.DocumentNumber;

Hi, I've been trying to search for an answer, and haven't come up with
anything. I'm hoping it's possible.

[quoted text clipped - 7 lines]

Thanks in advance.

 

[LeslieJ via AccessMonster.com]

Hi Karl, please have patience with me I'm still pretty new at all of this.

I created the left join, and it worked fabulously!

But now, how do I group it all together on the report? I'm guessing that
it's on the Sorting and Grouping section of the report, but I do not know how
to word the expression so that it brings the ControlledDocuments_1.
DocumentNumber up underneath the ControlledDocuments.DocumentNumber.

Thank you so much, I truly appreciate it.

I did not review your query throughly but you need to use LEFT JOIN instead
of INNER. With INNER both table have to have a match but with a LEFT only
the left must have data and the other can match or be null.

Thanks for your help Karl,

[quoted text clipped - 37 lines]

 

[KARL DEWEY]

Now you are at the 'chicken & egg' point.
You probably want something like this --
Doc # Title Xxxx
Red1 XYZ 123 ---- this is the [Filed With] doc so your query needs
the fields.
Associated Docs ---- these are with the above.
Blue2 ABC Yyyy
Grn4 GHI Kkk

Grouping is on the [Filed With] field.

--
KARL DEWEY
Build a little - Test a little

Hi Karl, please have patience with me I'm still pretty new at all of this.

I created the left join, and it worked fabulously!

But now, how do I group it all together on the report? I'm guessing that
it's on the Sorting and Grouping section of the report, but I do not know how
to word the expression so that it brings the ControlledDocuments_1.
DocumentNumber up underneath the ControlledDocuments.DocumentNumber.

Thank you so much, I truly appreciate it.

I did not review your query throughly but you need to use LEFT JOIN instead
of INNER. With INNER both table have to have a match but with a LEFT only
the left must have data and the other can match or be null.

Thanks for your help Karl,

[quoted text clipped - 37 lines]

Thanks in advance.

 

[KARL DEWEY]

If you can not follow post some raw data and example of how you want the
report to look like.
--
KARL DEWEY
Build a little - Test a little

Hi Karl, please have patience with me I'm still pretty new at all of this.

I created the left join, and it worked fabulously!

But now, how do I group it all together on the report? I'm guessing that
it's on the Sorting and Grouping section of the report, but I do not know how
to word the expression so that it brings the ControlledDocuments_1.
DocumentNumber up underneath the ControlledDocuments.DocumentNumber.

Thank you so much, I truly appreciate it.

I did not review your query throughly but you need to use LEFT JOIN instead
of INNER. With INNER both table have to have a match but with a LEFT only
the left must have data and the other can match or be null.

Thanks for your help Karl,

[quoted text clipped - 37 lines]

Thanks in advance.

 

[LeslieJ via AccessMonster.com]

Hi Karl,

Thanks for your help, I'm sorry I haven't gotten back to you.

I understand what you're telling me to do, it's just that I don't know how to
execute it.

I already have the report grouped by document type using a header, which
might throw a wrench into things.
Here is an example of what I'm hoping my report can look like:
Document Type: [SOP]
[Document A Number] [Document A Title ]
[Associated Document 1A] [Associated Document 1A Title]
[Associated Document 2A] [Associated Document 2A Title]

[Document B Number] [Document B Title ]
[Associated Document 1B] [Associated Document 1B Title]
etc.
So I have my report set up:
Page Header: Labels for Number, and Title
Switch Header: Document Type Label, and [Document Type]
Detail: [ControlledDocuments.DocumentNumber], [VersionInfo.Document Title]

I can't figure out where to place the "Associated fields"
[ControlledDocuments_1.DocumentNumber] and [VersionInfo_1.DocumentTitle].
The query works perfectly.

I really hope that this makes things as clear as mud ) Thanks for your
help again.

If you can not follow post some raw data and example of how you want the
report to look like.

Hi Karl, please have patience with me I'm still pretty new at all of this.

[quoted text clipped - 15 lines]