asked on
Counting reoccurrence of a Client in a View
Dear Experts,
Below is my SQL view code to return the information I need to create a page
It relates to entries in a competition for horses and riders - and it works very nicely.
I would like to add the additional functionality of counting the number of times a particular ClientID occurs in the results of this view and adding an additional column in the data entitled "CompReacc" to hold the number of times.
It is important to note that this must ONLY be linked to the occurrence of the ClientID as they may be riding multiple horses in the competition.
For instance, if a client is only entered once on one horse then column CompReacc would return 1
If they were entered twice (either on the same horse or another horse) then that column would return 2 - this would of course return the value twice in effect - once for each row where they were.
The reason behind needing this is that when I print a list of entries in a show I would like to quickly see those clients with multiple entries.
Many thanks in advance
Below is my SQL view code to return the information I need to create a page
SELECT TOP (100) PERCENT dbo.tblEntry.EntryID, dbo.tblClient.ClientForeName, dbo.tblClient.ClientSurName, dbo.tblClient.ClientID, dbo.tblHorse.HorseID, dbo.tblHorse.HorseName, dbo.tblEntry.EntryBridleNumber,
dbo.tblEntry.EntryStartTime, dbo.tblClass.ClassID, dbo.tblShow.ShowID, dbo.tblEntry.EntryApproved, dbo.tblEntry.EntrySectionPlace, dbo.tblEntry.EntryPercentageMark, dbo.tblEntry.EntryResultsMark,
dbo.tblEntry.EntryResultsPlace, dbo.tblEntry.Section, dbo.tblEntry.EntryCancelled, dbo.tblEntry.EntryCollectiveMark, dbo.tblEntry.EntryResultsMoney, dbo.tblEntry.EntryNotes, dbo.tblClass.ClassNumber,
dbo.tblTest.TestName, dbo.tblShow.ShowStartDate, dbo.tblClient.ClientBDNumber, dbo.tblClient.ClientBSJANumber, dbo.tblHorse.HorseBSJANumber, dbo.tblHorse.HorseBDNumber
FROM dbo.tblEntry INNER JOIN
dbo.tblClient ON dbo.tblEntry.RiderID = dbo.tblClient.ClientID INNER JOIN
dbo.tblHorse ON dbo.tblEntry.HorseID = dbo.tblHorse.HorseID INNER JOIN
dbo.tblClass ON dbo.tblEntry.ClassID = dbo.tblClass.ClassID INNER JOIN
dbo.tblShow ON dbo.tblClass.ShowID = dbo.tblShow.ShowID INNER JOIN
dbo.tblTest ON dbo.tblClass.TestID = dbo.tblTest.TestID
WHERE (dbo.tblEntry.EntryApproved <> 'Hold') AND (dbo.tblEntry.EntryCancelled <> 1) AND (dbo.tblEntry.EntryApproved <> 'No')
ORDER BY dbo.tblEntry.EntryStartTime, dbo.tblEntry.EntryBridleNumber, dbo.tblClass.ClassID
It relates to entries in a competition for horses and riders - and it works very nicely.
I would like to add the additional functionality of counting the number of times a particular ClientID occurs in the results of this view and adding an additional column in the data entitled "CompReacc" to hold the number of times.
It is important to note that this must ONLY be linked to the occurrence of the ClientID as they may be riding multiple horses in the competition.
For instance, if a client is only entered once on one horse then column CompReacc would return 1
If they were entered twice (either on the same horse or another horse) then that column would return 2 - this would of course return the value twice in effect - once for each row where they were.
The reason behind needing this is that when I print a list of entries in a show I would like to quickly see those clients with multiple entries.
Many thanks in advance
SQL
Last Comment
Pawan Kumar