Query returning some duplicate results (1 Viewer)

[SaraMegan]

Hi, everyone... I've got another problem...

I've created a query to try to generate a report, but it's giving me some duplicate results. It doesn't duplicate every result, just some of them...

Here's my sql:

SELECT qryApplication.AnnouncementNo, [qryApplicant].[LastName] & ", " & [qryApplicant].[FrstName] AS ApplicantLstFrst, qryApplication.applicant, [PositionTitle] & ", " & [PositionNo] AS Position, qryApplication.Office, qryApplication.CertifyID, qryApplication.TestDate, qryApplication.TestScore
FROM qryApplicant INNER JOIN qryApplication ON qryApplicant.ApplicationID = qryApplication.ApplicationID
WHERE (((qryApplication.AnnouncementNo)=[forms]![frmRegister]![lstPosting]) AND ((qryApplication.CertifyID)=1));


I've taken two already made queries (qryApplication and qryApplicant) and joined them by ApplicationID. Each time an applicant applies for a position, a new record joining a new application with an existing posting is created in tblJunction... The only thing I can find in my test data that the duplicate results have in common is a common Posting. (Meaning, the people who are duplicated applied to the same positions. The positions with only one applicant show up alone.)

Yeah, it's been a long week (even though we had Monday off...) Difficult to think now. Anyway, any insights would be appreciated, but I'll keep hammering away at trying to figure it out in the meanwhile.

Thanks in advance. Again.

--Sara

 

[SaraMegan]

Does anyone have any thoughts on this? Still having the problem... Still not sure why... Thanks again.

--Sara

 

[RichMorrison]

Sara,

Please explain the problem some more.

What do you want to see in your report ?
What do you actually see ?
(Examples are good)

SQL is hard to follow.

RichM

 

[Not A PHB]

Hi Sara,

By duplicate results do you mean the entire record is duplicated or the position is duplicated for each applicant that applied?

If the entire record is duplicated a group by statement should take care of it.

SELECT qryApplication.AnnouncementNo, [qryApplicant].[LastName] & ", " & [qryApplicant].[FrstName] AS ApplicantLstFrst, qryApplication.applicant, [PositionTitle] & ", " & [PositionNo] AS [Position], qryApplication.Office, qryApplication.CertifyID, qryApplication.TestDate, qryApplication.TestScore
FROM qryApplicant INNER JOIN qryApplication ON qryApplicant.ApplicationID = qryApplication.ApplicationID
GROUP BY qryApplication.AnnouncementNo, [qryApplicant].[LastName] & ", " & [qryApplicant].[FrstName], qryApplication.applicant, [PositionTitle] & ", " & [PositionNo], qryApplication.Office, qryApplication.CertifyID, qryApplication.TestDate, qryApplication.TestScore
HAVING (((qryApplication.AnnouncementNo)=[forms]![frmRegister]![lstPosting]) AND ((qryApplication.CertifyID)="1"));

If it is the position duplicated for each applicant and you only want the position to appear once in the report then you can use the sorting/grouping button in design mode. Group on Position, select group header and move the duplicated fields into the Position header. Leave the applicant names in the detail section and they will be listed below. Another way that may work is on the report select the fields that are duplicated, go to properties and check of hide duplicates.

HTH

Lance

 

[SaraMegan]

Not a PHB,

Thanks for the reply!

I've actually sort of discovered what was causing the query to have duplicate results (I think). It had to do with other things I was doing to manipulate data. I don't really have any idea what was really going on, but I had changed how I went about some of the other action queries, and it solved my problem. I don't know where or why, but I'm ok now.

Are you really from Hopkinton? I'm from Contoocook! Crazy!

--Sara