Union Query (1 Viewer)

[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?

 

[Pat Hartman]

A Union query is not the answer since you want columns from BOTH tables returned in the same row of the resultset.

Without a description of both tables I'm just guessing. But my guess is that you need to include Channel in the join. Draw an additional join line to connect the Channel field from both tables. That will change the join to:

FROM ChannelLineups INNER JOIN Franchise ON ChannelLineups.ChannelGroup = Franchise.ChannelGroup AND ChannelLineups.Channel = Franchise.Channel;

If that doesn't solve the problem, post the table structures showing the primary and foreign keys.

 

[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.

 

[Pat Hartman]

If you don't want the details about the channels, don't include that information in the query. The recordset you posted is what I would expect to see from the query that you posted.

Perhaps if you sorted the report by ChannelGroup, CommunityName, and finally Channel, it would look better to you.