No Data Appearing

  • Thread starter Thread starter Carly Access
  • Start date Start date
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;
 
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
..
 
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...
 
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...
 
Back
Top