Results 1 to 7 of 7
  1. #1
    Ekhart is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    80

    Alpha Split

    I have a report that pulls specific records based on who is logged on. How can I set these up to do a specific alpha split? Say Tom only sees records where the 'Last Name' field starts with A-M and Bill only sees records where the 'Last Name' field starts with N-Z.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you will need an additional text field in your user table - called say NameRange and populated with AM for Tom and NZ for Bill
    something like

    Code:
    SELECT *
    FROM myTable inner join usertable ON myTable.user=usertable.username
    WHERE left(myTable.lastname,1) between left(usertable.namerange,1) and right(usertable.namerange,1)
    or

    Code:
    SELECT *
    FROM myTable inner join usertable ON myTable.user=usertable.username and left(myTable.lastname,1) between left(usertable.namerange,1) and right(usertable.namerange,1)
    note that the second version will not display in the query window, only the sql window but has a performance beneft

  3. #3
    Ekhart is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    80
    Thanks for the above, I have tried and failed to incorporate it properly. Below is the SQL for one of my queries to see if it helps figure out where to put this. I use AlphaSplit as the field for the AM and NZ. EmployeeID is the unique field between the EmployeeInfo table which links to the SchedulerClinics table, and from there the Clinic field links the SchedulerClinics table to the Scheduler_Sheet table which is where the bulk of the data for the reports is stored.

    Code:
    SELECT Scheduler_Sheet.ConsultID, Scheduler_Sheet.LastName, Scheduler_Sheet.FirstName, Scheduler_Sheet.SSN, Scheduler_Sheet.Urgency, Scheduler_Sheet.Program, Scheduler_Sheet.Clinic, Scheduler_Sheet.LastAction, Scheduler_Sheet.DateofAction, Scheduler_Sheet.AppointmentDate, Scheduler_Sheet.RNComment, Scheduler_Sheet.SchedulerComment, Scheduler_Sheet.RNActionNeeded, Scheduler_Sheet.DateOfConsult, Scheduler_Sheet.ConsultNumber
    FROM Scheduler_Sheet INNER JOIN (tblEmployeeFiles INNER JOIN tblSchedulerClinics ON tblEmployeeFiles.EmployeeID = tblSchedulerClinics.EmployeeID) ON Scheduler_Sheet.Clinic = tblSchedulerClinics.Clinic
    WHERE (((Scheduler_Sheet.Program)<>"Choice") And ((Scheduler_Sheet.DateOfConsult)<Date()-60 And (Scheduler_Sheet.DateOfConsult)>Date()-80) And ((Scheduler_Sheet.Complete)=False) And ((Scheduler_Sheet.DC)=False) And ((tblEmployeeFiles.EmployeeID)=Forms!frmLogin!cboUser)) Or (((Scheduler_Sheet.Program)<>"Choice") And ((Scheduler_Sheet.DateOfConsult)<=Date()-80) And ((Scheduler_Sheet.Complete)=False) And ((Scheduler_Sheet.DC)=False) And ((tblEmployeeFiles.EmployeeID)=Forms!frmLogin!cboUser));
    Reformatted the SQL (orange) for readability
    Code:
    SELECT Scheduler_Sheet.ConsultID
        ,Scheduler_Sheet.LastName
        ,Scheduler_Sheet.FirstName
        ,Scheduler_Sheet.SSN
        ,Scheduler_Sheet.Urgency
        ,Scheduler_Sheet.Program
        ,Scheduler_Sheet.Clinic
        ,Scheduler_Sheet.LastAction
        ,Scheduler_Sheet.DateofAction
        ,Scheduler_Sheet.AppointmentDate
        ,Scheduler_Sheet.RNComment
        ,Scheduler_Sheet.SchedulerComment
        ,Scheduler_Sheet.RNActionNeeded
        ,Scheduler_Sheet.DateOfConsult
        ,Scheduler_Sheet.ConsultNumber
    FROM Scheduler_Sheet
    INNER JOIN (
        tblEmployeeFiles INNER JOIN tblSchedulerClinics ON tblEmployeeFiles.EmployeeID = tblSchedulerClinics.EmployeeID
        ) ON Scheduler_Sheet.Clinic = tblSchedulerClinics.Clinic
    WHERE (
            ((Scheduler_Sheet.Program) <> "Choice")
            AND (
                (Scheduler_Sheet.DateOfConsult) < DATE () - 60
                AND (Scheduler_Sheet.DateOfConsult) > DATE () - 80
                )
            AND ((Scheduler_Sheet.Complete) = False)
            AND ((Scheduler_Sheet.DC) = False)
            AND ((tblEmployeeFiles.EmployeeID) = Forms ! frmLogin ! cboUser)
            )
        OR (
            ((Scheduler_Sheet.Program) <> "Choice")
            AND ((Scheduler_Sheet.DateOfConsult) <= DATE () - 80)
            AND ((Scheduler_Sheet.Complete) = False)
            AND ((Scheduler_Sheet.DC) = False)
            AND ((tblEmployeeFiles.EmployeeID) = Forms ! frmLogin ! cboUser)
            );

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    thanks for laying the code out clearly - makes a huge difference!

    looks like you would need this on both parts of your or criteria

    AND left(Scheduler_Sheet.LastName,1) between left(tblEmployeeFiles.alphasplit,1) and right(tblEmployeeFiles.alphasplit,1)

    but your criteria seems overly complex.

    Only difference between the two parts is the dateofconsult

    Code:
    (
                (Scheduler_Sheet.DateOfConsult) < DATE () - 60
                AND (Scheduler_Sheet.DateOfConsult) > DATE () - 80
                )
    ....
    ....
    OR ((Scheduler_Sheet.DateOfConsult) <= DATE () - 80)
    which is saying the dateofconsult is between 60 and 80 days ago or more than 80 days ago

    which is the same as saying less than 60 days ago

    would think you could simplify it to

    Code:
    WHERE 
            Scheduler_Sheet.Program <> "Choice"
            AND Scheduler_Sheet.DateOfConsult < DATE () - 60
            AND Scheduler_Sheet.Complete = False
            AND Scheduler_Sheet.DC = False
            AND left(Scheduler_Sheet.LastName,1) between left(tblEmployeeFiles.alphasplit,1) and right(tblEmployeeFiles.alphasplit,1)
            AND tblEmployeeFiles.EmployeeID = Forms ! frmLogin ! cboUser

  5. #5
    Ekhart is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    80
    Aha great this works like a charm! And thanks for catching the error in the code I had

  6. #6
    Ekhart is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    80
    One small follow-up. Is there a way to give 1 person 2 alpha splits? So when Jim logs on it pulls the alphas for A-E and M-R for instance? I use the EmployeeID as the Primary so was trying to avoid simply putting 2 records in for Jim, one for each alpha split.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you could use the entire alphabet

    Jim - ABCDEMNOPQR
    Fred - FGHIJKLSTUVWXYZ

    and modify your criteria to

    AND instr(tblEmployeeFiles.alphasplit,left(Scheduler_S heet.LastName,1))>0

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

Similar Threads

  1. creation alpha numeric code
    By Jen0dorf in forum Access
    Replies: 1
    Last Post: 05-09-2016, 11:52 AM
  2. Alpha Numeric auto id?
    By arshadmgic in forum Forms
    Replies: 7
    Last Post: 09-02-2012, 03:00 AM
  3. Report in alpha order
    By khartoum in forum Reports
    Replies: 12
    Last Post: 03-27-2012, 09:01 AM
  4. Acess vs Alpha Five
    By afc in forum Access
    Replies: 5
    Last Post: 03-20-2012, 11:42 AM
  5. Recordset not showing alpha-numeric entries
    By rayhawk in forum Access
    Replies: 9
    Last Post: 10-29-2010, 08:15 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