Limit records meeting query criteria

Hi

I have an Access query but the difference with the limit I want to set compared to everyone else is that I want the limit after my criteria is met.  Each person in my database might meet the criteria several times, but I only want to know the first time they do, after that I want the query to move on to the next person.  Effectively I want one result per person.  So what's the SQL limiting statement that comes after the WHERE statement, I don't think its LIMIT?
matavaiIT ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

karunamoorthyCommented:
you can use function module along with little bit coding and using login table you can meet
your creteria.
RimvisCommented:
Hello matavai.

You can use TOP, e.g.
SELECT TOP 1 SomeData FROM SomeTable WHERE SomeCriteria='SomeValue' ORDER BY SomeColumn

Open in new window

peter57rCommented:
How do define 'first time they do'.  You should imagine records as being in a bucket , not organised in a table.  Are you saying you don't care which record you get for a person, or is there some data value (absolute or relative)  that will indicate the record you want?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Dale FyeOwner, Developing Solutions LLCCommented:
Would be helpful if you would provide a sample of your table structure and your data, with more than one record per person, and then provide a sample of what you want the results to look like.

Much easier for us to visualize that way.
Jeffrey CoachmanMIS LiasonCommented:
Yes, your question is unclear:
<Effectively I want one result per person. >
?


Post a sample of the Raw data
Then post a clear graphical example of the exact output you are expecting for the sample data (for each scenario?)
matavaiIT ManagerAuthor Commented:
Sorry, I wasn't sure how totally obvious my question would be.  So the scenario is we have lots of people who are using a device, they're supposed to use it 6 times a day for 8 weeks.  We have a complete record of each time they use the device, if we total them up by day we have for example (imagine Day headings are over each column of numbers)
      Day 1      Day 2      Day 3      Day 4      Day 5      Day 6
Person 1      5      7      6      6      8      5
Person 2      6      6      6      5      6      6
Person 3      9      8      6      7      9      6
Person 4      6      6      5      5      6      7

We want to know if a person uses over 6 times, we don't care how many times they do, we just want to know if they do.  So my search looks for daily totals that are greater than 6, the first one would be Person 1, Day 2, but I don't care about Day 5 also being over, I just want to move onto the next person as soon as I know Person 1 has overused, so next would be Person 3, Day 1, then Person 4 Day 6.  I tried the TOP 1 statement, but that only found the first record that met the criteria, it didn't move on to the next person.  (The criteria is a little bit more complicated because we don't want to look at Day 4, so the SELECT statement has a lot of date stuff in it, but if you think that would help I can put the full code in).
Dale FyeOwner, Developing Solutions LLCCommented:
Your data is poorly normalized for this type of effort.  What you should have is:

Person#    Day#   Count  

With that type of structure, it would be extremely easy.

So, you have 56 (7*8) of these Day## colums?  I'm thinking the easiest way to do this would be to create a userdefined function that accepts a parameter array.  Then you just pass it the column names and let the function loop through the elements of the array until if finds a value > 6, at which point it would return a True value.

Using the following function, you would create a query that looks like:

SELECT Person, fnExceedsTarget(6, [Day 1], [Day 2], [Day 3], [Day 4], [Day 5], ...., [Day 56]) as ExceedsTarget
FROM yourTable
WHERE fnExceedsTarget(6, [Day 1], [Day 2], [Day 3], [Day 4], [Day 5], ...., [Day 56]) = -1

You would need to replace the .... in the above query with days 6 - 55.  I'm not actually certain that the function will accept a parameter array that large, so you might need to break it up into smaller segments.  The function looks like:

Public Function fnExceedsTarget(TargetNum As Integer, ParamArray FieldValue() As Variant) As Boolean

    Dim intLoop As Integer
   
    'If the number of parameters passed is not the same as the previous pass,
    'then redimension the PrevValue array
    On Error Resume Next
    For intLoop = LBound(FieldValue) To UBound(FieldValue)
        If FieldValue(intLoop) > TargetNum Then
            fnExceedsTarget = True
            Exit For
        End If
    Next
   
End Function
matavaiIT ManagerAuthor Commented:
Sorry fyed, I've probably led you slightly astray just trying to describe the data. Each record has the Persons ID number & the date/time stamp of when they used the device.  We put them in order of person, then date/time, count each use for a given date, & want to know when that count goes over 6...but only the first time for each person.  Currently my SQL code is

SELECT [PD.STUDY ID] AS Expr1, Format([ADJUSTED TIME],"yyyy/mm/dd") AS Expr2, Count(*) AS Expr3
FROM [BASEDATA EXCLUDING THURS_DAYS] AS BDEV
WHERE (((BDEV.[STUDY GROUP])="BLUE"))
GROUP BY [PD.STUDY ID], Format([ADJUSTED TIME],"yyyy/mm/dd")
HAVING (((Count(*))>6))
ORDER BY [PD.STUDY ID], Format([ADJUSTED TIME],"yyyy/mm/dd");

(Extra WHERE statement is because we have three groups, we want to assess each separately).  This statement works, but it finds every person/date combination which has over 6 uses.  Should confess I'm very new to SQL, I understand the process, just don't know all the commands available to me, or their syntax.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
matavaiIT ManagerAuthor Commented:
No final solution, problem solved a different way.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.