473,854 Members | 1,845 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

criteria matching with table is null

I am not sure if I am asking the right question in the subject here.
But, here is my problem. I have 5tables/queries with various data
like below.

Table1: CustomerID1, Field1, Field2
Table2: CustomerID1, Field5, Field6
Table3: CustomerID2, Field7, Field8 (customerID1 does not exist in
the table)
....

So, how do I create query to "skip" over table3 and show me if
criterias are met in table1 & table2, then show me the customer?
When I do a IsNull([CustomerID] with the table, it does not skip over,
it displays other customerIDs.
Here are the actual SQL:
SELECT Trim([ELastName] & ", " & [EFirstName]) AS FullName,
TNameInfo.ELast Name, TNameInfo.EFirs tName, TNameInfo.CName ,
TFellowship.Ini tial, TFellowship.FCh inese, Ability.NameID,
Ability.QID, Ability.TQuesti on.Description, Ability.CodeID,
Ability.TCode.D escription, Experience.QID,
Experience.TQue stion.Descripti on, Experience.Code ID,
Experience.TCod e.Description, Heart.QID, Heart.TQuestion .Description,
Heart.CodeID, Heart.TCode.Des cription, Personality.QID ,
Personality.TQu estion.Descript ion, Personality.Cod eID,
Personality.TCo de.Description, SpiritualGift.Q ID,
SpiritualGift.T Question.Descri ption, SpiritualGift.C odeID,
SpiritualGift.T Code.Descriptio n
FROM TFellowship INNER JOIN (((((SpiritualG ift INNER JOIN Personality
ON SpiritualGift.N ameID = Personality.Nam eID) INNER JOIN Heart ON
Personality.Nam eID = Heart.NameID) INNER JOIN Experience ON
Heart.NameID = Experience.Name ID) INNER JOIN Ability ON
Experience.Name ID = Ability.NameID) INNER JOIN TNameInfo ON
SpiritualGift.N ameID = TNameInfo.NameI D) ON TFellowship.Fel lowshipID =
TNameInfo.Fello wshipID
WHERE (((Ability.QID) Like IIf(IsNull([Forms]![0F_All]![AB1])=True,"*",
[Forms]![0F_All]![AB1])) AND ((Ability.CodeI D) Like IIf(IsNull([Forms]!
[0F_All]![AB2])=True,"*",[Forms]![0F_All]![AB2])) AND
((Experience.QI D) Like IIf(IsNull([Forms]![0F_All]![Ex1])=True,"*",
[Forms]![0F_All]![Ex1])) AND ((Experience.Co deID) Like
IIf(IsNull([Forms]![0F_All]![Ex2])=True,"*",[Forms]![0F_All]![Ex2]))
AND ((Heart.QID) Like IIf(IsNull([Forms]![0F_All]![Pa1])=True,"*",
[Forms]![0F_All]![Pa1])) AND ((Heart.CodeID) Like IIf(IsNull([Forms]!
[0F_All]![Pa2])=True,"*",[Forms]![0F_All]![Pa2])) AND
((Personality.Q ID) Like IIf(IsNull([Forms]![0F_All]![Pe2])=True,"*",
[Forms]![0F_All]![Pe2])) AND ((Personality.C odeID) Like
IIf(IsNull([Forms]![0F_All]![Pe1])=True,"*",[Forms]![0F_All]![Pe1]))
AND ((SpiritualGift .QID) Like IIf(IsNull([Forms]![0F_All]!
[SG1])=True,"*",[Forms]![0F_All]![SG1])) AND ((SpiritualGift .CodeID)
Like IIf(IsNull([Forms]![0F_All]![SG2])=True,"*",[Forms]![0F_All]!
[SG2])));

Any hint will be appreciated.

Perry
Jan 11 '08 #1
5 1819
On Jan 11, 3:18*pm, perry...@yahoo. com wrote:
I am not sure if I am asking the right question in the subject here.
But, here is my problem. *I have 5tables/queries with various data
like below.

Table1: CustomerID1, Field1, Field2
Table2: CustomerID1, Field5, Field6
Table3: CustomerID2, Field7, Field8 *(customerID1 does not exist in
the table)
...

So, how do I create query to "skip" over table3 and show me if
criterias are met in table1 & table2, then show me the customer?
When I do a IsNull([CustomerID] with the table, it does not skip over,
it displays other customerIDs.

Here are the actual SQL:
SELECT Trim([ELastName] & ", " & [EFirstName]) AS FullName,
TNameInfo.ELast Name, TNameInfo.EFirs tName, TNameInfo.CName ,
TFellowship.Ini tial, TFellowship.FCh inese, Ability.NameID,
Ability.QID, Ability.TQuesti on.Description, Ability.CodeID,
Ability.TCode.D escription, Experience.QID,
Experience.TQue stion.Descripti on, Experience.Code ID,
Experience.TCod e.Description, Heart.QID, Heart.TQuestion .Description,
Heart.CodeID, Heart.TCode.Des cription, Personality.QID ,
Personality.TQu estion.Descript ion, Personality.Cod eID,
Personality.TCo de.Description, SpiritualGift.Q ID,
SpiritualGift.T Question.Descri ption, SpiritualGift.C odeID,
SpiritualGift.T Code.Descriptio n
FROM TFellowship INNER JOIN (((((SpiritualG ift INNER JOIN Personality
ON SpiritualGift.N ameID = Personality.Nam eID) INNER JOIN Heart ON
Personality.Nam eID = Heart.NameID) INNER JOIN Experience ON
Heart.NameID = Experience.Name ID) INNER JOIN Ability ON
Experience.Name ID = Ability.NameID) INNER JOIN TNameInfo ON
SpiritualGift.N ameID = TNameInfo.NameI D) ON TFellowship.Fel lowshipID =
TNameInfo.Fello wshipID
WHERE (((Ability.QID) Like IIf(IsNull([Forms]![0F_All]![AB1])=True,"*",
[Forms]![0F_All]![AB1])) AND ((Ability.CodeI D) Like IIf(IsNull([Forms]!
[0F_All]![AB2])=True,"*",[Forms]![0F_All]![AB2])) AND
((Experience.QI D) Like IIf(IsNull([Forms]![0F_All]![Ex1])=True,"*",
[Forms]![0F_All]![Ex1])) AND ((Experience.Co deID) Like
IIf(IsNull([Forms]![0F_All]![Ex2])=True,"*",[Forms]![0F_All]![Ex2]))
AND ((Heart.QID) Like IIf(IsNull([Forms]![0F_All]![Pa1])=True,"*",
[Forms]![0F_All]![Pa1])) AND ((Heart.CodeID) Like IIf(IsNull([Forms]!
[0F_All]![Pa2])=True,"*",[Forms]![0F_All]![Pa2])) AND
((Personality.Q ID) Like IIf(IsNull([Forms]![0F_All]![Pe2])=True,"*",
[Forms]![0F_All]![Pe2])) AND ((Personality.C odeID) Like
IIf(IsNull([Forms]![0F_All]![Pe1])=True,"*",[Forms]![0F_All]![Pe1]))
AND ((SpiritualGift .QID) Like IIf(IsNull([Forms]![0F_All]!
[SG1])=True,"*",[Forms]![0F_All]![SG1])) AND ((SpiritualGift .CodeID)
Like IIf(IsNull([Forms]![0F_All]![SG2])=True,"*",[Forms]![0F_All]!
[SG2])));

Any hint will be appreciated.

Perry
Perry,
I am not sure that I fully understand your question, but try this.
Create a query for table3 that limits it the way you want to - taking
out or leaving in nulls. Then use that query instead of the table in
the above query.
HTH
P
Jan 12 '08 #2
P,
I am sorry for being so confusing. I just don't know how to
ask.,.. let me give it one more try: (In general, this is a problem
because the table was inproperly designed in the first place, that is
why I am trying to "program" around it.)

I am simplifying the situation here:

Table1: CustomerID, Field1
Table2: CustomerID, Field2
Table3: CustomerID, Field3

Then I have a blank form with 3 fields: FormField1, FormField2 &
FormField3

If Table1Field1=Fo rmField1 and Table2Field2=Fo rmField2 and
Table3Field3=Fo rmField3, then display the Common Customer ID (Who is
it?)

Assuming the first 2 criteria are met for CustomerID = 11, but the
third criteria, in table 3, since CutomerID #11 does not even exist, I
would like for the query result to include CustomerID #11. (Because
the info was missing does not mean it may not match, we just fail to
create a customerID #11 for table3.) In other words, how do I not
exclude results CustomerID #11 from the result even if Table3 does not
contain customerID #11's data?

FYI: The above tables are actual queries (everything is built into
one big table, not so good design in the beginning by someone else).
I am just trying to savage this. Hope this help a little.

Perry
Jan 13 '08 #3
On Jan 12, 7:01*pm, perry...@yahoo. com wrote:
P,
* * *I am sorry for being so confusing. *I just don't know how to
ask.,.. let me give it one more try: *(In general, this is a problem
because the table was inproperly designed in the first place, that is
why I am trying to "program" around it.)

I am simplifying the situation here:

Table1: CustomerID, Field1
Table2: CustomerID, Field2
Table3: CustomerID, Field3

Then I have a blank form with 3 fields: FormField1, FormField2 &
FormField3

If Table1Field1=Fo rmField1 and Table2Field2=Fo rmField2 and
Table3Field3=Fo rmField3, then display the Common Customer ID (Who is
it?)

Assuming the first 2 criteria are met for CustomerID = 11, but the
third criteria, in table 3, since CutomerID #11 does not even exist, I
would like for the query result to include CustomerID #11. *(Because
the info was missing does not mean it may not match, we just fail to
create a customerID #11 for table3.) * In other words, how do I not
exclude results CustomerID #11 from the result even if Table3 does not
contain customerID #11's data?

FYI: *The above tables are actual queries (everything is built into
one big table, not so good design in the beginning by someone else).
I am just trying to savage this. *Hope this help a little.

Perry
if CustomerID is unique in each table (no duplictes) then you just
neet to do an outer join (left or right).
SELECT * FROM (table1 INNER JOIN table2 ON
table1.customer id=table2.custo merid) LEFT JOIN table3 on
table1.customer id=table3.custo merid
WHERE table1.customer ID=11
When you create your joins, in the query editor, double click on the
line that joins the two tables and you can choose an outer join.

if CustomerID is not unique and you are using other criteria to limit
your query, you will have to do some other stuff.
Jan 15 '08 #4
This does not work too well for me, because like I described, the main
problem is some table do not have that particular customerID at all.
But I do not want to exclude those customerID that does not exist in a
particular table. What I am seeing is when I just search 1 query, it
yields 6 records; but when I search thru this inner/outer join as you
suggested, it yields only 4 records (because 1 of the tables do not
contain that particular customerID). Thanks for your help, any other
thoughts?

Perry
Jan 16 '08 #5
On Jan 15, 6:11*pm, perry...@yahoo. com wrote:
This does not work too well for me, because like I described, the main
problem is some table do not have that particular customerID at all.
But I do not want to exclude those customerID that does not exist in a
particular table. *What I am seeing is when I just search 1 query, it
yields 6 records; but when I search thru this inner/outer join as you
suggested, it yields only 4 records (because 1 of the tables do not
contain that particular customerID). *Thanks for your help, any other
thoughts?

Perry
You need to create two separate queries and put one within the other.
Create a query on table1 and 2 that has the criteria in it (query1)
and then create another query (query2) that does a left join between
query1 and 2
You can also do this in a derived table if you are daring enough.

Query1:
SELECT * FROM (table1 INNER JOIN table2 ON
table1.customer id=table2.custo merid)
WHERE table1.customer ID=11

Query2:
Select * FROM query1 LEFT JOIN table3 on
query1.whatever =table3.whateve r

HTH
Feb 1 '08 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
3008
by: M Wells | last post by:
Hi All, Sorry if the subject line is too obscure -- I couldn't think of a way of describing this request. I have a table that contains approximately 1 million records. I want to be able to be able to select the top x records out of this table matching variable criteria.
8
2974
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...
2
1515
by: MLH | last post by:
Why does this one produce 9 records... SELECT DISTINCT tblVehicleJobs.VehicleJobID, tblVehicleJobs.Reclaimed, tblVehicleJobs.VSaleID, tblVehicleJobs.ENF262Written FROM tblVehicleJobs WHERE (((tblVehicleJobs.Reclaimed)=No) AND ((tblVehicleJobs.VSaleID) Is Null) AND ((tblVehicleJobs.ENF262Written)=True)); And this one produces NONE... SELECT DISTINCT tblVehicleJobs.Reclaimed, tblVehicleJobs.VSaleID,
3
4819
by: Rebekkah | last post by:
I did a search but couldn't find a question similar to mine. I need to count the values in a textbox on a report and have the count subtotal in a group footer. But I need three different counts on the same textbox because there are three possible values and I need a count on each. I can't just do a simple DCount on the table and manually define the criteria because there are many different groups that will need the counts for only that group....
16
6430
by: The Frog | last post by:
Hi Everyone, I have a small problem that doesnt seem to make any sense. I am using Access 97, and have a query that selects data from a text field, converts it to type Lng. This seems to work fine. When I try and apply a criteria for filtering is get the data type mismatch rubbish that Access likes to trow at you. Here is the SQL:
1
1693
klarae99
by: klarae99 | last post by:
I am new to this forum and new to Access 2003...this is my first database that is more than a table and some reports. The database I am working on is for inventory control. My current project is to produce a report by retail location that tells the available stock. I have choosen to create a form that will imput data to a queary that a report is based on. My form has a 5 button option group with an invisable textbox linked to the...
8
2488
by: limperger | last post by:
Hello everyone! First and foremost, my apologies for the title of the post. It is not very clarifying of what the problem is about, but I didn't know how to put it... My problem is as follows: I have a parameter query consisting of 2 fields (month and year). The parameter prompts the user to enter the month and the year, that is, . When both are entered, there is no problem. Nevertheless, users may want only to enter the year, so as to...
2
3671
by: Denise | last post by:
Front end is Access 2002, back end is linked Oracle tables. My users need to describe things in feet and inches and want to use the standard ' and " abbrevations. On a testing form I go to a record with double or single quotes in the location description and click a button to see if Access can tell me the ID number and find its way back to the original record. When the text contains double quotes the FindFirst works but the Dloookup...
8
2787
by: Dr Al | last post by:
I have a table with four date fields, some of which may not be filled in based on our data entry needs. I have a criteria set as <date()-180 which is supposed to pull dates older than 180 days ago. The problem is that when I use that criteria for all four fields I am not getting the expected results. I am trying to find out from this query is the date in date field one is older than 180, same thing for the other three date fields. For...
0
10672
jinu1996
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...
1
10740
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,...
0
10362
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9509
agi2029
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...
1
7909
isladogs
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...
0
7076
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5738
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...
1
4550
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
2
4147
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.