Brain fart - simple

[AvgMoJoe]

Say I have only one table:

Table1
ID Name
1 John
2 Tom
3 Fred
4 Sam
5 Max
6 Wilbur

I want to display a report with 2 groups:
"Red" and "Green"

I know that Red should have 1,2,3 and Green should have 1,4,5

How do I show John on both Red and Green?

I don't care about Wilbur.

 

[MCuthill]

AvgMoJo,

Not sure if this would fit your needs, but in place of a group, would it be sufficient to simply list the members of each group at the bottom of the report? What I am thinking may work is a "Running Total" where a name is added to a string if it meets the criteria.

For example, the "Red" Running Total would add the name (and a preceeding comma, as warranted) if the ID is 1,2 or 3.

If this approach will work for your needs, and you need assistance with the formula's, please advise.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[AvgMoJoe]

Actually that would not work, because I will be using table1 for grouping information from other tables linked to it.

 

[briangriffin]

Wilbur's feelings may be hurt.

I would create a command object and link the other tables to that. Syntax may vary slightly depending on your database, but:

select 'Red', ID, name from yourtable where ID in (1, 2, 3)
union all
select 'Green', ID, name from yourtable where ID in (1, 4, 5)

 

[AvgMoJoe]

A command was exactly what I was thinking I would need to do, but haven't done so yet! Thank you!