Avatar of roblickley
Flag for United Kingdom of Great Britain and Northern Ireland

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

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

Open in new window

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

Avatar of undefined
Last Comment
Pawan Kumar

8/22/2022 - Mon