Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

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?
0
matavai
Asked:
matavai
1 Solution
 
karunamoorthyCommented:
you can use function module along with little bit coding and using login table you can meet
your creteria.
0
 
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

0
 
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?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Dale FyeCommented:
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.
0
 
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?)
0
 
matavaiAuthor 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).
0
 
Dale FyeCommented:
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
0
 
matavaiAuthor 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.
0
 
matavaiAuthor Commented:
No final solution, problem solved a different way.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now