Solved

Limit records meeting query criteria

Posted on 2012-04-03
9
191 Views
Last Modified: 2012-06-29
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
Comment
Question by:matavai
9 Comments
 
LVL 7

Expert Comment

by:karunamoorthy
Comment Utility
you can use function module along with little bit coding and using login table you can meet
your creteria.
0
 
LVL 19

Expert Comment

by:Rimvis
Comment Utility
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
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 

Author Comment

by:matavai
Comment Utility
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
 

Accepted Solution

by:
matavai earned 0 total points
Comment Utility
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
 

Author Closing Comment

by:matavai
Comment Utility
No final solution, problem solved a different way.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now