Results 1 to 5 of 5
  1. #1
    DianeG is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    16

    Refine my formula, please

    How can I change this to make it only display information if there are 3 Blood Draws within the 30 days. Right now if there is only 1 or 2 in 30 days it shows data and I want it to be blank.


    In (SELECT TOP 3 [Blood Draw Date]


    FROM [Lab Entry] AS F1
    WHERE F1.[Client ID] = [Lab Entry].[Client ID] And [Blood Draw Date] +30 > Date ()
    ORDER BY F1.[Blood Draw Date] DESC, F1.[WBC Count] DESC)

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    Quote Originally Posted by DianeG View Post
    How can I change this to make it only display information if there are 3 Blood Draws within the 30 days. Right now if there is only 1 or 2 in 30 days it shows data and I want it to be blank.


    In (SELECT TOP 3 [Blood Draw Date]
    FROM [Lab Entry] AS F1
    WHERE F1.[Client ID] = [Lab Entry].[Client ID] And [Blood Draw Date] +30 > Date ()
    ORDER BY F1.[Blood Draw Date] DESC, F1.[WBC Count] DESC)
    GROUP BY [Client ID]
    HAVING count([Client ID]) = 3

    That's kind of a shot in the dark approach. Let me know if that works for you. I assume that you will be getting an instance of ClientID for each time blood is drawn. Therefore, if you group by ClientID and only show the ones that show up 3 times, you should get what you want.

  3. #3
    DianeG is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    16

    Talking Refine my formula, please - Solution

    I ended up building another query between the original query and the form. The second query has the Blood Draw Date and the Client ID. I Group By Client ID and Count the Blood Draw Date with >2 in the Criteria.

    It worked and I am very happy.

  4. #4
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    Isn't that what I suggested? just replace the = 3 with > 2 and run it all in one query. Does that not work?

    In any case, I'm glad you got it resolved. =]

  5. #5
    DianeG is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    16

    [solved] Refine my formula, please

    I couldn't get it to work in the original query which is why I ended up with the second query.

    I appreciate your help, because it led me to the solution. Thanks!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Implementing Math Formula
    By cwwaicw311 in forum Forms
    Replies: 19
    Last Post: 02-24-2010, 08:10 AM
  2. Help with formula for Query
    By goldie6175 in forum Queries
    Replies: 1
    Last Post: 02-18-2010, 11:29 AM
  3. Concatenate Formula
    By Shingo in forum Programming
    Replies: 1
    Last Post: 07-25-2009, 09:14 AM
  4. formula on report??
    By dike969 in forum Access
    Replies: 0
    Last Post: 03-05-2007, 01:58 AM
  5. Max formula in Access
    By mohsin74 in forum Programming
    Replies: 2
    Last Post: 12-26-2006, 07:21 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums