Only one value wanted from a column with duplicates

[Guest]

This is probably a beginner question. However, I have the following query:
SELECT tblPROJECTS.Projectsymbol, tblPROJECTS.Title, tblPROJECTS.StatusDate,
tblPROJECTS.Budget, tblPROJECTS.Status, tblPROJECTS.Region,
tblProjectSubject.SubjectName, tblPROJECTS.ProjectID
FROM tblPROJECTS INNER JOIN tblProjectSubject ON tblPROJECTS.ProjectID =
tblProjectSubject.ProjectID;
based on two tables where there is a one-to many elationship between
ProjectID (autonumber) and SubjectName. Thus the query returns many records
where, for example, the ProjectID is repeated.
I only need records with unique ProjectIDs. How do I do that?
Niels

 

[Tom Ellison]

Dear Niels:

Your query slightly modified for my reading preferences:

SELECT P.Projectsymbol, P.Title, P.StatusDate,
P.Budget, P.Status, P.Region,
S.SubjectName, P.ProjectID
FROM tblPROJECTS P
INNER JOIN tblProjectSubject S
ON P.ProjectID = S.ProjectID;

I put this here for my future reference as we work on this problem.

Now, this query returns 8 columns. When there are multiple rows for a
single value of ProjectID, do the other 7 columns always contain exactly the
same values for all the rows with that single ProjectID? Probably not.

So, it sounds like you expect the query to somehow arbitrarily choose values
for the other 7 columns out of all the various values, and put these 7
values into the results to the exclusion of all the others. Is that right?

Well, I have bad news for you. Queries are a kind of programming, and
expecting a program to perform arbitrary feats is not realistic. It's not
going to do that.

Instead, you may need to choose one of the rows presented by this query to
be the one presented, or to choose an aggregate (Sum, Average, Minimum,
Maximum, etc.) for the other columns. This a query will do for you, but you
must make the choices.

Possibly the ProjectID is unique in tblPROJECTS. In this case it is only
the INNER JOIN to tblProjectSubject that is creating the duplication. If
this is the case, that simplifies it. One possible solution would be to
eliminate the SubjectName coming from that table. Otherwise, you must
choose to display only one of these SubjectNames to the exclusion of all
others. Which one are you expecting to see in the results? How would you
suggest we can systematically choose the one that is desirable?

If the above is not an accurate representation of what you have, please help
me understand where I have misunderstood you.

Tom Ellison

 

[Guest]

Dear Tom,
Thanks for your help. You got it fairly precisely. The ProjectID is unique
in tblProjects and it is the inner join to the tblProjectSubject that creates
the duplication since each project can have more than one subject. I need the
tblProjectSubject since the query returns a choice to a list box based upon
paramters entered by the user in three different combo boxes (the "all"
choice is included in the combos), and subject is one of them.
It does not matter what value is returned. I just do not want, e.g. project
1 or project 5 to appear in the list list box numerous times because it
refers to several subjects. It is enough that project 1 or project 5 is
returned once.
Hope this explains what I intend to achieve.
Niels

 

[Tom Ellison]

Dear Niels:

I does seem unusual to me to show some values that do not necessarily belong
together, and to omit others in a fairly arbitrary fashion. Would it be
acceptable to just not show these other columns? How is showing an
arbitrary set of values for those columns better than not showing them at
all?

Well, anyway, perhaps this does it for you:

SELECT FIRST(P.Projectsymbol), FIRST(P.Title), FIRST(P.StatusDate),
FIRST(P.Budget), FIRST(P.Status), FIRST(P.Region),
FIRST(S.SubjectName), P.ProjectID
FROM tblPROJECTS P
INNER JOIN tblProjectSubject S
ON P.ProjectID = S.ProjectID;
GROUP BY P.ProjectID

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison