duplicate rows in a query table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to use a query for a subform and a report, but it has duplicate rows.
How can I get rid of the extra rows. We I use the distinct function, it says
"Data type mismatch in criteria expression" Here is the SQL code:

SELECT distinct Proposal_Info.ProposalInfoID, Proposals.ProposalName,
Proposal_Info.[Key?], Proposal_Info.ProposalID, [Resource Type].ResourceType,
Proposal_Info.ResourceID, Proposal_Info.Position, [FirstName] & " " &
[LastName] AS Resource, Proposals.Hiring_Manager, Proposals.Recruiter,
Proposal_Info.Date_Opened, Proposal_Info.Date_Filled,
Workdays(Proposal_Info.Date_Opened,nz(Proposal_Info.Date_Filled,Date())) AS
No_Working_Days, (IIf(IsNull(Proposal_Info.date_filled),'Not Filled','Day(s)
to Fill ')) AS Message
FROM ([Resource Type] RIGHT JOIN Resources ON [Resource Type].ResourceTypeID
= Resources.ResourceType) INNER JOIN ((Proposals INNER JOIN Time_to_Fill ON
Proposals.ProposalID = Time_to_Fill.ProposalID) INNER JOIN Proposal_Info ON
Proposals.ProposalID = Proposal_Info.ProposalID) ON Resources.ResourceID =
Proposal_Info.ResourceID;


Thanks in advance.
 
Dear Akilah:

If you want to use the subform to perform updates to the data, you must
provide some way it can distinguish which row contributes each row to the
query. If you "eliminate duplicates" and there are two "duplicate rows"
that each contribute the exact same information to the subform, then when
that row in the subform is altered, which of the two rows should be altered?
The fact is, that you must control this to completely avoid ambiguity like
this. That is entirely the responsibility of the database designer and
builder.

Now, it is not really clear what you mean by "duplicate". Are there two
rows in one of your tables that are completely indistinguishable in every
detail? Or are there just selected columns in which two rows are the same?
A good question is, why are they the same? Is it by design? If so, what
significance is there to the duplicity? And finally, what is it you expect
this subform to do functionally with respect to such duplicate rows?

Tom Ellison
 
Back
Top