Need help With Inner Join Select Statement

[Justin]

I'm accessing an MSAccess DB from within VB.NET. To start here is my OLD
select statement which works 100% and has provided the data I need for many
years:

SELECT TimeSheets.Employee, TimeSheets.[Hol Hours], TimeSheets.[Job Date],
TimeSheets.[Job Number], TimeSheets.[Over Hours], TimeSheets.[Period Start],
Employee.Employee AS Expr1, Employee.Type, TimeSheets.Dept, TimeSheets.GL
FROM (Employee INNER JOIN TimeSheets ON Employee.Employee =
TimeSheets.Employee) WHERE [Period Start] = @PeriodStart AND [Over Hours] >
0

You will notice I pull from TimeSheets and Employee. What I'm trying to do
now is add a third table "Jobs" to pull one more field into my report. Here
is my attempt:

SELECT TimeSheets.Employee, TimeSheets.[Hol Hours], TimeSheets.[Job Date],
TimeSheets.[Job Number], TimeSheets.[Over Hours], TimeSheets.[Period Start],
Employee.Employee AS Expr1, Employee.Type, TimeSheets.Dept, TimeSheets.GL,
Jobs.[Job Number], Jobs.[Job Name] FROM (Employee INNER JOIN TimeSheets ON
Employee.Employee = TimeSheets.Employee) INNER JOIN Jobs ON TimeSheets.[Job
Number] = Jobs.[Job Number] WHERE [Period Start] = @PeriodStart AND [Over
Hours] > 0

This results in an empty dataset. Nothing is selected form the DB. If I
remove the WHERE statement I get a very odd and very small dataset of
mismatched data. When I use the original select statement I get over 3,000
results. I'm expecting the exact same amount of result with the new select
statement only with a new field added (the name of the job).

I would appreciate any help on this matter and thanks in advance!!!

 

[Justin]

Doh! Slight change. I just noticed that the table Jobs was empty. Thus
it had nothing to select. Someone dropped the ball I guess.

I just added as much of the job numbers as I needed for my current report
and I am now filling my dataset. I haven't tried to get that new field yet
but at least I'm back up to speed.

I had to make one change. I had to remove Jobs.[Job Number]. I guess since
I was already pulling that field from TimeSheets?? It wasn't pulling that
field at all until I removed the duplicate. However my dataset, from what I
can tell, doesn't unclude the new field. The temp DataGrid I'm using should
populate it right?

I'm going to mess with this for a little bit and see what happens.

 

[Justin]

Ok, so now I'm back to getting my data however that second INNER JOIN isn't
working. I'm not getting any data from my third table JOBS.

SELECT TimeSheets.Employee, TimeSheets.[Hol Hours], TimeSheets.[Job Date],
TimeSheets.[Job Number], TimeSheets.[Over Hours], TimeSheets.[Period Start],
Employee.Employee AS Expr1, Employee.Type, TimeSheets.Dept, TimeSheets.GL,
Jobs.[Job Name] FROM (Employee INNER JOIN TimeSheets ON Employee.Employee =
TimeSheets.Employee) INNER JOIN Jobs ON TimeSheets.[Job Number] = Jobs.[Job
Number] WHERE [Period Start] = @PeriodStart AND [Over Hours] > 0

So ultimately, I'm not getting Jobs.[Job Name]

Again I would greatly appreciate any help on this mater.

 

[Justin]

When I use that SQL Statement in Access I get all the data I need. So I'm
not sure why VB.NET refused to see that new field. If I try to select
columns on the datagrid it wont bring up the new field at all.