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:

[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!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
untested

SELECT Batch, [Salesperson Name], MAX([Date Allocated]) AS [Date Allocated]
FROM table
WHERE [Salesperson Name] <> 'Alan Smith'
GROUP BY Batch, [Salesperson Name]
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0
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:
[TABLE="class: grid, width: 500, align: left"]
<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]01/12/2018[/TD]
[TD]James Smith[/TD]
[TD]03/02/2019[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]HGD2[/TD]
[TD]06/10/2018[/TD]
[TD]Danny Franklin[/TD]
[TD]10/12/2018[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top