SQL SubQuery

D

DS

I have an SQ Statement. It's a Query based on a SubQuery. I need to
make it all SQL by incorperating the Query into the statement. Any help
apprecited.
Thanks
DS

This is the SQL Statement

SELECT Query12.LogJobID, tblJobNames.JobName
FROM Query12 INNER JOIN tblJobNames ON
Query12.LogJobID=tblJobNames.JobNameID
GROUP BY Query12.LogJobID, tblJobNames.JobName
ORDER BY tblJobNames.JobName;

This Query12
SELECT tblTimeLog.LogDateIn, tblTimeLog.LogDateOut, tblTimeLog.LogJobID
FROM tblTimeLog
GROUP BY tblTimeLog.LogDateIn, tblTimeLog.LogDateOut, tblTimeLog.LogJobID
HAVING (((tblTimeLog.LogDateIn)>=[Forms]![frmTimeCardFilter]![TxtDate2])
AND ((tblTimeLog.LogDateOut)<=[Forms]![frmTimeCardFilter]![TxtDate3]))
OR (((tblTimeLog.LogDateIn)>=[Forms]![frmTimeCardFilter]![TxtDate2]) AND
((tblTimeLog.LogDateOut) Is Null));
 
R

Ron Weiner

This ought to do it

SELECT Query12.LogJobID, tblJobNames.JobName
FROM (
SELECT tblTimeLog.LogDateIn, tblTimeLog.LogDateOut, tblTimeLog.LogJobID
FROM tblTimeLog
GROUP BY tblTimeLog.LogDateIn, tblTimeLog.LogDateOut, tblTimeLog.LogJobID
HAVING (((tblTimeLog.LogDateIn)>=[Forms]![frmTimeCardFilter]![TxtDate2])
AND ((tblTimeLog.LogDateOut)<=[Forms]![frmTimeCardFilter]![TxtDate3]))
OR (((tblTimeLog.LogDateIn)>=[Forms]![frmTimeCardFilter]![TxtDate2]) AND
((tblTimeLog.LogDateOut) Is Null))
) As Query12 INNER JOIN tblJobNames ON
Query12.LogJobID=tblJobNames.JobNameID
GROUP BY Query12.LogJobID, tblJobNames.JobName
ORDER BY tblJobNames.JobName;
 
M

Marshall Barton

DS said:
I have an SQ Statement. It's a Query based on a SubQuery. I need to
make it all SQL by incorperating the Query into the statement. Any help
apprecited.
Thanks
DS

This is the SQL Statement

SELECT Query12.LogJobID, tblJobNames.JobName
FROM Query12 INNER JOIN tblJobNames ON
Query12.LogJobID=tblJobNames.JobNameID
GROUP BY Query12.LogJobID, tblJobNames.JobName
ORDER BY tblJobNames.JobName;

This Query12
SELECT tblTimeLog.LogDateIn, tblTimeLog.LogDateOut, tblTimeLog.LogJobID
FROM tblTimeLog
GROUP BY tblTimeLog.LogDateIn, tblTimeLog.LogDateOut, tblTimeLog.LogJobID
HAVING (((tblTimeLog.LogDateIn)>=[Forms]![frmTimeCardFilter]![TxtDate2])
AND ((tblTimeLog.LogDateOut)<=[Forms]![frmTimeCardFilter]![TxtDate3]))
OR (((tblTimeLog.LogDateIn)>=[Forms]![frmTimeCardFilter]![TxtDate2]) AND
((tblTimeLog.LogDateOut) Is Null));
Click to expand...


I think you may be way over complicating this operation.

Why are you using GROUP BY clauses in these queries? Since
you are not using any aggregate functions, the only effect
is to eliminate duplicate records, which should not be there
in most properly designed tables.

Q12 should not use the HAVING clause. A WHERE clause should
be used to filter the query's data.

After making those simplifications, it appears that you do
not need Q12 at all. The INNER JOIN can join tblJobNames
directly to tblTimeLog.

If you were using the GROUP BY clauses to suppress duplicate
records, use the DISTINCT predicate instead.

Think about all that and try to work up a new query. Post
back with specific questions if you have trouble with some
aspect.
 
D

DS

Perhaps I wasn't clear....
This works.

SELECT Query12.LogJobID, tblJobNames.JobName
FROM Query12 INNER JOIN tblJobNames ON
Query12.LogJobID=tblJobNames.JobNameID
GROUP BY Query12.LogJobID, tblJobNames.JobName
ORDER BY tblJobNames.JobName;

I just want to replace Query12 with the code that is actually Query12.

Thanks
DS
 
D

DS

Marshall said:
DS wrote:

I have an SQ Statement. It's a Query based on a SubQuery. I need to
make it all SQL by incorperating the Query into the statement. Any help
apprecited.
Thanks
DS

This is the SQL Statement

SELECT Query12.LogJobID, tblJobNames.JobName
Click to expand...
FROM Query12 INNER JOIN tblJobNames ON
Click to expand...
Query12.LogJobID=tblJobNames.JobNameID
GROUP BY Query12.LogJobID, tblJobNames.JobName
ORDER BY tblJobNames.JobName;

This Query12
SELECT tblTimeLog.LogDateIn, tblTimeLog.LogDateOut, tblTimeLog.LogJobID
Click to expand...
FROM tblTimeLog
Click to expand...
GROUP BY tblTimeLog.LogDateIn, tblTimeLog.LogDateOut, tblTimeLog.LogJobID
HAVING (((tblTimeLog.LogDateIn)>=[Forms]![frmTimeCardFilter]![TxtDate2])
AND ((tblTimeLog.LogDateOut)<=[Forms]![frmTimeCardFilter]![TxtDate3]))
OR (((tblTimeLog.LogDateIn)>=[Forms]![frmTimeCardFilter]![TxtDate2]) AND
((tblTimeLog.LogDateOut) Is Null));
Click to expand...



I think you may be way over complicating this operation.

Why are you using GROUP BY clauses in these queries? Since
you are not using any aggregate functions, the only effect
is to eliminate duplicate records, which should not be there
in most properly designed tables.

Q12 should not use the HAVING clause. A WHERE clause should
be used to filter the query's data.

After making those simplifications, it appears that you do
not need Q12 at all. The INNER JOIN can join tblJobNames
directly to tblTimeLog.

If you were using the GROUP BY clauses to suppress duplicate
records, use the DISTINCT predicate instead.

Think about all that and try to work up a new query. Post
back with specific questions if you have trouble with some
aspect.
Click to expand...
The reson for the SubQuery is that when I ran it with the SubQuery the
JobNames would repeat if there was a different date record for it. I'm
basically grabbing the JobNames between Date2 and Date3. And I only
need one instance of each JobName. I just need to get rid of Query12
and make an SQL statement out of it with a Sub-Query.
Thanks
DS
 
R

Ron Weiner

Perhaps I am misunderstanding what it is that you want to do. Did you try
the Sql in my previous post? It wraps up your Sub Query into the Sql
statement that you said works. Try pasting the sql I posted into a new
query. If it works OK you can rename or Delete Query12 from you database
and it will still work.
 
M

Marshall Barton

DS said:
Marshall said:
DS wrote:

I have an SQ Statement. It's a Query based on a SubQuery. I need to
make it all SQL by incorperating the Query into the statement. Any help
apprecited.
Thanks
DS

This is the SQL Statement

SELECT Query12.LogJobID, tblJobNames.JobName
Click to expand...
FROM Query12 INNER JOIN tblJobNames ON
Click to expand...
Query12.LogJobID=tblJobNames.JobNameID
GROUP BY Query12.LogJobID, tblJobNames.JobName
ORDER BY tblJobNames.JobName;

This Query12
SELECT tblTimeLog.LogDateIn, tblTimeLog.LogDateOut, tblTimeLog.LogJobID
Click to expand...
FROM tblTimeLog
Click to expand...
GROUP BY tblTimeLog.LogDateIn, tblTimeLog.LogDateOut, tblTimeLog.LogJobID
HAVING (((tblTimeLog.LogDateIn)>=[Forms]![frmTimeCardFilter]![TxtDate2])
AND ((tblTimeLog.LogDateOut)<=[Forms]![frmTimeCardFilter]![TxtDate3]))
OR (((tblTimeLog.LogDateIn)>=[Forms]![frmTimeCardFilter]![TxtDate2]) AND
((tblTimeLog.LogDateOut) Is Null));
Click to expand...



I think you may be way over complicating this operation.

Why are you using GROUP BY clauses in these queries? Since
you are not using any aggregate functions, the only effect
is to eliminate duplicate records, which should not be there
in most properly designed tables.

Q12 should not use the HAVING clause. A WHERE clause should
be used to filter the query's data.

After making those simplifications, it appears that you do
not need Q12 at all. The INNER JOIN can join tblJobNames
directly to tblTimeLog.

If you were using the GROUP BY clauses to suppress duplicate
records, use the DISTINCT predicate instead.

Think about all that and try to work up a new query. Post
back with specific questions if you have trouble with some
aspect.
Click to expand...
The reson for the SubQuery is that when I ran it with the SubQuery the
JobNames would repeat if there was a different date record for it. I'm
basically grabbing the JobNames between Date2 and Date3. And I only
need one instance of each JobName. I just need to get rid of Query12
and make an SQL statement out of it with a Sub-Query.
Click to expand...


I don't think that so. If you didn't follow what I was
trying to say earlier, here's my version of a translation
of your two queries into a single query:

SELECT DISTINCT T.LogJobID, J.JobName
FROM tblTimeLog As T INNER JOIN tblJobNames As J
ON T.LogJobID=J.JobNameID
WHERE T.LogDateIn>=Forms!frmTimeCardFilter!TxtDate2
AND (T.LogDateOut<=Forms!frmTimeCardFilter!TxtDate3
OR T.LogDateOut Is Null)
ORDER BY J.JobName
 
D

DS

Marshall said:
DS wrote:

Marshall Barton wrote:

DS wrote:



I have an SQ Statement. It's a Query based on a SubQuery. I need to
make it all SQL by incorperating the Query into the statement. Any help
apprecited.
Thanks
DS

This is the SQL Statement

SELECT Query12.LogJobID, tblJobNames.JobName

FROM Query12 INNER JOIN tblJobNames ON


Query12.LogJobID=tblJobNames.JobNameID
GROUP BY Query12.LogJobID, tblJobNames.JobName
ORDER BY tblJobNames.JobName;

This Query12
SELECT tblTimeLog.LogDateIn, tblTimeLog.LogDateOut, tblTimeLog.LogJobID

FROM tblTimeLog


GROUP BY tblTimeLog.LogDateIn, tblTimeLog.LogDateOut, tblTimeLog.LogJobID
HAVING (((tblTimeLog.LogDateIn)>=[Forms]![frmTimeCardFilter]![TxtDate2])
AND ((tblTimeLog.LogDateOut)<=[Forms]![frmTimeCardFilter]![TxtDate3]))
OR (((tblTimeLog.LogDateIn)>=[Forms]![frmTimeCardFilter]![TxtDate2]) AND
((tblTimeLog.LogDateOut) Is Null));



I think you may be way over complicating this operation.

Why are you using GROUP BY clauses in these queries? Since
you are not using any aggregate functions, the only effect
is to eliminate duplicate records, which should not be there
in most properly designed tables.

Q12 should not use the HAVING clause. A WHERE clause should
be used to filter the query's data.

After making those simplifications, it appears that you do
not need Q12 at all. The INNER JOIN can join tblJobNames
directly to tblTimeLog.

If you were using the GROUP BY clauses to suppress duplicate
records, use the DISTINCT predicate instead.

Think about all that and try to work up a new query. Post
back with specific questions if you have trouble with some
aspect.
Click to expand...

The reson for the SubQuery is that when I ran it with the SubQuery the
JobNames would repeat if there was a different date record for it. I'm
basically grabbing the JobNames between Date2 and Date3. And I only
need one instance of each JobName. I just need to get rid of Query12
and make an SQL statement out of it with a Sub-Query.
Click to expand...



I don't think that so. If you didn't follow what I was
trying to say earlier, here's my version of a translation
of your two queries into a single query:

SELECT DISTINCT T.LogJobID, J.JobName
FROM tblTimeLog As T INNER JOIN tblJobNames As J
ON T.LogJobID=J.JobNameID
WHERE T.LogDateIn>=Forms!frmTimeCardFilter!TxtDate2
AND (T.LogDateOut<=Forms!frmTimeCardFilter!TxtDate3
OR T.LogDateOut Is Null)
ORDER BY J.JobName
Click to expand...
This works exactly the way I want it to!
Thanks
DS
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SubQuery Problem 7
Textbox controlsource = SQL statement ? 2
I'm not sure how to return the value using the SQL statement? 4
SQL Woes 4
Combining 2 Queries 2
Variable subquery criteria SQL problems 1
Subquery Help 4
Need a code for a command button to sort records 5

Top