list of roomates

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

In a perfect world I could do this:

Select First_Name, Last_Name from table where roomid = this AND ID <> that.

But I have to bind the query to a report that will generate a list of
roomates for each person of a several thousands person list.

My question is: How do I write make the report plug values in for
"this" and "that" so that I can get a list of roomates only (without the
oringally referenced person)?
 
Chris said:
In a perfect world I could do this:

Select First_Name, Last_Name from table where roomid = this AND ID <> that.

But I have to bind the query to a report that will generate a list of
roomates for each person of a several thousands person list.

My question is: How do I write make the report plug values in for
"this" and "that" so that I can get a list of roomates only (without the
oringally referenced person)?
Click to expand...


This question is too far out of context to answer. Is the
above query the record source for a subreport? If so, you
woulf have to use some code in the subreport's detail Format
event to supress the original person's record.

OTOH, from what you have (or maybe haven't) said, I don't
see a reason to use a subreport. Instead, you can use a
main report record source query that Joins the people on the
room ID and group on the person. The person info could then
be in the group header and the rest of them in the detail
section.
 
PERHAPS, the following untested query would work.

SELECT T.FName, T.LastName, Roommates.FName, Roommates.LastName
FROM TableName As T INNER JOIN TableName As Roommates
ON T.RoomId = T2.RoomID
WHERE T.FName <> Roommates.Fname AND
T.LastName <> Roommates.LastName
 
Back
Top