Displaying a Name

[Milkstr]

I have a database of football players names, their names appear more than once if the table, i.e a players name might be there 10 times, the initial name is just a short version for ease, i then have a second field with their full name, but this only appears once, I want to do a sum of all their goals scored but display their full name that appears once and not their short name that appears 10 times or more.

SELECT Players.PlayerName, Sum(Players.Goals) AS SumOfGoals
FROM Players
WHERE (((Players.Goals)>0) AND ((Players.LeagueCup)="league"))
GROUP BY Players.PlayerName, Players.Team
HAVING (((Players.Team)="first"));

I use this at the moment, but this diplays their short name, their full name is DisplayName

Can anyone help????

[PEB]

Where is stored their full name?

In separate table or in the same table?

If in separate table do a join with this table in your query!

JOIN by Short name and invoke your full name!

:)

[Milkstr]

The full name is stored in the same table, but the full name only appears in one of the entries.

[PEB]

Hi,
So the structure of your table isn't well done! It should be great if you have a table with players where you have short name and full name and table with scores... Where you have the sort name and the score for each match...

In this case it will be very easy to link the short names results to your full name...

In your case there is always possibility to do it...

So you need to save a query with 2 columns short and full name...

It should be like:

Select ShortName, FullName FROM Soccers GROUP BY ShortName, FullName WHERE len(ShortName)>0 AND Len(FullName)>0;

Save this query as soccers_names, but remember! You should change the fields and the table with your own!

When it is done, add this query to the query that you have done, link the 2 views by ShortName and invoke FullName from soccers_names and it should work!

:)