troubleshooting Question

Counting reoccurrence of a Client in a View

Avatar of roblickley
roblickleyFlag for United Kingdom of Great Britain and Northern Ireland asked on
SQL
5 Comments1 Solution85 ViewsLast Modified:
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

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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros