Every record paired with every other record in the same report?

  • Thread starter Thread starter Lucky Man Cree
  • Start date Start date
L

Lucky Man Cree

Is it possible to create the following report in Access 2000? The report
would have two instances of the same field shown on each line of the report.
That is, the report will display a single field (but different record) twice
on the same line. Every record will be paired with every other record in
the table but will not be paired with itself. That is, the report will skip
the line if both instances of the field are equal to one another. An
example of the way I'd like the report to display is shown below. In the
example, each name (Alison, Betty, Candace, Danielle, and Ellen) is a
separate record in the "names" table and query. The names are all stored in
the same "names" field and are not distinguished from each other by any
difference in level or importance. I'm not sure if this type of thing would
be called recursion.

I believe I can do this by creating a subreport but I think, with this
method, the width of the fields would be fixed and there would sometimes be
a gap between the two names. I would like the names to be concatenated so
that there would always be only a single space between them. Is this
possible?

Thanks,
Shannon

Allison Betty
Allison Candace
Allison Danielle
Allison Ellen

Betty Allison
Betty Candace
Betty Danielle
Betty Ellen

Candace Allison
Candace Betty
Candace Danielle
Candace Ellen

Danielle Allison
Danielle Betty
Danielle Candace
Danielle Ellen

Ellen Allison
Ellen Betty
Ellen Candace
Ellen Danielle
 
Hi Shannon,

I think you have a couple of options. I am using tblNames as the name of the
table, and FirstName as the name of the field. "Names" is a reserved word in
Jet 4.0, and "Name" is a reserved word in Access. You should avoid using any
reserved words for things that you assign a name to in Access:

Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266

So, in tblNames, I have two fields:
pkPersonID (Autonumber primary key)
FirstName

I entered the five names you indicated. Create a new query. Dismiss the Add
Table dialog without selecting any tables. In query design view, click on
View > SQL View. You should see the word SELECT highlighted. Copy the
following SQL (Structured Query Language) statement, and paste it into the
SQL window, replacing the default SELECT that is highlighted. The first SQL
statement will give you the names in two fields. The second SQL statement
will give you the names in one field, concatenated together with a single
space:

SELECT tblNames.FirstName, tblNames_1.FirstName
FROM tblNames, tblNames AS tblNames_1
WHERE (((tblNames.FirstName)<>[tblNames_1.FirstName]))
ORDER BY tblNames.FirstName;


SELECT [tblNames.FirstName] & " " & [tblNames_1.FirstName] AS FirstName
FROM tblNames, tblNames AS tblNames_1
WHERE ((([tblNames.FirstName] & " " & [tblNames_1.FirstName])
<>[tblNames_1.FirstName] & " " & [tblNames.FirstName]))
ORDER BY [tblNames.FirstName] & " " & [tblNames_1.FirstName];


You can switch back to the more familiar query design view by clicking on
View > Query Design. Use one of these two queries as the basis for your
report. A slight variation of the second form, which returns two fields,
includes the following:

SELECT [tblNames.FirstName] & " " & [tblNames_1.FirstName] AS FirstName,
tblNames.FirstName AS FirstName1
FROM tblNames, tblNames AS tblNames_1
WHERE ((([tblNames.FirstName] & " " & [tblNames_1.FirstName])
<>[tblNames_1.FirstName] & " " & [tblNames.FirstName]))
ORDER BY [tblNames.FirstName] & " " & [tblNames_1.FirstName];

This version returns the names concatenated with a single space in the
"FirstName" field. It also returns each individual name in the "FirstName1"
field. You could now create a grouped report, where you group on the
FirstName1 field. You can set the visible property for the textbox to No, so
that it will not display. Then add the other field, with the concatenated
names to the detail section.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Is it possible to create the following report in Access 2000? The report
would have two instances of the same field shown on each line of the report.
That is, the report will display a single field (but different record) twice
on the same line. Every record will be paired with every other record in
the table but will not be paired with itself. That is, the report will skip
the line if both instances of the field are equal to one another. An
example of the way I'd like the report to display is shown below. In the
example, each name (Alison, Betty, Candace, Danielle, and Ellen) is a
separate record in the "names" table and query. The names are all stored in
the same "names" field and are not distinguished from each other by any
difference in level or importance. I'm not sure if this type of thing would
be called recursion.

I believe I can do this by creating a subreport but I think, with this
method, the width of the fields would be fixed and there would sometimes be
a gap between the two names. I would like the names to be concatenated so
that there would always be only a single space between them. Is this
possible?

Thanks,
Shannon

Allison Betty
Allison Candace
Allison Danielle
Allison Ellen

Betty Allison
Betty Candace
Betty Danielle
Betty Ellen

Candace Allison
Candace Betty
Candace Danielle
Candace Ellen

Danielle Allison
Danielle Betty
Danielle Candace
Danielle Ellen

Ellen Allison
Ellen Betty
Ellen Candace
Ellen Danielle
 
Thank you, Tom!
So easy, and, although I need to investigate further, it seems to be
exactly what I want.

Shannon
 
Back
Top