By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,729 Members | 1,178 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,729 IT Pros & Developers. It's quick & easy.

Complex Query Criteria (fun challenge, anyone?)

P: 48
Given:
Expand|Select|Wrap|Line Numbers
  1. tblPurchase contains:
  2.      customerID
  3.      dtePickup   'estimated date a customer will return to the shop
  4.  
  5.  
  6. tblPickup contains:
  7.      dteArrived  'the actual date the customer returned
  8.      status      'set to "pickup complete" or "will return another day"
  9.  
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.
Aug 27 '07 #1
Share this Question
Share on Google+
5 Replies


Rabbit
Expert Mod 10K+
P: 12,427
Given:
Expand|Select|Wrap|Line Numbers
  1. tblPurchase contains:
  2.      customerID
  3.      dtePickup   'estimated date a customer will return to the shop
  4.  
  5.  
  6. tblPickup contains:
  7.      dteArrived  'the actual date the customer returned
  8.      status      'set to "pickup complete" or "will return another day"
  9.  
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.
Aug 27 '07 #2

P: 48
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?
Sep 5 '07 #3

Rabbit
Expert Mod 10K+
P: 12,427
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
Sep 5 '07 #4

P: 48
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

Expand|Select|Wrap|Line Numbers
  1. WHERE itemPickup = "will return"        'so, there's an entry event, but the item still needs to be picked up
  2. 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?
Sep 5 '07 #5

Rabbit
Expert Mod 10K+
P: 12,427
Good Luck.
Sep 5 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.