Solved

Limit records meeting query criteria

Posted on 2012-04-03
9
222 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
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 47

Expert Comment

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

Expert Comment

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

840 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