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.dte Pickup does not have a corresponding tblPickup.dteAr rived 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.dte Arrived records that are within DateAdd("m",(+/-)2,tblPickup.dt ePickup). 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.
5 1524 Rabbit 12,516
Recognized Expert Moderator MVP
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.dte Pickup does not have a corresponding tblPickup.dteAr rived 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.dte Arrived records that are within DateAdd("m",(+/-)2,tblPickup.dt ePickup). 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.
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 12,516
Recognized Expert Moderator MVP
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 12,516
Recognized Expert Moderator MVP Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Steve Jorgensen |
last post by:
Mailing List management is a good example of a case where my conundrum arises.
Say there is a m-m relationship between parties and groups - anyone can be a
member of any combintation of groups. Now, let's say the user wants to be
able to send mailings to people who have various combinations of membership
and non-membership in those groups.
Here's a medium-complex example: (Knitting Group or Macrame Group) and Active
Contact and Mailing...
|
by: Liam.M |
last post by:
hey guys,
I have one last problem to fix, and then my database is essentially
done...I would therefore very much appreciate any assistance anyone
would be able to provide me with.
Currently I have set up a Query to show only records that meet a
certain criteria...therefore excluding all of the records that do not
meet this criteria (just for the record the criteria is any record
within my database that falls within two months of its "Due...
|
by: Wired Hosting News |
last post by:
I tried to be breif and give a scenario so as not to be overlooked because
it was soooo long. Let me give you real world.
I am a manufacturer of goods and produce 11 items that are distributed to
1800 stores of a national home improvement chain store. Every week I
electronicaly receive an excel spreadsheet "inventory report" with 19,800
rows or records, which I import into my tblSalesData table. The table now
has 10 weeks of data or...
|
by: Mark Roughton |
last post by:
I have a form where the users need to view records for various
criteria, one of which is a date field on which they may wish to view
all related data for the selected date, for all dates upto and icluding
the selected date or all records on or after the selected date
The user selects either "=", >=" or "<=" from a combo box and then a
date from another combobox. The combination of thse two choices is
then set in an unbound textbox so...
|
by: Tererai |
last post by:
Hi guys? I am new to this community, i'm very pleased to join you.
For the past 2 weeks i have been trying to specify criteria in a crosstab query but i get a fun message which says"Microsoft Jet Database does not recognize whatever criteria i would have specified as valid field name or expression."
I am stuck and don't know what to do next.
| |
by: RacerX2000 |
last post by:
I have an MS access Database (2000) and Have created a form that sets a variable to a value I would like (Based on other selections in the form) to pass to my query criteria and I get the following results.
If the Form Variable = "10" - everything works fine
If the Form Variable = "Between 10 And 12" - it tells me the criteria is too complex
If I paste the exact text from the variable ie "Between 10 And 12" into the query it works fine
...
|
by: binky |
last post by:
Question for all you SQL gurus out there. I have a (seemingly) complex query to write, and since
I'm just learning SQL server, am not sure how to go about it.
Tables:
t_trans
t_account
All tables are bound by an SSN column ("SSN").
t_trans has the bulk of the data I need, and includes the following data:
|
by: Coll |
last post by:
I'm receiving this message when running a query...
This expression is typed incorrectly or is too complex to be
evaluated. For example, a numeric expression may contain too many
complicated elements. Try simplifying the expression by assigning
parts of the expression to variables.
Here's the background. I have a query. I've figured out through
cutting and pasting which field is the culprit. So I have a field with
the following info...
|
by: dizzydangler |
last post by:
Hi all,
I am a new Access user and just starting to get my head around some of the basic concepts, so please take it easy on me :) My company has been managing client records on excel, and I’m in the process of migrating the data over to Access 2007 (Windows XP), kind of learning as I go. I’ve managed to import the client records into a single table, and set up a “single view” form that streamlines how we input new client data. Now I’m...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |