By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,607 Members | 1,972 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,607 IT Pros & Developers. It's quick & easy.

Null values in a query

P: n/a
Ian
Hi everyone,

I have been working on a database to overhaul the maintenance job
system here, and Ihave hit a problem when developing a query. I have a
table listing the details of each job and a table listing the plant
numbers of the machinery in the factory.

These two tables are joined with with a field in the job table listing
the plant number id.

If for some reason the plant number is unavailable and therefore left
blank, that record will not appear in a query, even if I include the
criteria Is Null or Like "*" etc.

Does anyone have any ideas?

Thanks in advance,

Ian
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Ian wrote:
Hi everyone,

I have been working on a database to overhaul the maintenance job
system here, and Ihave hit a problem when developing a query. I have a
table listing the details of each job and a table listing the plant
numbers of the machinery in the factory.

These two tables are joined with with a field in the job table listing
the plant number id.

If for some reason the plant number is unavailable and therefore left
blank, that record will not appear in a query, even if I include the
criteria Is Null or Like "*" etc.

Does anyone have any ideas?

Thanks in advance,

Ian


Do I take it to mean you're trying to join on a null column? Impossible[1].

Imagine two tables, Patients and BagsOfBlood, now join by blood type to
issue the bags for transfusion, if it matched up nulls on the join then
imagine the hilarious results.

[1] Well, you can frig it to do that but not advisable, in the above
example the nurses and families of the patients will be after your blood
for sure.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer
Nov 13 '05 #2

P: n/a
Ian
I don't think I am trying to join on a null column. The structure is
shown below (sorry for the poor diagram!).

Table 1
some fields Table 2
Plant ID -------------- ID
some fields Plant Number

If I then invoke a query to show all records in Table 1, any records
which do not have a value in the plant id field do not appear in the
query results, even if the query includes Is Null in the plant id
criteria.

Cheers,

Ian

Trevor Best <nospam@localhost> wrote in message news:<40**********************@auth.uk.news.easyne t.net>...

Do I take it to mean you're trying to join on a null column? Impossible[1].

Imagine two tables, Patients and BagsOfBlood, now join by blood type to
issue the bags for transfusion, if it matched up nulls on the join then
imagine the hilarious results.

[1] Well, you can frig it to do that but not advisable, in the above
example the nurses and families of the patients will be after your blood
for sure.

Nov 13 '05 #3

P: n/a
In query design view, you have two tables in your query.
Double-click the line joining the tables.
Access pops up a dialog with 3 choices.
Choose the one that says:
All records from Table 1, and any matches from Table2.

This changes the SQL of the query to an outer join.

More information:
The query lost my records!
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.

"Ian" <ia*********@maps-eu.com> wrote in message
news:f4**************************@posting.google.c om...
I don't think I am trying to join on a null column. The structure is
shown below (sorry for the poor diagram!).

Table 1
some fields Table 2
Plant ID -------------- ID
some fields Plant Number

If I then invoke a query to show all records in Table 1, any records
which do not have a value in the plant id field do not appear in the
query results, even if the query includes Is Null in the plant id
criteria.

Cheers,

Ian

Trevor Best <nospam@localhost> wrote in message

news:<40**********************@auth.uk.news.easyne t.net>...

Do I take it to mean you're trying to join on a null column? Impossible[1].
Imagine two tables, Patients and BagsOfBlood, now join by blood type to
issue the bags for transfusion, if it matched up nulls on the join then
imagine the hilarious results.

[1] Well, you can frig it to do that but not advisable, in the above
example the nurses and families of the patients will be after your blood
for sure.

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.