How do you make a Query that shows records even if a field is blan

  • Thread starter Thread starter Query Trouble
  • Start date Start date
Q

Query Trouble

I have a query with 10 fields. When I Run the query the only records that
show are the ones that have information in all the fields... I want records
to show even if they only have 7, or 8 of the fields.
Example: A query has the fields FIRST, LAST, EMAIL, UNIVERSITY...
- I want a record to show even if IT has FIRST LAST EMAIL but Nothing in
UNiversity...
HE is my SQL:

SELECT [1 Email Contacts].[Email Address], [1 Email Contacts].[First Name],
[1 Email Contacts].[Last Name], [1 Email Contacts].School, [6 RFI].Date, [6
RFI].Requested, [2 Email Campaigns].[Email Campaign Name], [4 Links
Table].[Link Name], [5 Links Clicked].[Click Count], [5 Links Clicked].Date
FROM ([2 Email Campaigns] INNER JOIN [4 Links Table] ON [2 Email
Campaigns].[Email Campaign ID] = [4 Links Table].[Email Campaign ID]) INNER
JOIN (([1 Email Contacts] INNER JOIN [6 RFI] ON [1 Email Contacts].[Email
Address] = [6 RFI].[Email Address]) INNER JOIN [5 Links Clicked] ON [1 Email
Contacts].[Email Address] = [5 Links Clicked].[Email Address]) ON [4 Links
Table].[Link ID] = [5 Links Clicked].[Links ID];


Help please!
 
You will need to change the INNER JOIN's to LEFT JOIN, RIGHT JOIN, or some
combination of the left and right joins.

I recommend starting with a query joining only two of the tables. Make sure
that you get the records you want with a LEFT or RIGHT join. Then add the
next table to the query and test. Do so until you have all the tables and
data that you need.
 
Thank you , Jerry
Yes I tried making them all LEFT then all RIGHT both of those didnt work...
I am not 100% familiar with the SQL lingo do you know the combination I need?

Jerry Whittle said:
You will need to change the INNER JOIN's to LEFT JOIN, RIGHT JOIN, or some
combination of the left and right joins.

I recommend starting with a query joining only two of the tables. Make sure
that you get the records you want with a LEFT or RIGHT join. Then add the
next table to the query and test. Do so until you have all the tables and
data that you need.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Query Trouble said:
I have a query with 10 fields. When I Run the query the only records that
show are the ones that have information in all the fields... I want records
to show even if they only have 7, or 8 of the fields.
Example: A query has the fields FIRST, LAST, EMAIL, UNIVERSITY...
- I want a record to show even if IT has FIRST LAST EMAIL but Nothing in
UNiversity...
HE is my SQL:

SELECT [1 Email Contacts].[Email Address], [1 Email Contacts].[First Name],
[1 Email Contacts].[Last Name], [1 Email Contacts].School, [6 RFI].Date, [6
RFI].Requested, [2 Email Campaigns].[Email Campaign Name], [4 Links
Table].[Link Name], [5 Links Clicked].[Click Count], [5 Links Clicked].Date
FROM ([2 Email Campaigns] INNER JOIN [4 Links Table] ON [2 Email
Campaigns].[Email Campaign ID] = [4 Links Table].[Email Campaign ID]) INNER
JOIN (([1 Email Contacts] INNER JOIN [6 RFI] ON [1 Email Contacts].[Email
Address] = [6 RFI].[Email Address]) INNER JOIN [5 Links Clicked] ON [1 Email
Contacts].[Email Address] = [5 Links Clicked].[Email Address]) ON [4 Links
Table].[Link ID] = [5 Links Clicked].[Links ID];


Help please!
Click to expand...
Click to expand...
 
No I don't. It depends all on the relationships between the tables. That's
why I suggested making a simple query between two tables and building up from
there table by table. There is a possibility that the relationships between
the tables won't allow it to happen at all.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Query Trouble said:
Thank you , Jerry
Yes I tried making them all LEFT then all RIGHT both of those didnt work...
I am not 100% familiar with the SQL lingo do you know the combination I need?

Jerry Whittle said:
You will need to change the INNER JOIN's to LEFT JOIN, RIGHT JOIN, or some
combination of the left and right joins.

I recommend starting with a query joining only two of the tables. Make sure
that you get the records you want with a LEFT or RIGHT join. Then add the
next table to the query and test. Do so until you have all the tables and
data that you need.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Query Trouble said:
I have a query with 10 fields. When I Run the query the only records that
show are the ones that have information in all the fields... I want records
to show even if they only have 7, or 8 of the fields.
Example: A query has the fields FIRST, LAST, EMAIL, UNIVERSITY...
- I want a record to show even if IT has FIRST LAST EMAIL but Nothing in
UNiversity...
HE is my SQL:

SELECT [1 Email Contacts].[Email Address], [1 Email Contacts].[First Name],
[1 Email Contacts].[Last Name], [1 Email Contacts].School, [6 RFI].Date, [6
RFI].Requested, [2 Email Campaigns].[Email Campaign Name], [4 Links
Table].[Link Name], [5 Links Clicked].[Click Count], [5 Links Clicked].Date
FROM ([2 Email Campaigns] INNER JOIN [4 Links Table] ON [2 Email
Campaigns].[Email Campaign ID] = [4 Links Table].[Email Campaign ID]) INNER
JOIN (([1 Email Contacts] INNER JOIN [6 RFI] ON [1 Email Contacts].[Email
Address] = [6 RFI].[Email Address]) INNER JOIN [5 Links Clicked] ON [1 Email
Contacts].[Email Address] = [5 Links Clicked].[Email Address]) ON [4 Links
Table].[Link ID] = [5 Links Clicked].[Links ID];


Help please!
Click to expand...
Click to expand...
Click to expand...
 
OK I am very close... in total I should have 139 Records....I have 137...
It is showing all the records from one chart but not the other... I have a
combination of RIGHT, RIGHT LEFT, RIGHT.. do you know how to fix this?

Jerry Whittle said:
No I don't. It depends all on the relationships between the tables. That's
why I suggested making a simple query between two tables and building up from
there table by table. There is a possibility that the relationships between
the tables won't allow it to happen at all.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Query Trouble said:
Thank you , Jerry
Yes I tried making them all LEFT then all RIGHT both of those didnt work...
I am not 100% familiar with the SQL lingo do you know the combination I need?

Jerry Whittle said:
You will need to change the INNER JOIN's to LEFT JOIN, RIGHT JOIN, or some
combination of the left and right joins.

I recommend starting with a query joining only two of the tables. Make sure
that you get the records you want with a LEFT or RIGHT join. Then add the
next table to the query and test. Do so until you have all the tables and
data that you need.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have a query with 10 fields. When I Run the query the only records that
show are the ones that have information in all the fields... I want records
to show even if they only have 7, or 8 of the fields.
Example: A query has the fields FIRST, LAST, EMAIL, UNIVERSITY...
- I want a record to show even if IT has FIRST LAST EMAIL but Nothing in
UNiversity...
HE is my SQL:

SELECT [1 Email Contacts].[Email Address], [1 Email Contacts].[First Name],
[1 Email Contacts].[Last Name], [1 Email Contacts].School, [6 RFI].Date, [6
RFI].Requested, [2 Email Campaigns].[Email Campaign Name], [4 Links
Table].[Link Name], [5 Links Clicked].[Click Count], [5 Links Clicked].Date
FROM ([2 Email Campaigns] INNER JOIN [4 Links Table] ON [2 Email
Campaigns].[Email Campaign ID] = [4 Links Table].[Email Campaign ID]) INNER
JOIN (([1 Email Contacts] INNER JOIN [6 RFI] ON [1 Email Contacts].[Email
Address] = [6 RFI].[Email Address]) INNER JOIN [5 Links Clicked] ON [1 Email
Contacts].[Email Address] = [5 Links Clicked].[Email Address]) ON [4 Links
Table].[Link ID] = [5 Links Clicked].[Links ID];


Help please!
Click to expand...
Click to expand...
Click to expand...
 
Create a union query like this --
SELECT [1 Email Contacts].[Email Address] AS [All_E-Mail]
FROM [1 Email Contacts]
UNION SELECT [2 Email Campaigns].[Email Campaign ID] AS [All_E-Mail]
FROM [2 Email Campaigns]
UNION SELECT [4 Links Table].[Email Campaign ID] AS [All_E-Mail]
FROM [4 Links Table]
UNION SELECT [5 Links Clicked].[Email Address] AS [All_E-Mail]
FROM [5 Links Clicked]
UNION SELECT [6 RFI].[Email Address] AS [All_E-Mail]
FROM [6 RFI];

I may have some that are not e-mail fields so leave them out, the left join
this query to the tables with e-mail fields. Further left join those to the
other tables on the appropriate fields.

Query Trouble said:
OK I am very close... in total I should have 139 Records....I have 137...
It is showing all the records from one chart but not the other... I have a
combination of RIGHT, RIGHT LEFT, RIGHT.. do you know how to fix this?

Jerry Whittle said:
No I don't. It depends all on the relationships between the tables. That's
why I suggested making a simple query between two tables and building up from
there table by table. There is a possibility that the relationships between
the tables won't allow it to happen at all.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Query Trouble said:
Thank you , Jerry
Yes I tried making them all LEFT then all RIGHT both of those didnt work...
I am not 100% familiar with the SQL lingo do you know the combination I need?

:

You will need to change the INNER JOIN's to LEFT JOIN, RIGHT JOIN, or some
combination of the left and right joins.

I recommend starting with a query joining only two of the tables. Make sure
that you get the records you want with a LEFT or RIGHT join. Then add the
next table to the query and test. Do so until you have all the tables and
data that you need.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have a query with 10 fields. When I Run the query the only records that
show are the ones that have information in all the fields... I want records
to show even if they only have 7, or 8 of the fields.
Example: A query has the fields FIRST, LAST, EMAIL, UNIVERSITY...
- I want a record to show even if IT has FIRST LAST EMAIL but Nothing in
UNiversity...
HE is my SQL:

SELECT [1 Email Contacts].[Email Address], [1 Email Contacts].[First Name],
[1 Email Contacts].[Last Name], [1 Email Contacts].School, [6 RFI].Date, [6
RFI].Requested, [2 Email Campaigns].[Email Campaign Name], [4 Links
Table].[Link Name], [5 Links Clicked].[Click Count], [5 Links Clicked].Date
FROM ([2 Email Campaigns] INNER JOIN [4 Links Table] ON [2 Email
Campaigns].[Email Campaign ID] = [4 Links Table].[Email Campaign ID]) INNER
JOIN (([1 Email Contacts] INNER JOIN [6 RFI] ON [1 Email Contacts].[Email
Address] = [6 RFI].[Email Address]) INNER JOIN [5 Links Clicked] ON [1 Email
Contacts].[Email Address] = [5 Links Clicked].[Email Address]) ON [4 Links
Table].[Link ID] = [5 Links Clicked].[Links ID];


Help please!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top