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

    How to auto-number rows for each unique record in a parent table

    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.
    I want to design the query to number the rows for every blood draw that the phlebotomist performed, but I want it to be unique to the phlebotomist who performed the blood draw.

    For example in the table below, I would like the 'PatientCount' to be generated by the query and to count up the number of blood draws that each phlebotomist has performed. This way I can easily see that Joe Schmo was George Washington's 2nd patient of the day, or that Sally White was John Adam's 4th patient of the day.
    PhlebotomistName PatientCount PatientName
    Washington, George 1 Doe, John
    Washington, George 2 Schmo, Joe
    Adams, John 1 Smith, Jane
    Adams, John 2 Brown, Tom
    Adams, John 3 Jones, Bill
    Adams, John 4 White, Sally
    Madison, James 1 Green, Ted
    Madison, James 2 Black, Fred
    Madison, James 3 Johnson, Mark

    The phlebotomist table has a one to many relationship with the patient table.

    Thanks!

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You can do it in a report, but not in a query.
    in a query, set a field =1,
    then set the report field of this field to running total=yes, (in the group)

  3. #3
    ExtremeNovice is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    24
    Thanks for Responding! Unfortunately this needs to be performed in my query because I have other calculations that will be based of the numbers generated - we bill a higher rate for the first blood draw then we do for subsequent blood draws.

  4. #4
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    What field in your table orders the patients for each phlebotomist? Is there a datetime the blood was drawn or and ID field where you put them into the table in that order?

    Quote Originally Posted by ExtremeNovice View Post
    I have other calculations that will be based of the numbers generated - we bill a higher rate for the first blood draw then we do for subsequent blood draws.
    You can do calculations on the report when it is run instead of in the query. What are the calculations you need done once you know the order number of the patient?

  5. #5
    ExtremeNovice is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    24
    I have a table for my Phlebotomists (with an auto-numbered PhlebotomistID field), I have an Event table (with an auto-numbered EventID) that records the details of the blood-draw (date, time, address, etc.) and a Patient table (with a auto-numbered PatientID field) that contains patient data (names, case number, contact info, etc.). Each table has a One to Many relationship with the next: Phlebotomists-->Event-->Patients.

    If there is a way that I can generate this number in my table that would work just as well...

  6. #6
    ExtremeNovice is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    24
    I have just a few simple IIF functions that determine billing based on the row number but I would still like to avoid having to create a report. My other problem with using a report is that I will, ideally, be exporting the raw query data as a .xls file to be sent along with a corresponding invoice to our clients.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    See ranking in a query by Allen Browne here for possible ideas. Not sure what you need exactly. As Bulzie asked --maybe you can order records by Date/time or something.

  8. #8
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Maybe do in vba to set that counter in your Events table based on PhlebotomistID and BloodDrawDateTime(make sure this is a datetime data type). Add PatientCount to Events table. Maybe something like below could run either once a day or in AfterUpdate of the form to do this every time a record is created?

    Dim db as Database, rs1 as Recordset, vsql as string, vPhlebID, vCounter as Integer
    Set db = Current Database

    vsql = "Select PhlebotomistID, BloodDrawDateTime, PatientCount From tblEvents Where (BloodDrawDateTime = Date()) Order By PhlebotomistID, BloodDrawDateTime"
    Set rs1 = db.OpenRecordset (vsql)

    rs1.MoveFirst
    vPhlebID =0
    vCounter = 0

    DoUntil rs1.EOF
    rs1!Edit
    If vPhlebID = rs1!PhlebotomistID then
    rs1!PatientCount - vCounter + 1
    Else
    rs1!PatientCount = 1
    vCounter = 1
    End If
    rs1!Update
    rs1!MoveNext
    Loop

    Or better yet maybe do this before saving new record:

    vPatientCount = DMax("[PatientCount]", "tblEvents", "[BloodDrawDateTime] = #" & Date() & "# And [PhlebotomistID] = " & Me.PhlebotomistID)

    If IsNull(vPatientCount) then
    rs1!PatientCount = 1
    Else
    rs1!PatientCount = vPatientCount + 1
    End If


    Syntax needs adjusting but that's general idea.

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

Similar Threads

  1. Replies: 2
    Last Post: 08-12-2016, 07:00 AM
  2. Replies: 4
    Last Post: 07-16-2015, 04:49 PM
  3. Replies: 5
    Last Post: 11-30-2014, 12:46 PM
  4. Replies: 2
    Last Post: 06-01-2013, 07:00 AM
  5. Auto Unique Number on every REPORT
    By imvineet in forum Access
    Replies: 3
    Last Post: 09-09-2010, 08:08 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