Excluding Records from Query

QandAdam

New Member
Joined
Jan 12, 2019
Messages
30
I have an Access Database Table for Sales which lists multiple records and the Salesperson associated. Example below:

BatchSalesperson NameDate Allocated
1234Alan Smith01/02/2019
1234Ryan Fisher25/02/2019
1234Danny Franklin22/12/2018
0987Ryan Fisher14/02/2019
3453John Hazeldine12/01/2019
3453Jake Spencer31/12/2018

<tbody>
</tbody>

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)

BatchSalesperson NameDate Allocated
0987Ryan Fisher14/02/2019
3453John Hazeldine12/01/2019

<tbody>
</tbody>


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

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
untested

SELECT Batch, [Salesperson Name], MAX([Date Allocated]) AS [Date Allocated]
FROM table
WHERE [Salesperson Name] <> 'Alan Smith'
GROUP BY Batch, [Salesperson Name]
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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
 

QandAdam

New Member
Joined
Jan 12, 2019
Messages
30
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,746
Office Version
365
Platform
Windows
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.
 

QandAdam

New Member
Joined
Jan 12, 2019
Messages
30
Amazing! Thankyou so much
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,746
Office Version
365
Platform
Windows
You are welcome.
:)
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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]
 

QandAdam

New Member
Joined
Jan 12, 2019
Messages
30
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:
BatchDate Allocated to AlanLast Allocated ToLast Allocated DateCurrently Allocated
ABC101/12/2018James Smith03/02/2019Yes
HGD206/10/2018Danny Franklin10/12/2018No

<tbody>
</tbody>
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,746
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,849
Messages
5,489,240
Members
407,682
Latest member
gmb2521

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top