Results 1 to 3 of 3
  1. #1
    ExtremeNovice is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    24

    How to determine if a record is the first in its set

    Hello, I am building a patient database for my office and I am learning Access as I go.



    I have a multi table query that I am building to be the basis of a report that will (among other things) display all of the blood draws that our phlebotomists have performed that day - Our Company Bills a higher rate for the first blood draw than we do for subsequent blood draws.

    I have an Event table (date, location, etc.) and a Patient table (name, contact info, etc.). I want to build a formula that recognizes if a patient is the first patient for that event - I think I can do this by comparing the EventID (which is an AutoNumber) against the PatientID (also an AutoNumber) but I have no idea how to do it.

    As an example I would like my query to look something like this (where the 'FirstPatient' field is being determined as a function of the query):
    EventID EventDate EventLocation PatientID PatientName FirstPatient
    5 3/3/2017 MEMPHIS 25 JOE True
    5 3/3/2017 MEMPHIS 26 SALLY False
    6 3/3/2017 LOS ANGELES 27 BILL True
    6 3/3/2017 LOS ANGELES 28 JANE False
    6 3/3/2017 LOS ANGELES 29 FRED False
    7 3/3/2017 MIAMI 30 JILL True


    *the result doesn't need to be True/False it can be 1/0 or Yes/No... I don't really care.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a min query to find the lowest PatientID for the eventid, (qsMin)
    select eventID, min(PatientID) as MinPatient from table.

    now make a query to pull the data, by using the qsMin query:
    select * from tData,qsMin where
    tData.EVentID = qsMin.EventID
    and
    tData.PatientID = qsMin.MinPatient

    make it an outer join to show ALL records in tData, and SOME data in qsMin.
    where qsMin.Minpatient IsNull , will be the false. Add an extra field to get the true/false
    FirstPatient: IIF(IsNull(qsMin.Minpatient),'false','true')

  3. #3
    ExtremeNovice is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    24
    Thank you! It took me a bit to get it working exactly how I wanted but you definitely got me where I needed to be.

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

Similar Threads

  1. How to determine if in Design Mode
    By JrMontgom in forum Access
    Replies: 2
    Last Post: 01-02-2016, 10:24 PM
  2. Replies: 1
    Last Post: 12-04-2012, 11:46 PM
  3. Replies: 3
    Last Post: 05-13-2012, 07:31 AM
  4. How to determine if textbox contains a value then
    By shabbaranks in forum Programming
    Replies: 1
    Last Post: 02-01-2012, 10:58 AM
  5. How do I determine a SQL query result?
    By Trainman in forum Database Design
    Replies: 1
    Last Post: 10-15-2009, 04:49 AM

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