query does not extract records if blank fields are included

[Robert Marshall]

Hi all,

Hoping someone can help me with an issue I'm having.

I'm using AccessXP to create a database of U.S. Census records. After
keying the data in, I decided to run a query to create a report for
each domocile address, and the three names that appear at each street
address for the three censii.

After running the query, the report skips over any "address" that
might have an incomplete result (i.e.: 1900 Linden St had people
living there in 1900 and 1920, however since 1910 was vacant, that
address was not included in the report).

Is there a way to allow the query to include addresses with null
values in the query (i.e.: include non-complete records)?

Here is the SQL query that I am using:

SELECT [Street Addresses].[1910 Address], [Street Addresses].[1920
Address], [Street Addresses].[Street Name] AS [Street Addresses_Street
Name], [1920 Census].[street address] AS [1920 Census_street address],
[1920 Census].[street name] AS [1920 Census_street name], [1920
Census].[first name] AS [1920 Census_first name], [1920 Census].[last
name] AS [1920 Census_last name], [1920 Census].relation AS [1920
Census_relation], [1910 Census].[street address] AS [1910
Census_street address], [1910 Census].[street name] AS [1910
Census_street name], [1910 Census].[first name] AS [1910 Census_first
name], [1910 Census].[last name] AS [1910 Census_last name], [1910
Census].relation AS [1910 Census_relation], [1900 Census].[street
name] AS [1900 Census_street name], [1900 Census].[street address] AS
[1900 Census_street address], [1900 Census].[last name] AS [1900
Census_last name], [1900 Census].[first name] AS [1900 Census_first
name], [1900 Census].relation AS [1900 Census_relation], [1910
Census].Race
FROM (([Street Addresses] INNER JOIN [1910 Census] ON ([Street
Addresses].[1910 Address] = [1910 Census].[street address]) AND
([Street Addresses].[Street Name] = [1910 Census].[street name]))
INNER JOIN [1920 Census] ON ([Street Addresses].[1920 Address] = [1920
Census].[street address]) AND ([Street Addresses].[Street Name] =
[1920 Census].[street name])) INNER JOIN [1900 Census] ON ([Street
Addresses].[1910 Address] = [1900 Census].[street address]) AND
([Street Addresses].[Street Name] = [1900 Census].[street name])
WHERE ((([1920 Census].relation)="Head") AND (([1910
Census].relation)="Head") AND (([1900 Census].relation)="Head"))
ORDER BY [1910 Census].Race;

NOTE: address numbers changed between in 1912 so I created a table
called "Street Addresses" to keep track of which 1900/10 address is
equivilent to a 1920 address

[Allen Browne]

Try outer joins instead of inner joins, and change the WHERE clause to get
Nulls as well.

Details of both issues in article:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robert Marshall" <ro******@hockeyhockeyhockey.com> wrote in message
news:42**************************@posting.google.c om...

Hi all,

Hoping someone can help me with an issue I'm having.

I'm using AccessXP to create a database of U.S. Census records. After
keying the data in, I decided to run a query to create a report for
each domocile address, and the three names that appear at each street
address for the three censii.

After running the query, the report skips over any "address" that
might have an incomplete result (i.e.: 1900 Linden St had people
living there in 1900 and 1920, however since 1910 was vacant, that
address was not included in the report).

Is there a way to allow the query to include addresses with null
values in the query (i.e.: include non-complete records)?

Here is the SQL query that I am using:

SELECT [Street Addresses].[1910 Address], [Street Addresses].[1920
Address], [Street Addresses].[Street Name] AS [Street Addresses_Street
Name], [1920 Census].[street address] AS [1920 Census_street address],
[1920 Census].[street name] AS [1920 Census_street name], [1920
Census].[first name] AS [1920 Census_first name], [1920 Census].[last
name] AS [1920 Census_last name], [1920 Census].relation AS [1920
Census_relation], [1910 Census].[street address] AS [1910
Census_street address], [1910 Census].[street name] AS [1910
Census_street name], [1910 Census].[first name] AS [1910 Census_first
name], [1910 Census].[last name] AS [1910 Census_last name], [1910
Census].relation AS [1910 Census_relation], [1900 Census].[street
name] AS [1900 Census_street name], [1900 Census].[street address] AS
[1900 Census_street address], [1900 Census].[last name] AS [1900
Census_last name], [1900 Census].[first name] AS [1900 Census_first
name], [1900 Census].relation AS [1900 Census_relation], [1910
Census].Race
FROM (([Street Addresses] INNER JOIN [1910 Census] ON ([Street
Addresses].[1910 Address] = [1910 Census].[street address]) AND
([Street Addresses].[Street Name] = [1910 Census].[street name]))
INNER JOIN [1920 Census] ON ([Street Addresses].[1920 Address] = [1920
Census].[street address]) AND ([Street Addresses].[Street Name] =
[1920 Census].[street name])) INNER JOIN [1900 Census] ON ([Street
Addresses].[1910 Address] = [1900 Census].[street address]) AND
([Street Addresses].[Street Name] = [1900 Census].[street name])
WHERE ((([1920 Census].relation)="Head") AND (([1910
Census].relation)="Head") AND (([1900 Census].relation)="Head"))
ORDER BY [1910 Census].Race;

NOTE: address numbers changed between in 1912 so I created a table
called "Street Addresses" to keep track of which 1900/10 address is
equivilent to a 1920 address