Solved

Limit records meeting query criteria

Posted on 2012-04-03
9
239 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 7

Expert Comment

by:karunamoorthy
ID: 37804488
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
ID: 37804674
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
ID: 37805258
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 48

Expert Comment

by:Dale Fye
ID: 37805539
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37806435
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
ID: 37808872
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 48

Expert Comment

by:Dale Fye
ID: 37808980
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
ID: 37809059
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
ID: 38137179
No final solution, problem solved a different way.
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

623 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