Grouping two fields together ?
in [Reports]
|
Prev: query to update data
Next: Cannot open any more tables
From: LeslieJ via AccessMonster.com on 2 Jul 2008 11:10 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. -- Message posted via http://www.accessmonster.com
From: bhicks11 via AccessMonster.com on 2 Jul 2008 11:36 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 LeslieJ wrote: >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. -- Message posted via http://www.accessmonster.com
From: LeslieJ via AccessMonster.com on 2 Jul 2008 12:06 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! bhicks11 wrote: >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. -- Message posted via http://www.accessmonster.com
From: KARL DEWEY on 2 Jul 2008 14:02 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; -- KARL DEWEY Build a little - Test a little "LeslieJ via AccessMonster.com" wrote: > 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. > > -- > Message posted via http://www.accessmonster.com > >
From: LeslieJ via AccessMonster.com on 2 Jul 2008 15:17
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. KARL DEWEY wrote: >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. -- Message posted via http://www.accessmonster.com |