Missing Values In A Query

[Tedd]

I have two tables that I am running a query on. Both
tables contain columns with values and a date for each
value.
For example:
TABLE1 TABLE2
NAME DATE NAME DATE
abc 2004-01-02 efg 2004-03-02
xyz 1999-12-05 mno 2003-11-09

In my query I selecting rows that are <= to a date that I
enter. So if I select rows that are less then or equal to
2003-12-01 I get the last two rows in the table.

THE PROBLEM is that if I try to select a date that is not
in the date range I give I don't get any results. So if I
try selecting everything that is <= to 2001-01-01 I don't
get any results because the minimum date on TABLE2 is 2003-
11-09. I know that only one date meets the criteria, not
both, and that's why I don't get anything. So...

MY QUESTION IS: how do I get Access to put a value or
something(like N/A) in the fields where the query couldn't
return any results? So if I am trying to find everything
that is <= to 2001-01-01 from TABLE1 and TABLE2 I want to
get:
xyz 1999-12-05 N/A N/A.

I hope this makes since. Any help would be appreciated.

Thanks

 

[Steve Schapel]

Tedd,

It is not clear (to me, anyway) what you are trying to do. What is the
connection, in your example, between xyz and mno? Where are you
applying your date criteria... to both the date fields? It seems to me
that you would get the results you are after if your criteria was...
WHERE Table1.[Date] <= #1-01-2001# Or Table2.[Date] <= #1-01-2001#
Alternatively, it may be appropriate to use a Union Query. But anyway,
at this stage I am only guessing... can you give some more details?

 

[Guest]

I should have explained my tables better. This is kind of
what they look like:

TABLE1 TABLE2
EmpNUM Date Info EmpNUM Date Info
789 2004-01-01 XYZ 789 2004-02-06 ABC
789 1999-11-09 THX 789 2003-05-14 QWR

The tables are connected by the employee number(EmpNUM).
I have joined these tables together by the EmpNUM field.
So when I run the query I enter a value for EmpNUM and a
Date. The logic for the date is that I only want records
that are <= the date I provide and equal to the employee
number I give. (There are muliple rows on both tables with
the same EmpNUM)

The query works if I enter a date that is within the range
of both date fields. So if I want records that are <= to
2003-12-23 I get the last row from each table. But if I
enter the date as 2002-01-01 the query doen't bring back
anything.

What I would like for it to do is to (in the case of my
example) give me the last row from TABLE1 and, if it can't
find anything from TABLE2, I would like it to save N/A or
something in the missing fields.

I have done this with DB2 SQL by unioning two tables
together: the first query in the union selects the fields
if it meets with ask for and the second query would return
a literal if it dosn't find anything. Thay way the query
always returns something.

I hope that helps. And thanks for taking the time to
reply.

-----Original Message-----
Tedd,

It is not clear (to me, anyway) what you are trying to do. What is the
connection, in your example, between xyz and mno? Where are you
applying your date criteria... to both the date fields? It seems to me
that you would get the results you are after if your criteria was...
WHERE Table1.[Date] <= #1-01-2001# Or Table2.[Date] <= #1- 01-2001#
Alternatively, it may be appropriate to use a Union Query. But anyway,
at this stage I am only guessing... can you give some more details?

--
Steve Schapel, Microsoft Access MVP

I have two tables that I am running a query on. Both
tables contain columns with values and a date for each
value.
For example:
TABLE1 TABLE2
NAME DATE NAME DATE
abc 2004-01-02 efg 2004-03-02
xyz 1999-12-05 mno 2003-11-09

In my query I selecting rows that are <= to a date that I
enter. So if I select rows that are less then or equal to
2003-12-01 I get the last two rows in the table.

THE PROBLEM is that if I try to select a date that is not
in the date range I give I don't get any results. So if I
try selecting everything that is <= to 2001-01-01 I don't
get any results because the minimum date on TABLE2 is 2003-
11-09. I know that only one date meets the criteria, not
both, and that's why I don't get anything. So...

MY QUESTION IS: how do I get Access to put a value or
something(like N/A) in the fields where the query couldn't
return any results? So if I am trying to find everything
that is <= to 2001-01-01 from TABLE1 and TABLE2 I want to
get:
xyz 1999-12-05 N/A N/A.

I hope this makes since. Any help would be appreciated.

Thanks

.

 

[Steve Schapel]

Tedd,

Can you say why it is that some of this information is in one table and
some in the other?

As mentioned in my earlier reply, it seems to me that a Union Query
would be appropriate here.

 

[Victor Delgadillo]

Don't relate the tables, because both have the same type of information!!
Make a union query, which will add one table to the other making one long
table. Then filter by whatever you need, be it dates, employee number, etc.
As a matter of fact, I would have ALL the data in one table! There is no
need to have two tables except if they are coming from, let say, different
branches or offices. In either case, it would be better to make one single
long table and then work by filtering the date in whichever way you wish.

The information you are relating is not related!! You are merely linking two
distinct records into one, which is why you cannot filter by only one of the
dates.

--
Victor Delgadillo [MVP Access]
Miami, Florida

Consultas al grupo, asi todos nos beneficiamos.

_

I should have explained my tables better. This is kind of
what they look like:

TABLE1 TABLE2
EmpNUM Date Info EmpNUM Date Info
789 2004-01-01 XYZ 789 2004-02-06 ABC
789 1999-11-09 THX 789 2003-05-14 QWR

The tables are connected by the employee number(EmpNUM).
I have joined these tables together by the EmpNUM field.
So when I run the query I enter a value for EmpNUM and a
Date. The logic for the date is that I only want records
that are <= the date I provide and equal to the employee
number I give. (There are muliple rows on both tables with
the same EmpNUM)

The query works if I enter a date that is within the range
of both date fields. So if I want records that are <= to
2003-12-23 I get the last row from each table. But if I
enter the date as 2002-01-01 the query doen't bring back
anything.

What I would like for it to do is to (in the case of my
example) give me the last row from TABLE1 and, if it can't
find anything from TABLE2, I would like it to save N/A or
something in the missing fields.

I have done this with DB2 SQL by unioning two tables
together: the first query in the union selects the fields
if it meets with ask for and the second query would return
a literal if it dosn't find anything. Thay way the query
always returns something.

I hope that helps. And thanks for taking the time to
reply.

-----Original Message-----
Tedd,

It is not clear (to me, anyway) what you are trying to do. What is the
connection, in your example, between xyz and mno? Where are you
applying your date criteria... to both the date fields? It seems to me
that you would get the results you are after if your criteria was...
WHERE Table1.[Date] <= #1-01-2001# Or Table2.[Date] <= #1- 01-2001#
Alternatively, it may be appropriate to use a Union Query. But anyway,
at this stage I am only guessing... can you give some more details?

--
Steve Schapel, Microsoft Access MVP

I have two tables that I am running a query on. Both
tables contain columns with values and a date for each
value.
For example:
TABLE1 TABLE2
NAME DATE NAME DATE
abc 2004-01-02 efg 2004-03-02
xyz 1999-12-05 mno 2003-11-09

In my query I selecting rows that are <= to a date that I
enter. So if I select rows that are less then or equal to
2003-12-01 I get the last two rows in the table.

THE PROBLEM is that if I try to select a date that is not
in the date range I give I don't get any results. So if I
try selecting everything that is <= to 2001-01-01 I don't
get any results because the minimum date on TABLE2 is 2003-
11-09. I know that only one date meets the criteria, not
both, and that's why I don't get anything. So...

MY QUESTION IS: how do I get Access to put a value or
something(like N/A) in the fields where the query couldn't
return any results? So if I am trying to find everything
that is <= to 2001-01-01 from TABLE1 and TABLE2 I want to
get:
xyz 1999-12-05 N/A N/A.

I hope this makes since. Any help would be appreciated.

Thanks

.