No Data Appearing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carly Access - 28 Nov 2007 12:08 GMT

No data appearing with the following:
Linking to tables with a left join. Data appearing from first table but no
matching from second. HELP!

SELECT [IRB Separations (April 1, 2006 to March 31, 2007)].EMPLID, [IRB
Workforce March 31, 2007].AGE
FROM [IRB Separations (April 1, 2006 to March 31, 2007)] LEFT JOIN [IRB
Workforce March 31, 2007] ON [IRB Separations (April 1, 2006 to March 31,
2007)].EMPLID=[IRB Workforce March 31, 2007].EMPLID;

Signature

Carly(access fan)

Reply to this Message

John Spencer - 28 Nov 2007 12:43 GMT

Is there matching EmplId in the table on the right?
Is there data in the Age field?

Does the following show any data in the Workforce EMPLID field?
By the way, I used aliases for your LONG and complex table names to make it
easier to enter and modify the query.
SELECT S.EMPLID, W.EMPLID, W.AGE
FROM [IRB Separations (April 1, 2006 to March 31, 2007)] as S
LEFT JOIN [IRB Workforce March 31, 2007] As W
ON S.EMPLID=W.EMPLID;

If the above shows no data in W.EMPLID and you are sure there should be, you
might check to see
-- Are you referencing the correct query/table?
-- Are records being returned by IRB WorkForce March 31, 2007?

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> No data appearing with the following:
> Linking to tables with a left join. Data appearing from first table but no
[quoted text clipped - 5 lines]
> Workforce March 31, 2007] ON [IRB Separations (April 1, 2006 to March 31,
> 2007)].EMPLID=[IRB Workforce March 31, 2007].EMPLID;

Reply to this Message

Carly Access - 29 Nov 2007 02:41 GMT

Hi John,

Yes there is a matching EmplID field in the Workforce table and Separation
Table, data criterias both the same;
Yes there is data in the Age column of the Workforce.

When I run the Query the number of rows from the Separation table all appear
with a blank column of age from the workforce table, if I reverse the join
the opposite occurs, in other words there is a match with the EmplId but no
data. The records are being returned but blank????

Signature

Carly(access fan)

> Is there matching EmplId in the table on the right?
> Is there data in the Age field?
[quoted text clipped - 21 lines]
> > Workforce March 31, 2007] ON [IRB Separations (April 1, 2006 to March 31,
> > 2007)].EMPLID=[IRB Workforce March 31, 2007].EMPLID;

Reply to this Message

John Spencer - 29 Nov 2007 14:53 GMT

I guess it is possible that you have a corrupt index. We can force Access
to NOT use the index by changing the join criteria.

SELECT S.EMPLID, W.EMPLID, W.AGE
FROM [IRB Separations (April 1, 2006 to March 31, 2007)] as S
LEFT JOIN [IRB Workforce March 31, 2007] As W
ON S.EMPLID & "" =W.EMPLID & ""

If that returns the expected data, then you will need to delete the indexes
for EmplID, compact the database, redo the indexes.

By the way since you are using a LEFT JOIN the table on the Left side of the
join will return all the records. I still suspect that the problem is that
EMPLID is not matching between the two. What are the sources of the two
tables/queries you are using? Is there any possibility that one or the
other of the two has trailing spaces or a leading space? You can check that
with

SELECT S.EMPLID, W.EMPLID, W.AGE
FROM [IRB Separations (April 1, 2006 to March 31, 2007)] as S
LEFT JOIN [IRB Workforce March 31, 2007] As W
ON Trim(S.EMPLID) = Trim(W.EMPLID)

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> Hi John,
>
[quoted text clipped - 38 lines]
>> > 31,
>> > 2007)].EMPLID=[IRB Workforce March 31, 2007].EMPLID;

Reply to this Message