Is this possible? Grouping and Not In

[toast]

Hello all,

I'm struggling to come up with a recordsource for a report in the following circumstances:

Employees work on Shifts, each shift has multiple deliveries, each delivery is to a certain district.

What I need is a report grouped by district, showing the employees who have NOT made a delivery to that district.
This would be something along the lines of:

District A
Employee 3
Employee 5

District B
Employee 2
Employee 9

and so on.

I can get the recordsource correct to show which employees HAVE made deliveries to each district.

And I can come up with a recordsource that shows me who has not made any deliveries to any district (or I can limit it to a particular district by adding that to the WHERE statement)

SELECT lngEmployeeID, strFullName FROM tblEmployees e
WHERE NOT EXISTS (SELECT * FROM  tblShifts s 
INNER JOIN tblDeliveries d ON s.lngShiftID = d.lngShiftID 
WHERE e.lngEmployeeID = s.lngDriver OR e.lngEmployeeID = s.lngLoader

But I can't figure out what recordsource I would need to be able to produce the report described above.

If anybody can offer any suggestions or help that would be very much appreciated (even if the help is stating that it can't be done!)

 

[Isskint]

Use the query you have produced to show which employees HAVE made deliveries to each district as the source for the Find Unmatched Query Wizard and use the employees table as the second argument JOINING on employee ID. This should produce a list of names that do not appear on the your employees HAVE made deliveries query.

 

[toast]

Thanks for introducing me to a new feature of Access I never knew exists! (find unmatched query wizard)

Unfortunately, it will not solve my problem in this instance.

The query which shows who HAS made deliveries simply finds all deliveries (with their associated employees conducting the deliveries) and then the report groups these employees by district.

The unmatched query will just show who hasn't made any deliveries yet. Therefore if an employee has delivered to just one district, they won't appear in this query. Plus I can't see a way of grouping them by district this way either.

Or am I missing something more obvious? (quite possible!)

 

[Brianwarnock]

The logic of what is required suggests that you cannot accomplish this in a query but would have to use code,

Unless you can create 2 queries
1 listing district and employee who has delivered
2 listing district and all employees

Then run unmatched query on the concatenated fields

Might work

Brian

 

[plog]

I think you can accomplish this with just queries. You will need a datasource that lists all the unique employees, a datasource that lists all the unique Districts and then a datasource that lists the deliveries that have gone to districts by employee. Can you provide the structure of those datasources? List the name of the tables and the name of the fields.

 

[toast]

Hello, thanks for the replies. The required tables and fields would be:

tblEmployees
lngEmployeeID (PK)
strFullName

tblShifts
lngShiftID (PK)
lngDriver (FK from tblEmployees*)
lngLoader (FK from tblEmployees*)

tblDeliveries
lngDeliveryID (PK)
lngShiftID (FK from tblShifts)
lngDistrictID (FK from tblDistricts)

tblDistricts
lngDistrictID (PK)
strDistrictName

*each employee can be allocated as either a Driver or a Loader.

 

[plog]

*each employee can be allocated as either a Driver or a Loader.

Just to be clear, that is more of an FYI and doesn't really have any bearing on what you want to produce. Right? You are concerned about drivers (lngDriver) and not with loaders. You were just demonstrating the realtionships between the two tables?

If so, then these are the queries you will need to produce your dataset:

DistrictDrivers_sub1

SELECT tblDeliveries.lngDistrictID, tblEmployees.lngEmployeeID, Count(tblDeliveries.lngDeliveryID) AS Deliveries
FROM (tblDeliveries INNER JOIN tblShifts ON tblDeliveries.lngShiftID = tblShifts.lngShiftID) INNER JOIN tblEmployees ON tblShifts.lngDriver = tblEmployees.lngEmployeeID
GROUP BY tblDeliveries.lngDistrictID, tblEmployees.lngEmployeeID;

DistrictDrivers_sub2

SELECT tblEmployees.lngEmployeeID, tblDistricts.lngDistrictID, tblEmployees.strFullName, tblDistricts.strDistrictName
FROM tblEmployees, tblDistricts;

DistrictDrivers

SELECT DistrictDrivers_sub1.strFullName, DistrictDrivers_sub1.strDistrictName, IIf(IsNull([Deliveries]),0,[Deliveries]) AS TotalDeliveries
FROM DistrictDrivers_sub1 LEFT JOIN DistrictDrivers_sub2 ON (DistrictDrivers_sub1.lngDistrictID = DistrictDrivers_sub2.lngDistrictID) AND (DistrictDrivers_sub1.lngEmployeeID = DistrictDrivers_sub2.lngEmployeeID);

The first query (DistrictDrivers_sub1) is probably what you tried--its a simple aggregate query that totals up the drivers by district and sees how many deliveries they have to each. Unfortunately it doesn't return any 0 results--if a driver didn't deliver to a district there won't be a record.

That's where the second query (DistrictDrivers_sub2) comes in. It is a Cartesian product query (http://en.wikipedia.org/wiki/Cartesian_product) and it is based on both the Districts and the Employees table, but it doesn't link the two. Doing this forces the query to match up every driver to every district.

The last query is a LEFT JOIN from the second sub query to the first. This means it uses every driver and every district and if there isn't a match in the first, it shows that record, but with a zero value.

 

[toast]

Wow! Thank you very much for the detailed reply. I don't have the ability to test it out right now, but your explanations make sense.

Just to be clear, that is more of an FYI and doesn't really have any bearing on what you want to produce. Right? You are concerned about drivers (lngDriver) and not with loaders. You were just demonstrating the realtionships between the two tables?

Actually, some employees can be drivers and loaders, others just loaders. But for this report I'm not interested in which capacity they visit the district. So I'm presuming I just need to change the first query to:

SELECT tblDeliveries.lngDistrictID, tblEmployees.lngEmployeeID, Count(tblDeliveries.lngDeliveryID) AS Deliveries
FROM (tblDeliveries INNER JOIN tblShifts ON tblDeliveries.lngShiftID = tblShifts.lngShiftID) INNER JOIN tblEmployees
ON (tblShifts.lngDriver = tblEmployees.lngEmployeeID OR tblShifts.lngLoader = tblEmployees.lngEmployeeID)
GROUP BY tblDeliveries.lngDistrictID, tblEmployees.lngEmployeeID;

 

[plog]

I don't think that will work. I think you may need another query similar to _sub1 and then a union query to bring them together.

 

[Brianwarnock]

Can you drop the second join and replace it with a Where clause?

Brian

 

[Brianwarnock]

Btw my thinking in my first post was that in the first two queries could you create a field that concatenated district and employee then run an unmatched query using that field

Brian

 

[toast]

Thanks for more suggestions.

I can't seem to get it working right now. As an interim solution, I have just placed a combo box for the user to select the district they want to check.

It isn't ideal, but I will revisit this when I get more time to better explore both of your suggestions.

On a related note to broaden my own knowledge, I use that OR statement (Loader or Driver) in several other joins with apparent success... Did you mean that it wouldn't work in this particular case, or that OR statements in joins don't work in general?

 

[Brianwarnock]

I think that the OR will work, but I am always reluctant to argue with current users as I approach 7 years retired. I know that it can't, or at least when I was a lad, be done in the design grid but thought it ok in SQL.

Brian