Need help getting specific records with SQL

[Guest]

I sure hope I can explain this clearly!

I have two tables: Table 1 (tblJobs) and Table 2 (tblJobTickets). For each
job in tblJobs there could be one or more listings in the tblJobTickets. What
happens here is that when we get a new job it gets listed in the tblJobs.
Once the job goes into production we issue it one or more job tickets and
they get listed in the tblJobTickets. What I want to do is to pull all jobs
from tblJobs that fit certain criteria (all jobs that aren't completed or all
jobs entered in the last week, etc.) and display some of the info about that
job from tblJobs and that job's ticket number from tblJobTickets if it has
been issued one. Every job gets a unique ID and that is the field that links
the two tables together in the Relationship window. I have tried using
different Joins in my SQL statements and different relationship types but I
can't seem to get the records I want. Here is an example of one of my SQL
statements:

SELECT tblJobTickets.JobNumber, tblJobs.intJobID, " & _
"tblJobs.txtJobName, tblJobs.dtEntered, tblJobs.dtModified, " & _
"tblJobs.Status, tblJobs.txtUsername, tblJobs.intStatusSort " & _
"FROM tblJobTickets RIGHT JOIN tblJobs ON tblJobTickets.intJobID
= tblJobs.intJobID " & _
"WHERE (((tblJobs.Status) <> 'Completed'))

Here is an example of what I get with this sql statement: In tblJobs I have
a job with ID 630. This particular job has seven job ticket listings in
tblJobTickets. When I run this SQL statement I get all seven listings and not
just one. I wish I knew how to limit the records returned to those with
unique Job ID's because that seems like what I want. Even though Job number
630 has seven tickets if I could limit the SQL results to those with Unique
Job ID's I would be getting only one listing for Job # 630.

Thanks in advance to any and all who take the time to share their advice. It
is greatly appreciated!

 

[Dorothy]

I sure hope I can explain this clearly!

I have two tables: Table 1 (tblJobs) and Table 2 (tblJobTickets). For each
job in tblJobs there could be one or more listings in the tblJobTickets. What
happens here is that when we get a new job it gets listed in the tblJobs.
Once the job goes into production we issue it one or more job tickets and
they get listed in the tblJobTickets. What I want to do is to pull all jobs
from tblJobs that fit certain criteria (all jobs that aren't completed or all
jobs entered in the last week, etc.) and display some of the info about that
job from tblJobs and that job's ticket number from tblJobTickets if it has
been issued one. Every job gets a unique ID and that is the field that links
the two tables together in the Relationship window. I have tried using
different Joins in my SQL statements and different relationship types but I
can't seem to get the records I want. Here is an example of one of my SQL
statements:

SELECT tblJobTickets.JobNumber, tblJobs.intJobID, " & _
"tblJobs.txtJobName, tblJobs.dtEntered, tblJobs.dtModified, " & _
"tblJobs.Status, tblJobs.txtUsername, tblJobs.intStatusSort " & _
"FROM tblJobTickets RIGHT JOIN tblJobs ON tblJobTickets.intJobID
= tblJobs.intJobID " & _
"WHERE (((tblJobs.Status) <> 'Completed'))

Here is an example of what I get with this sql statement: In tblJobs I have
a job with ID 630. This particular job has seven job ticket listings in
tblJobTickets. When I run this SQL statement I get all seven listings and not
just one. I wish I knew how to limit the records returned to those with
unique Job ID's because that seems like what I want. Even though Job number
630 has seven tickets if I could limit the SQL results to those with Unique
Job ID's I would be getting only one listing for Job # 630.

Thanks in advance to any and all who take the time to share their advice. It
is greatly appreciated!

Hello. Have you tried adding a GROUP BY clause to your statement?
You could throw in

GROUP BY tblJobs.intJobID

after the WHERE clause.

Dorothy

 

[John Spencer]

You could try
SELECT DISTINCT TblJobTickets.JobNumber, ...

That should give you rows that combine into one if all the fields in the
Select clause have the same value. Which should mean that if JobNumber is
the same for all the associated job tickets for a specific job you will get
one record. If there are no associated job tickets, you will get one
record.

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

 

[Guest]

Thanks for the idea Dorothy but it didn't work. I had already tried making a
query and clicking on the Summations button and doing a Group By that way but
Access didn't like the resulting SQL. This time I did as you suggested and
added a "GROUP BY" clause in my written SQL statement (after the WHERE
clause) and I got an error. The same error I got when I tried it via the
summations button in a query. The error is:

You tried to execute a query that does not include the specified expression
'JobNumber' as part of an aggregate function.

Here is the actual SQL statement:

SELECT tblJobTickets.JobNumber, tblJobs.intJobID, " & _
"tblJobs.txtJobName, tblJobs.dtEntered, tblJobs.dtModified, " & _
"tblJobs.Status, tblJobs.txtUsername, tblJobs.intStatusSort " & _
"FROM tblJobTickets RIGHT JOIN tblJobs ON tblJobTickets.intJobID
= tblJobs.intJobID " & _
"WHERE (((tblJobs.Status) <> 'Completed')) GROUP BY
tblJobs.intJobID

I like the idea because it is certainly what I was thinking the solution
might be but I'm afraid it didn't work. At least the way I implemented it!

Thanks for the try though.

 

[Guest]

Thanks John. Doing a SELECT DISTINCT did indeed fix my issue. I swear I had
tried that but I guess not. Actually, I think I was toying around with the
SQL of a Query and I probably had fields in there that I shouldn't have.
Either way, problem solved and thanks again.