Excluding Records from Query

[QandAdam]

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

<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)

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

<tbody>
</tbody>

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

 

[QandAdam]

The last column is another field in the table.

The table fields I'll use are:
Batch, Date Allocated, Salesperson, Currently Allocated

E.g. The table will show this for batch ABC1

Batch	Date Allocated	Salesperson	Currently Allocated
ABC1	01/12/2018	James Smith	Yes
ABC1	30/09/2018	Alan Smith	No
ABC1	14/08/2018	Danny Franklin	No

<tbody>
</tbody>

I'd want the query to show:

Batch	Date Allocated To Alan	Last Allocated To	Last Allocated Date	Currently Allocated
ABC1	30/09/2018	James Smith	01/12/2018	Yes

<tbody>
</tbody>

So the final 3 columns are based on the record with the highest Date Allocated for that specific batch and the 'Date Allocated to Alan' returns the date in the record which shows when it was allocated to him.

Does this help?

 

[Joe4]

OK, though it is not always necessary, sometimes it is easier to understand if you break the queries up into different queries, and do "queries of queries". Sometimes it is hard to understand or do if you try to do too much in one single query.

So first, let's create a query called "qryAlanSmith", which returns the batches that have been allocated to Alan smith and have "Currently Allocated" set to "No" for records with his name.
The SQL code of that query might look like this:

SELECT SalesTable.Batch, Sum(IIf([Salesperson Name]="Alan Smith",1,0)) AS [Check], Max(SalesTable.[Date Allocated]) AS [Date Allocated to Alan]
FROM SalesTable
WHERE (((SalesTable.[Currently Allocated])="No"))
GROUP BY SalesTable.Batch
HAVING (((Sum(IIf([Salesperson Name]="Alan Smith",1,0)))>0));

Now, let's create a second query (and call it "qryLastAllocated", which shows when each batch was last allocated).

SELECT SalesTable.Batch, Max(SalesTable.[Date Allocated]) AS [Last Allocated Date]
FROM SalesTable
GROUP BY SalesTable.Batch;

Finally, let's create a third query which combines our first two queries with our original table. That code might look like this:

SELECT SalesTable.Batch, qryAlanSmith.[Date Allocated to Alan], SalesTable.[Salesperson Name] as [Last Allocated To], qryLastAllocated.[Last Allocated Date], SalesTable.[Currently Allocated]
FROM (qryAlanSmith INNER JOIN qryLastAllocated ON qryAlanSmith.Batch = qryLastAllocated.Batch) INNER JOIN SalesTable ON (qryLastAllocated.Batch = SalesTable.Batch) AND (qryLastAllocated.[Last Allocated Date] = SalesTable.[Date Allocated]);

When I did this on your data, it returned what you wanted.