Union Query

[shutls]

Hope someone can point me in the right direction with this -- I have two tables I am trying to combine in a query without getting duplicate records.

One table (Franchise) lists the franchise and its unique ID and the next table (ChannelLineups) linked to it lists the channel lineup for the community.

Currently my SQL statement looks like this:
SELECT DISTINCTROW ChannelLineups.ChannelGroup, ChannelLineups.Channel, ChannelLineups.CallLetters, ChannelLineups.StationName, ChannelLineups.StationType, ChannelLineups.NotCableReadyChannel, ChannelLineups.Updated, Franchise.[Franchise Tax Area], Franchise.[Community Name]
FROM ChannelLineups INNER JOIN Franchise ON ChannelLineups.ChannelGroup = Franchise.ChannelGroup;

The problem with this is I dont want to see channel listed numerous times for each community - I want them to be listed once by the ChannelGroup field. Can anyone offer help on how to construct my query and or SQL statement?

I am understanding that a union query will probably get me the result I want but I am not quite familiar with SQL so I guess I need the "baby steps" to achieve this. THANKS MUCH in advance for any and all help!

 

[shutls]

I forgot to mention that the two tables are joined by the field Channel Group and that the whole reason I want to do a query is to base a report on the query - Is there another way around this besides a union query?

 

[shutls]

union Query

Well I may have in part answered my own question - I just changed my report design to group by ChannelGroup which was what I was trying to accomplish BUT I still do not know how to list on the report the community names included in each group.

Heres what my select query is coming up with when I join the tables currently:

ChannelGroup Channel CallLetters StationName StationType NotCableReadyChannel Updated Franchise Tax Area Community Name
Blissfield 2 WJBK FOX 2 BAS. 6/30/01 22 Township of Blissfield
Blissfield 2 WJBK FOX 2 BAS. 6/30/01 25 Township of Riga
Blissfield 2 WJBK FOX 2 BAS. 6/30/01 28 Township of Palmyra
Blissfield 2 WJBK FOX 2 BAS. 6/30/01 21 Village of Blissfield
Blissfield 3 WGTE PBS 30 BAS. 6/30/01 22 Township of Blissfield
Blissfield 3 WGTE PBS 30 BAS. 6/30/01 25 Township of Riga
Blissfield 3 WGTE PBS 30 BAS. 6/30/01 28 Township of Palmyra
Blissfield 3 WGTE PBS 30 BAS. 6/30/01 21 Village of Blissfield6

What I want to do is show that Channel Group of Blissfield lists at the bottom of the report which Franchise Tax area and community name are included in the Blissfield Group.
If I include the field name in the report I am only getting one of the communities listed when I really want to show all comunities for that group.

 

[cogent1]

A union query has to be based on the same number of fields in the same order from each table, so on the face of it you don't have the fields for a union query here.

The SELECT DISTINCTROW predicate doesn't necessarily return unique rows: try SELECT DISTINCT instead. See Help under DISTINCT for an explanation of how these predicates work.

This is the same as having the Unique Values property set to "yes" in the property sheet.