Excluding Records from Query

[QandAdam]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Batch[/TD]
[TD]Salesperson Name[/TD]
[TD]Date Allocated[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]Alan Smith[/TD]
[TD]01/02/2019[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]Ryan Fisher[/TD]
[TD]25/02/2019[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]Danny Franklin[/TD]
[TD]22/12/2018[/TD]
[/TR]
[TR]
[TD]0987[/TD]
[TD]Ryan Fisher[/TD]
[TD]14/02/2019[/TD]
[/TR]
[TR]
[TD]3453[/TD]
[TD]John Hazeldine[/TD]
[TD]12/01/2019[/TD]
[/TR]
[TR]
[TD]3453[/TD]
[TD]Jake Spencer[/TD]
[TD]31/12/2018[/TD]
[/TR]
</tbody>[/TABLE]

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)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Batch[/TD]
[TD]Salesperson Name[/TD]
[TD]Date Allocated[/TD]
[/TR]
[TR]
[TD]0987[/TD]
[TD]Ryan Fisher[/TD]
[TD]14/02/2019[/TD]
[/TR]
[TR]
[TD]3453[/TD]
[TD]John Hazeldine[/TD]
[TD]12/01/2019[/TD]
[/TR]
</tbody>[/TABLE]


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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Batch[/TD]
[TD]Date Allocated[/TD]
[TD]Salesperson[/TD]
[TD]Currently Allocated[/TD]
[/TR]
[TR]
[TD]ABC1[/TD]
[TD]01/12/2018[/TD]
[TD]James Smith[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]ABC1[/TD]
[TD]30/09/2018[/TD]
[TD]Alan Smith[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]ABC1[/TD]
[TD]14/08/2018[/TD]
[TD]Danny Franklin[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

I'd want the query to show:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Batch[/TD]
[TD]Date Allocated To Alan[/TD]
[TD]Last Allocated To[/TD]
[TD]Last Allocated Date[/TD]
[TD]Currently Allocated[/TD]
[/TR]
[TR]
[TD]ABC1[/TD]
[TD]30/09/2018[/TD]
[TD]James Smith[/TD]
[TD]01/12/2018[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]

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.