Excluding Records from Query
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Excluding Records from Query

  1. #1
    New Member
    Join Date
    Jan 2019
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excluding Records from Query

    I have an Access Database Table for Sales which lists multiple records and the Salesperson associated. Example below:

    Batch Salesperson Name Date Allocated
    1234 Alan Smith 01/02/2019
    1234 Ryan Fisher 25/02/2019
    1234 Danny Franklin 22/12/2018
    0987 Ryan Fisher 14/02/2019
    3453 John Hazeldine 12/01/2019
    3453 Jake Spencer 31/12/2018

    This list shows all allocations for each batch. Is it possible to have a query which shows a list of all Batches that do not have any records with a certain Salesperson's name?

    E.G If the query was for Alan Smith, then batches with records with Alan Smith in the Salesperson Name field are excluded? Meaning it would return something like this: (It would return 1 record per Batch - The record with the most recent Date Allocated)

    Batch Salesperson Name Date Allocated
    0987 Ryan Fisher 14/02/2019
    3453 John Hazeldine 12/01/2019


    Very complicated explanation but if there is any advice, it would be much appreciated!

  2. #2
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,141
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Excluding Records from Query

    untested

    SELECT Batch, [Salesperson Name], MAX([Date Allocated]) AS [Date Allocated]
    FROM table
    WHERE [Salesperson Name] <> 'Alan Smith'
    GROUP BY Batch, [Salesperson Name]
    To receive a better answer, put more work into asking the question.


  3. #3
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,141
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Excluding Records from Query

    Earlier I missed the requirement about only one record per batch (and gave the simple MAX date by batch & name).

    Instead of the earlier SQL, this is one way, if I've interpreted correctly,

    Code:
    SELECT A.Batch, A.[Salesperson Name], A.[Date Allocated]
    FROM YourTable A, (SELECT Batch, MAX([Date Allocated]) AS [MaxDate]
    FROM YourTable
    WHERE [Salesperson Name] <> 'Alan Smith'
    GROUP BY Batch) B
    WHERE A.Batch = B.Batch AND A.[Date Allocated] = B.MaxDate
    To receive a better answer, put more work into asking the question.


  4. #4
    New Member
    Join Date
    Jan 2019
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excluding Records from Query

    Quote Originally Posted by Fazza View Post
    Earlier I missed the requirement about only one record per batch (and gave the simple MAX date by batch & name).

    Instead of the earlier SQL, this is one way, if I've interpreted correctly,

    Code:
    SELECT A.Batch, A.[Salesperson Name], A.[Date Allocated]
    FROM YourTable A, (SELECT Batch, MAX([Date Allocated]) AS [MaxDate]
    FROM YourTable
    WHERE [Salesperson Name] <> 'Alan Smith'
    GROUP BY Batch) B
    WHERE A.Batch = B.Batch AND A.[Date Allocated] = B.MaxDate
    This returns all batches where 'Alan Smith' is not the most recent Saleperson BUT if Alan is the most recent person then the query returns the record prior to Alan for that specific batch.

    What I'm after is to return all Batches where Alan Smith is not recorded within any records.

  5. #5
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,392
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Excluding Records from Query

    Try this:
    Code:
    SELECT SalesTable.Batch, SalesTable.[Salesperson Name], SalesTable.[Date Allocated]
    FROM SalesTable
    INNER JOIN
    (SELECT SalesTable.Batch, Sum(IIf([Salesperson Name]="Alan Smith",1,0)) AS [Check], Max(SalesTable.[Date Allocated]) AS [MaxOfDate Allocated]
    FROM SalesTable
    GROUP BY SalesTable.Batch) as X
    ON SalesTable.Batch=X.Batch AND SalesTable.[Date Allocated]=X.[MaxOfDate Allocated]
    WHERE X.Check=0;
    Change the table name to match your table name.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #6
    New Member
    Join Date
    Jan 2019
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excluding Records from Query

    Amazing! Thankyou so much
    Quote Originally Posted by Joe4 View Post
    Try this:
    Code:
    SELECT SalesTable.Batch, SalesTable.[Salesperson Name], SalesTable.[Date Allocated]
    FROM SalesTable
    INNER JOIN
    (SELECT SalesTable.Batch, Sum(IIf([Salesperson Name]="Alan Smith",1,0)) AS [Check], Max(SalesTable.[Date Allocated]) AS [MaxOfDate Allocated]
    FROM SalesTable
    GROUP BY SalesTable.Batch) as X
    ON SalesTable.Batch=X.Batch AND SalesTable.[Date Allocated]=X.[MaxOfDate Allocated]
    WHERE X.Check=0;
    Change the table name to match your table name.

  7. #7
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,392
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Excluding Records from Query

    You are welcome.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #8
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,141
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Excluding Records from Query

    See you got this sorted while I was away. And also see I originally mis-understood the exclusion of all Batch containing 'Alan Smith'

    FWIW, a slight variation on Joe's solution - in the way the unwanted batches are identified.

    Code:
    SELECT C.Batch, C.[Salesperson Name], C.[Date Allocated]
    FROM YourTable C, 
    (SELECT A.Batch, MAX(A.[Date Allocated]) AS [Date Allocated]
    FROM YourTable A
    WHERE A.Batch NOT IN (SELECT DISTINCT Batch
    FROM YourTable
    WHERE [Salesperson Name] = 'Alan Smith')
    GROUP BY A.Batch) B
    WHERE C.Batch = B.Batch AND C.[Date Allocated] = B.[Date Allocated]
    To receive a better answer, put more work into asking the question.


  9. #9
    New Member
    Join Date
    Jan 2019
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excluding Records from Query

    My next question is sort of related to this but I'm not sure if I should ask this here or not so let me know if I need to post a thread.

    If I wanted a different query to show me a list of all batches that Alan Smith HAS been allocated WHERE [Current Allocation]='No', could I get the following fields:
    Batch
    Date Allocated (To Alan Smith)
    Allocated To (Based on the record which includes the MAX [Date Allocated])
    Date Allocated (Based on the record which includes the MAX [Date Allocated])
    Current Allocation (Based on the record which includes the MAX [Date Allocated])

    It should look something like this:
    Batch Date Allocated to Alan Last Allocated To Last Allocated Date Currently Allocated
    ABC1 01/12/2018 James Smith 03/02/2019 Yes
    HGD2 06/10/2018 Danny Franklin 10/12/2018 No

  10. #10
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,392
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Excluding Records from Query

    I am not sure I understand the last column. What indicates if something is currently allocated?
    It may be helpful if you can post a myriad of different data examples, and the expected output of each.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •