No Data Appearing

C

Carly Access

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;
 
J

John Spencer

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?

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

Carly Access

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????





--
Carly(access fan)


John Spencer said:
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?

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

Carly Access said:
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;
Click to expand...
Click to expand...
 
J

John Spencer

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)

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

Carly Access said:
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????





--
Carly(access fan)


John Spencer said:
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?

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

Carly Access said:
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;
Click to expand...
Click to expand...
Click to expand...
 
Top