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
5  1811 
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
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
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.
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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.
|
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: 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,
|
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....
|
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:
| | |
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...
|
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...
|
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...
|
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...
|
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: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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: 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...
|
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: 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();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
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...
| |
