Sub-Query Problem

[DS]

I'm having a problem coding this subquery...
Not quite sure where to place the EXISTS.
Any help appreciated.
Thanks
DS

SELECT tblCheckDetails.CDBizDay
FROM tblCheckDetails
GROUP BY tblCheckDetails.CDBizDay
HAVING tblCheckDetails.CDBizDay>=[Forms]![frmReportDates]![TxtStart] And
tblCheckDetails.CDBizDay<=[Forms]![frmReportDates]![TxtEnd]
EXISTS
IN
(SELECT tblCheckDetails.CDQuantity, tblCheckDetails.CDPrice,
tblCheckDetails.CDDiscountDP, tblCheckDetails.CDDiscountAmount,
tblCheckDetails.CDDiscountPercent, tblCheckDetails.CDDiscountWhere,
tblCheckDetails.CDInclusive, tblCheckDetails.CDKillTax,
RepTax([CDKillTax],[CDInclusive],[CDDiscountWhere],[CDDiscountDP],[CDQuantity],[CDPrice],[CDTaxRate],[CDDiscountAmount],[CDDiscountPercent])
AS TX
FROM tblCheckDetails);

 

[Bob Barrows [MVP]]

I'm having a problem coding this subquery...
Not quite sure where to place the EXISTS.
Any help appreciated.
Thanks
DS

SELECT tblCheckDetails.CDBizDay
FROM tblCheckDetails
GROUP BY tblCheckDetails.CDBizDay
HAVING tblCheckDetails.CDBizDay>=[Forms]![frmReportDates]![TxtStart]
And tblCheckDetails.CDBizDay<=[Forms]![frmReportDates]![TxtEnd]

This, in addition to being very inefficient, really does not make sense ...
are you trying to get a unique list of CDBizDays?

Your criteria should be in a WHERE clause instead of a HAVING clause since
none of the criteria depend on aggregated values. I would probably choose to
do it like this (forgive my use of aliases - I find queries more readable
when they are used):

SELECT DISTINCT t.CDBizDay
FROM tblCheckDetails t
WHERE CDBizDay>=[Forms]![frmReportDates]![TxtStart]
And
CDBizDay<=[Forms]![frmReportDates]![TxtEnd]

Now here is where things get really murky for me:

EXISTS
IN

You cannot use both keywords together like this. You need to be using either
using an EXISTS criterion, or an IN criterion. These are two different
things.

When using IN, it has to take the form:

WHERE
mainquery.column-name IN (select single-field from table WHERE ... )

When using EXISTS, it is rarely useful to list columns in the subquery:
"Select *" will do because the query engine is simply looking for at least
one row that satisfies the conditions in the subquery's WHERE clause (which
seems to be missing in your subquery). The typical form is:

WHERE EXISTS
(select * from table t where t.field = mainquery.field)

(SELECT tblCheckDetails.CDQuantity, tblCheckDetails.CDPrice,
tblCheckDetails.CDDiscountDP, tblCheckDetails.CDDiscountAmount,
tblCheckDetails.CDDiscountPercent, tblCheckDetails.CDDiscountWhere,
tblCheckDetails.CDInclusive, tblCheckDetails.CDKillTax,
RepTax([CDKillTax],[CDInclusive],[CDDiscountWhere],[CDDiscountDP],[CDQuantity],[CDPrice],[CDTaxRate],[CDDiscountAmount],[CDDiscountPercent])
AS TX
FROM tblCheckDetails);

This subquery makes no sense - given that tblCheckDetails contains at least
one record, the EXISTS will ALWAYS return true.
Could you provide more details about what you are trying to achieve here?

 

[DS]

Thanks,
I'm trying to get the records that are between the Start Date and End Date
then select the following records from there. I know it sounds a litte off
but I need to do it this way. Thanks
DS

 

[DS]

Hi Bob more info... here is one case where I'm using the sub-query.
Thanks
DS

The QueryDates is...
SELECT tblCheckDetails.CDBizDay
FROM tblCheckDetails
GROUP BY tblCheckDetails.CDBizDay
HAVING (((tblCheckDetails.CDBizDay)>=[Forms]![frmReportDates]![TxtStart] And
(tblCheckDetails.CDBizDay)<=[Forms]![frmReportDates]![TxtEnd]));
And is used here...

SELECT
Sum(Abs(DISTL([CDQuantity],[CDPrice],[CDDiscountAmount],[CDDiscountPercent],[CDDiscountDP],[CDDiscountWhere],[CDTaxRate],[CDInclusive])))
AS DD, " & _
"tblDiscounts.DiscountName, Count(tblDiscountDetails.CDDiscountID) AS
CountOfCDDiscountID " & _
"FROM QueryDates INNER JOIN (tblDiscountDetails INNER JOIN tblDiscounts ON
tblDiscountDetails.CDDiscountID = tblDiscounts.DiscountID) ON
QueryDates.CDBizDay = tblDiscountDetails.CDBizDay " & _
"GROUP BY tblDiscounts.DiscountName;"

 

[Bob Barrows [MVP]]

Hi Bob more info... here is one case where I'm using the sub-query.
Thanks
DS

The QueryDates is...
SELECT tblCheckDetails.CDBizDay
FROM tblCheckDetails
GROUP BY tblCheckDetails.CDBizDay
HAVING
(((tblCheckDetails.CDBizDay)>=[Forms]![frmReportDates]![TxtStart] And
(tblCheckDetails.CDBizDay)<=[Forms]![frmReportDates]![TxtEnd])); And is
used here...

Again, use WHERE instead of HAVING.
DISTINCT vs GROUP BY is a matter of personal preference.
WHERE vs. HAVING is not: HAVING should only be used for criteria that depend
on aggregation (Sum, Count, etc.) being done by the GROUP BY. In this case,
CDBizDay is _not_ being aggregated, so any criteria involving this field
should be done in the WHERE clause, not the HAVING clause. It works if done
in the HAVING clause, but it is much less efficient. Your goal should be to
feed as few records as possible into the grouping engine. The WHERE clause
filters the results _before_ grouping, so it has the effect of reducing the
number of records being grouped.

SELECT
Sum(Abs(DISTL([CDQuantity],[CDPrice],[CDDiscountAmount],[CDDiscountPercent],[CDDiscountDP],[CDDiscountWhere],[CDTaxRate],[CDInclusive])))
AS DD, " & _
"tblDiscounts.DiscountName, Count(tblDiscountDetails.CDDiscountID) AS
CountOfCDDiscountID " & _
"FROM QueryDates INNER JOIN (tblDiscountDetails INNER JOIN
tblDiscounts ON tblDiscountDetails.CDDiscountID = tblDiscounts.DiscountID)
ON
QueryDates.CDBizDay = tblDiscountDetails.CDBizDay " & _
"GROUP BY tblDiscounts.DiscountName;"

OK, this looks valid. What is your question?

 

[Bob Barrows [MVP]]

Thanks,
I'm trying to get the records that are between the Start Date and End
Date then select the following records from there. I know it sounds
a litte off but I need to do it this way. Thanks
DS

Doesn't it already do that? I think you need to show us some sample data and
desired results. We do not have the benefit of being able to run this query
against your data.

 

[DS]

Yes Bob,
It does that. I just want to do away with the stored query and convert this
to SQL.
Thanks
DS

 

[Bob Barrows [MVP]]

Yes Bob,
It does that. I just want to do away with the stored query and
convert this to SQL.
Thanks
DS

Oh! What version of Access? This will work with A2000+:

SELECT
Sum(Abs(DISTL([CDQuantity],[CDPrice],[CDDiscountAmount],[CDDiscountPerce
nt],[CDDiscountDP],[CDDiscountWhere],[CDTaxRate],[CDInclusive])))
AS DD,
tblDiscounts.DiscountName, Count(tblDiscountDetails.CDDiscountID) AS
CountOfCDDiscountID
FROM (
SELECT DISTINCT t.CDBizDay
FROM tblCheckDetails t
WHERE CDBizDay>=[Forms]![frmReportDates]![TxtStart]
And
CDBizDay<=[Forms]![frmReportDates]![TxtEnd]
) as q
INNER JOIN (tblDiscountDetails INNER JOIN tblDiscounts ON
tblDiscountDetails.CDDiscountID = tblDiscounts.DiscountID) ON
q.CDBizDay = tblDiscountDetails.CDBizDay
GROUP BY tblDiscounts.DiscountName

 

[DS]

Thanks Bob,
Just what I was looking for !
DS