Hi, we run a delivery service where usually one employee makes both the pickup and dropoff but sometimes the delivery may be picked up by one employee, brought to the central area and then delivered by another employee. I have a Deliveries table and an Employees table. The deliveries table has a field PickupAssignedTo and DropoffAssignedTo.
I seek to create a query as the basis for a report that will contain each employees' assignments for the day. I'm having trouble figuring out what type of join(s) to create between the two fields so that only unique values are returned. I.e. if the same employee is performing both the pickup and the dropoff for the delivery to only contain that delivery in the query results once.
I'm having a similar problem with the delivery locations. There are two fields in the Deliveries table: PickupLocation and DropoffLocation. I'm trying to create a list of events (pickup or dropoff) by location so we can easily see what will have to be done at each location on a given day. (Most of the deliveries have been prescheduled by the beginning of the day.) What types of joins must be created between the deliveries and locations table in this query? Do I need two separate queries as the basis of a third query that returns unique records? Is there another way to do this?