Trouble with Select Query

[Guest]

Hello,

Using WinXP and Access 2K2

I have two tables: the first is a table of Employees, the second is a table
of Jobs they perform. Both tables have identical keys, Employees-the primary
key, Jobs-the foreign key. In the jobs table, the jobs performed are
identified by 'check boxes'. (True or False).

In a Select Query, I have [strLName], [strFName] from the Employees table;
[ysnEquip] from the Jobs table. Criteria for the [Jobs].[ysnEquip] = TRUE.
This is a One-to-Many relationship. (Employees to Jobs)

When I run the Query, I get nothing.

If I run a Select Query based on Employees and Address tables, joined using
the same keys as above, where the tables are different, everything works
fine. Employee's Names and Addresses result. This is also a One-To-Many
relationship, as an employee may have more than one Address.

I'm sure I'm missing some detail that is not letting this Query work
properly, but for the life of me, I haven't been able to determine the
solution.

Aside from the fact that Employees are labeled as Entity, here is the SQL:

SELECT tblEntity.strLName, tblEntity.strFName, tblVolJobs.ysnEquipDel
FROM tblEntity INNER JOIN tblVolJobs ON tblEntity.intEntityID =
tblVolJobs.intEntityID
WHERE (((tblVolJobs.ysnEquipDel)=True));

Thank you for taking the time.

 

[Guest]

When a query doesn't work, simplify until it works; fix the problem; and
build it back up.

SELECT tblEntity.strLName, tblEntity.strFName, tblVolJobs.ysnEquipDel
FROM tblEntity INNER JOIN tblVolJobs ON tblEntity.intEntityID =
tblVolJobs.intEntityID ;

Does this run? If so there is something going on in your Where clause. Try
this:

SELECT tblVolJobs.ysnEquipDel
FROM tblVolJobs
WHERE (((tblVolJobs.ysnEquipDel)=True));

Any records? If not this is the problem.

If the first one does run, maybe it's a join problem.

SELECT tblEntity.strLName, tblEntity.strFName, tblVolJobs.ysnEquipDel
FROM tblEntity Left JOIN tblVolJobs ON tblEntity.intEntityID =
tblVolJobs.intEntityID ;

If this runs it means that there are no matching foreign keys in the
tblVolJobs.

 

[Guest]

-Sometimes the simplest suggestions are the best. It makes one look at areas
not thought to be a problem. I was stuck thinking in the 'box'. The problem
was - there was no data to discover in the Jobs Table. Duh.

Thanks Jerry and to all those that have helped me in the past.
--
Jim Ory

When a query doesn't work, simplify until it works; fix the problem; and
build it back up.

SELECT tblEntity.strLName, tblEntity.strFName, tblVolJobs.ysnEquipDel
FROM tblEntity INNER JOIN tblVolJobs ON tblEntity.intEntityID =
tblVolJobs.intEntityID ;

Does this run? If so there is something going on in your Where clause. Try
this:

SELECT tblVolJobs.ysnEquipDel
FROM tblVolJobs
WHERE (((tblVolJobs.ysnEquipDel)=True));

Any records? If not this is the problem.

If the first one does run, maybe it's a join problem.

SELECT tblEntity.strLName, tblEntity.strFName, tblVolJobs.ysnEquipDel
FROM tblEntity Left JOIN tblVolJobs ON tblEntity.intEntityID =
tblVolJobs.intEntityID ;

If this runs it means that there are no matching foreign keys in the
tblVolJobs.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello,

Using WinXP and Access 2K2

I have two tables: the first is a table of Employees, the second is a table
of Jobs they perform. Both tables have identical keys, Employees-the primary
key, Jobs-the foreign key. In the jobs table, the jobs performed are
identified by 'check boxes'. (True or False).

In a Select Query, I have [strLName], [strFName] from the Employees table;
[ysnEquip] from the Jobs table. Criteria for the [Jobs].[ysnEquip] = TRUE.
This is a One-to-Many relationship. (Employees to Jobs)

When I run the Query, I get nothing.

If I run a Select Query based on Employees and Address tables, joined using
the same keys as above, where the tables are different, everything works
fine. Employee's Names and Addresses result. This is also a One-To-Many
relationship, as an employee may have more than one Address.

I'm sure I'm missing some detail that is not letting this Query work
properly, but for the life of me, I haven't been able to determine the
solution.

Aside from the fact that Employees are labeled as Entity, here is the SQL:

SELECT tblEntity.strLName, tblEntity.strFName, tblVolJobs.ysnEquipDel
FROM tblEntity INNER JOIN tblVolJobs ON tblEntity.intEntityID =
tblVolJobs.intEntityID
WHERE (((tblVolJobs.ysnEquipDel)=True));

Thank you for taking the time.