473,289 Members | 2,040 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

query does not extract records if blank fields are included

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
Nov 12 '05 #1
1 3648
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

Nov 12 '05 #2

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

Similar topics

2
by: lawrence | last post by:
A very strange bug. www.monkeyclaus.org is run by a cms I'm developing. One of types of users we allow is "justTestingTheSite", a type of user I developed to give demo's to prospective clients. The...
8
by: Maxi | last post by:
There is a lotto system which picks 21 numbers every day out of 80 numbers. I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21) Here is the structure and sample data: ...
9
by: MLH | last post by:
I have a table (tblCorrespondence) holding records with fields like , , , , , , , etc... About a dozen 's are defined and I often use queries to extract records of a given . That's pretty easy....
4
by: Macroman | last post by:
MS Access XP, running on Win XP, Processor 2.4Ghz , 512Mb RAM, 40Gb Hard drive Table 1 has 167,000 records and contains the following fields tblone_custID tblone_easting tblone_northing ...
36
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...
16
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...
2
by: Will | last post by:
Hi, I need to handle blank values in a query calculation. I have 636 records at the moment but when I sum over these records the blank fields are not returned. I have looked around here and on...
7
by: nofear | last post by:
I have a table with 2 fields where one has duplicates and the other does not something like this: ID field1 field2 1 A A 2 A B 3 A C...
9
by: Sinner | last post by:
Hi, I have a field name 'USER' in tableMAIN. How do I replace the user names with corresponding user names. I can do that in xl using vlookup but now I'm trying to find a way to do that in...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

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.