Complex Query Criteria (fun challenge, anyone?)

[isoquin]

Given:

tblPurchase contains:
     customerID
     dtePickup   'estimated date a customer will return to the shop
 
 
tblPickup contains:
     dteArrived  'the actual date the customer returned
     status      'set to "pickup complete" or "will return another day"
 

I need one query/report which shows all pending pickups, broken down by:
1) Customers who came to the store, but could not pick up their item that day and should be returning soon for that purpose (being, any tblPickup entry where status="will return another day"), and
2) Customers who are anticipated to pickup their item who haven't already, being tblPurchase.dtePickup does not have a corresponding tblPickup.dteArrived within 2 months of it.

I know how to do item #1 alone in a query. I know how to do the reverse of item #2 in a query (that is, find all tblPurchase.dteArrived records that are within DateAdd("m",(+/-)2,tblPickup.dtePickup). However, I have no clue how to ascertain if a corresponding tblPickup record doesn't exist when expected to within that range. So, how do I write query criteria for records that *don't* exist?

If there's some way to combine both #1 and #2 for easy reporting, that would be all the better. Thank you, as always.

[Rabbit]

Given:

tblPurchase contains:
     customerID
     dtePickup   'estimated date a customer will return to the shop
 
 
tblPickup contains:
     dteArrived  'the actual date the customer returned
     status      'set to "pickup complete" or "will return another day"
 

I need one query/report which shows all pending pickups, broken down by:
1) Customers who came to the store, but could not pick up their item that day and should be returning soon for that purpose (being, any tblPickup entry where status="will return another day"), and
2) Customers who are anticipated to pickup their item who haven't already, being tblPurchase.dtePickup does not have a corresponding tblPickup.dteArrived within 2 months of it.

I know how to do item #1 alone in a query. I know how to do the reverse of item #2 in a query (that is, find all tblPurchase.dteArrived records that are within DateAdd("m",(+/-)2,tblPickup.dtePickup). However, I have no clue how to ascertain if a corresponding tblPickup record doesn't exist when expected to within that range. So, how do I write query criteria for records that *don't* exist?

If there's some way to combine both #1 and #2 for easy reporting, that would be all the better. Thank you, as always.

I'm assuming there's a CustomerID field in tblPickup.

If you outer join the two tables together, any customer who hasn't attempted picked up an item will have null values in tblPickup.

Yes, you can join the two queries together by ORing the criteria.

Here's the problem. What happens when the same customer has more than one order? You should be linking by an OrderID field rather than a CustomerID field.

[isoquin]

Well, it is 100% guaranteed that the customer won't make multiple orders within 5 months of another, which is why I can safely look 2 months in either direction without problem.

I'm unsure of OuterJoin or ORing. Could you possibly point me to a good tutorial?

[Rabbit]

http://www.w3schools.com/sql/sql_join.asp
When I say outer join, that means left/right joins.

http://www.w3schools.com/sql/sql_and_or.asp

[isoquin]

EDIT: I'll keep the below post for any troubled googler, but I have the answer. I needed itemPickup Is Null, instead of =null

thanks everyone!

[/EDIT]

ok that was good - learned something - almost there.

So I have my left join query (which works great), and wanted to add where statements, along the lines of the following pseudocode

WHERE itemPickup = "will return"        'so, there's an entry event, but the item still needs to be picked up
OR itemPickup = Null        'the customer didn't come in at all

Now, if I just use the first line (with all appropriate parenthesis and accessories), it works. However, if I use anything like =Null or ="" then nothing comes out on the query. I must be screwing up the syntax, altho I get no angry Access messages.

Ideas?

[Rabbit]

Good Luck.