recordset duplicates

[jedale]

I have a query that brings back the same records more than once depending on
how many entry's are in the table based on a non-unique ID. This is exactly
how I want the table set up, but I only want the recordset to have just one of
these ID's to be brought back so that I can populate a summary table which will
not distinguish between these duplicates.

Here is some background on what I am trying to accomplish if needed:

I am creating a reporting database in which a primary user is selected but not
necessarily the user logged in (i.e Secretary) My form also brings in as many
secondary authors as the user inputs which also doesn't have to have the
secretary's ID in it. I record the actual maker in the tblEOW so that a
secretary entering information is associated with the report even though they
are not a primary or secondary author. These primary and secondary authors are
recorded into a tblAuthorEOW table which gives me the one to many relationship
for multiple authors. Now that I have the data I wanted to show a summary to
the user logged in of every report that they have made but not necessary an
primary or secondary author hence the Union statement in the query. The query
returns the correct entries but as many times as there are programsID's that
are associated with the EOWRID. Since the summary will not include which
program a report is connected to, I only need to return one PromgramID for each
different EOWRID.

I was wondering if there was something wrong with my query because I thought
the group by syntax would take care of this issue. Also is there a more
efficient way to get the information I want? How would I correct this recordset
duplication?

Thanks you for your time
-Jeff D




<cfquery name="GetEOW" datasource="WEARS">
(SELECT Title, Date, Input, Engineer, Username, Program, tblEOWR.EOWRID
, COUNT(tblProgramEOWR.ProgramID) AS NumProgramPerEOW
, COUNT(tblAuthorEOWR.AuthorID) AS NumAuthors

FROM
tblEOWR, tblAuthorEOWR, tblProgramEOWR, tblProgram, tblAuthor

WHERE tblEOWR.Engineer = '#Session.Username#'
AND tblAuthorEOWR.EOWRID = tblEOWR.EOWRID
AND tblAuthorEOWR.AuthorID = tblAuthor.AuthorID
AND tblProgramEOWR.EOWRID = tblEOWR.EOWRID
AND tblProgramEOWR.ProgramID = tblProgram.ProgramID
GROUP BY
tblEOWR.EOWRID,
Date,
Title,
Engineer,
Program
)
UNION
(SELECT Title, Date, Input, Engineer, Username, Program, tblEOWR.EOWRID
, COUNT(tblProgramEOWR.ProgramID) AS NumProgramPerEOW
, COUNT(tblAuthorEOWR.AuthorID) AS NumAuthors

FROM
tblEOWR, tblAuthorEOWR, tblProgramEOWR, tblProgram, tblAuthor

WHERE tblAuthorEOWR.AuthorID = #Session.AuthorID#
AND tblEOWR.EOWRID = tblAuthorEOWR.EOWRID
AND tblEOWR.EOWRID = tblProgramEOWR.EOWRID
AND tblProgram.ProgramID = tblProgramEOWR.ProgramID
GROUP BY
tblEOWR.EOWRID,
Date,
Title,
Engineer,
Program)

ORDER BY Date
</cfquery>

[jedale]

Well it looks like I stumbled across part of my answer doesn't include the
union statement and it only has the EOWRID in the group by term. I realized
that they only need to see the EOW's they made and not the one's others made
and had their name in as an author. This will be taken care of on a separate
search page.

-Jeff