Issue in Query

[hafizfarooq]

Dears,

I m using the following Query which gives the required data
SELECT Sale_Main.SaleDate, Sum(Sale_Detail.Qty) AS SumOfQty
FROM Sale_Main INNER JOIN Sale_Detail ON Sale_Main.SaleId = Sale_Detail.SaleId
GROUP BY Sale_Main.SaleDate, Sale_Detail.Qty

But when i use it with SELECT Sale_Main.SaleDate, Sum(Sale_Detail.Qty) AS SumOfQty FROM Sale_Main INNER JOIN Sale_Detail ON Sale_Main.SaleId = Sale_Detail.SaleId GROUP BY Sale_Main.SaleDate, Sale_Detail.Qty
HAVING (((Sale_Main.SaleDate)<[?])) it gives nothing... is something wronh in this Query ?? Please help

[Optional]

What is the parameter value you are passing for [?] ?

[hafizfarooq]

Its a date i want to get data before 02/01/2010 or any date

[Optional]

Does it not work as part of a join, like this ?:

SELECT
    Sale_Main.SaleDate,
    Sum(Sale_Detail.Qty) As SumOfQty
From
    Sale_Main INNER JOIN Sale_Detail ON
        Sale_Main.SaleId = Sale_Detail.SaleId AND
        Sale_Main.SaleDate < [?]
GROUP BY Sale_Main.SaleDate, Sale_Detail.Qty

The HAVING statement filters the returned data from the GROUP BY statement.
If the join works you don't need to get the data in the first place.

[gibra]

Dears,

But when i use it with SELECT Sale_Main.SaleDate, Sum(Sale_Detail.Qty) AS SumOfQty FROM Sale_Main INNER JOIN Sale_Detail ON Sale_Main.SaleId = Sale_Detail.SaleId GROUP BY Sale_Main.SaleDate, Sale_Detail.Qty
HAVING (((Sale_Main.SaleDate)<[?])) it gives nothing... is something wronh in this Query ?? Please help

1. Database type ? SQL Server, Oracle, Access, ...

2. show exactly the query string before to execute it, i.e.

dim sSQL As String
sSQL = "SELECT Sale_Main.SaleDate, Sum(Sale_Detail.Qty) AS SumOfQty "
sSQL = sSQL & " FROM Sale_Main INNER JOIN Sale_Detail "
sSQL = sSQL & " ON Sale_Main.SaleId = Sale_Detail.SaleId "
sSQL = sSQL & " GROUP BY Sale_Main.SaleDate, Sale_Detail.Qty"
sSQL = sSQL & " HAVING (((Sale_Main.SaleDate) < " & <???>
Debug.Print sSQL

Show the result in Immediate window.

Note tha the formatted string <???> is depend by 'database type' you use.



P.S.
Code must be formatted using Code button, not Bold style.

[hafizfarooq]

Dear Optional,
Its giving Error "Joints expression not supported"

[hafizfarooq]

Dear Gibra, still not showing anything

[Optional]

Dear Optional,
Its giving Error "Joints expression not supported"

I though adding it to the join and removing it from having would work

The only other thought I have is a maybe to try a sub-select:
(If you are in MS-Access, it can be limited in SQL features)

SELECT SaleDate, SumOfQty FROM
    (SELECT
        Sale_Main.SaleDate,
        Sum(Sale_Detail.Qty) As SumOfQty
    From
        Sale_Main INNER JOIN Sale_Detail ON
            Sale_Main.SaleId = Sale_Detail.SaleId
    GROUP BY Sale_Main.SaleDate, Sale_Detail.Qty)
Where
    SaleDate < [?]

I'm not 100&#37; on the SQL syntax as I'm doing this without having access to Access or SQL 2008 at the moment.

[Optional]

OK, I was able to throw together a query similar to yours on my tables in access and I think your issue is not the syntax but the values you are comparing in the HAVING clause.

Your Sale_Main.SaleDate column, I'm assuming is of date/time type thus you must be comparing it to a valid date value.

You need to convert your parameter into a date value. That's propably why it doesn't find a match and returns empty data.

In your query you need to use something like this:

HAVING (((Sale_Main.SaleDate)<DateValue([?])))

check this link for some examples:
How to store, calculate, and compare Date/Time data in Microsoft Access

Hope this helped this time.

[gibra]

Dear Gibra, still not showing anything

What?
You haven't understand.
Do nothing...

[si_the_geek]

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)