GROUP BY... AND >=5000

[sal21]

This is my SQL:

Code: Select all

SELECT DESCRIZIONE FROM PREL_2014 WHERE DATA Between #01/03/2014# And #01/31/2014# AND T='L' GROUP BY DESCRIZIONE ORDER BY DESCRIZIONE

i need to add a new GROUP BY on field CODICE where the sum of field AUMONT is >=5000 ...
How to?

[HansV]

Do you want to group on CODICE, then on DESCRIZIONE? Or on DESCRIZIONE, then on CODICE? Or on DESCRIZIONE only?

The condition on AUMONT is

GROUP BY ... HAVING Sum(AUMONT)>=5000 ORDER BY ...

[sal21]

Do you want to group on CODICE, then on DESCRIZIONE? Or on DESCRIZIONE, then on CODICE? Or on DESCRIZIONE only?

The condition on AUMONT is

GROUP BY ... HAVING Sum(AUMONT)>=5000 ORDER BY ...

Really i need to extract all recordsets if summ of CONTANTI/21.7 is >=5000(i have used the param 21.7 based this post:

http://www.eileenslounge.com/viewtopic. ... 7&e=119457

example:

the summ CONTANTI is 45000/21.7=2073,73 no extract records...
but 450000/21.7=20737,32719 extract records

my query;

Code: Select all

SELECT 2014.SPORT_OP, 2014.DESCR_AGENZIA, 2014.NDG, 2014.DESCRIZIONE, Count(2014.DESCRIZIONE) AS CountOfDESCRIZIONE, Sum(2014.CONTANTI) AS SumOfCONTANTI
FROM 2014
WHERE ((2014.T)="L") AND ((2014.DATA) Between [INIZIO] And [FINE]))
GROUP BY 2014.SPORT_OP, 2014.DESCR_AGENZIA, 2014.DG, 2014.DESCRIZIONE;

NOTE:
INIZIO and FINE are the first and last day in month in question

[HansV]

You can use

Code: Select all

SELECT 2014.SPORT_OP, 2014.DESCR_AGENZIA, 2014.NDG, 2014.DESCRIZIONE, Count(2014.DESCRIZIONE) AS CountOfDESCRIZIONE, Sum(2014.CONTANTI) AS SumOfCONTANTI
FROM 2014
WHERE 2014.T="L" AND 2014.DATA Between [INIZIO] And [FINE]
GROUP BY 2014.SPORT_OP, 2014.DESCR_AGENZIA, 2014.DG, 2014.DESCRIZIONE
HAVING Sum(2014.CONTANTI)>=5000*21.7

It's not clear to me where CODICE comes into this.

[sal21]

You can use

Code: Select all

SELECT 2014.SPORT_OP, 2014.DESCR_AGENZIA, 2014.NDG, 2014.DESCRIZIONE, Count(2014.DESCRIZIONE) AS CountOfDESCRIZIONE, Sum(2014.CONTANTI) AS SumOfCONTANTI
FROM 2014
WHERE 2014.T="L" AND 2014.DATA Between [INIZIO] And [FINE]
GROUP BY 2014.SPORT_OP, 2014.DESCR_AGENZIA, 2014.[b]DG[/b], 2014.DESCRIZIONE
HAVING Sum(2014.CONTANTI)>=5000*21.7

It's not clear to me where CODICE comes into this.

Now DG is CODICE

and...
is correct to divide the aumont /21.7 or is possible to consider the steep of init and and days of month in 2014.DATA Between [INIZIO] And [FINE]

[HansV]

If you're working in Access you can create a custom VBA function:

Code: Select all

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Long
    '....................................................................
    ' Name:     WorkingDays
    ' Inputs:   StartDate As Date
    '           EndDate As Date
    ' Returns:  Long
    ' Author:   Arvin Meyer
    ' Date:     February 19, 1997
    ' Comment:  Accepts two dates and returns the number of weekdays between them
    ' Note that this function does not account for holidays.
    '....................................................................
    Dim lngCount As Long
    On Error GoTo Err_WorkingDays

    lngCount = 0
    Do While StartDate <= EndDate
        If Weekday(StartDate, vbMonday) < 6 Then
            lngCount = lngCount + 1
        End If
        StartDate = StartDate + 1
    Loop
    WorkingDays = lngCount
    Exit Function

Err_WorkingDays:
    MsgBox Err.Description
End Function

(Modified from Calculate Number of Working Days)

You can then replace 21.7 with WorkingDays([INIZIO],[FINE]) but it will probably slow down your query.

[sal21]

If you're working in Access you can create a custom VBA function:

Code: Select all

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Long
    '....................................................................
    ' Name:     WorkingDays
    ' Inputs:   StartDate As Date
    '           EndDate As Date
    ' Returns:  Long
    ' Author:   Arvin Meyer
    ' Date:     February 19, 1997
    ' Comment:  Accepts two dates and returns the number of weekdays between them
    ' Note that this function does not account for holidays.
    '....................................................................
    Dim lngCount As Long
    On Error GoTo Err_WorkingDays

    lngCount = 0
    Do While StartDate <= EndDate
        If Weekday(StartDate, vbMonday) < 6 Then
            lngCount = lngCount + 1
        End If
        StartDate = StartDate + 1
    Loop
    WorkingDays = lngCount
    Exit Function

Err_WorkingDays:
    MsgBox Err.Description
End Function

(Modified from Calculate Number of Working Days)

You can then replace 21.7 with WorkingDays([INIZIO],[FINE]) but it will probably slow down your query.

No, i'm working in Excel VBA and DAO.

Peraph i can calculate the nrs of day via vba code and pass in a param of this code:?

Code: Select all

...
Set RSA = Nothing
    Set QRY = DB.QueryDefs("SINTETICO")
    QRY.Parameters("[INIZIO]").Value = CDate(DATA1)
    QRY.Parameters("[FINE]").Value = CDate(DATA2)
    Set RSA = QRY.OpenRecordset()
...

[HansV]

Try

Code: Select all

SELECT 2014.SPORT_OP, 2014.DESCR_AGENZIA, 2014.NDG, 2014.DESCRIZIONE, Count(2014.DESCRIZIONE) AS CountOfDESCRIZIONE, Sum(2014.CONTANTI) AS SumOfCONTANTI
FROM 2014
WHERE 2014.T="L" AND 2014.DATA Between [INIZIO] And [FINE]
GROUP BY 2014.SPORT_OP, 2014.DESCR_AGENZIA, 2014.DG, 2014.DESCRIZIONE
HAVING Sum(2014.CONTANTI)>=5000*[CONTAGIORNI]

and add the following to your code:

Code: Select all

    QRY.Parameters("CONTAGIORNI").Value = WorkingDays(DATA1, DATA2)

[sal21]

Try

Code: Select all

SELECT 2014.SPORT_OP, 2014.DESCR_AGENZIA, 2014.NDG, 2014.DESCRIZIONE, Count(2014.DESCRIZIONE) AS CountOfDESCRIZIONE, Sum(2014.CONTANTI) AS SumOfCONTANTI
FROM 2014
WHERE 2014.T="L" AND 2014.DATA Between [INIZIO] And [FINE]
GROUP BY 2014.SPORT_OP, 2014.DESCR_AGENZIA, 2014.DG, 2014.DESCRIZIONE
HAVING Sum(2014.CONTANTI)>=5000*[CONTAGIORNI]

and add the following to your code:

Code: Select all

    QRY.Parameters("CONTAGIORNI").Value = WorkingDays(DATA1, DATA2)

ops...

note:
the two variables DATA1, DATA2 are date dimensioned

[HansV]

What happens if you change the first line of the function to

Code: Select all

Public Function WorkingDays(ByVal StartDate As Date, ByVal EndDate As Date) As Long

[sal21]

What happens if you change the first line of the function to

Code: Select all

Public Function WorkingDays(ByVal StartDate As Date, ByVal EndDate As Date) As Long

naturally work!

[sal21]

Try

Code: Select all

SELECT 2014.SPORT_OP, 2014.DESCR_AGENZIA, 2014.NDG, 2014.DESCRIZIONE, Count(2014.DESCRIZIONE) AS CountOfDESCRIZIONE, Sum(2014.CONTANTI) AS SumOfCONTANTI
FROM 2014
WHERE 2014.T="L" AND 2014.DATA Between [INIZIO] And [FINE]
GROUP BY 2014.SPORT_OP, 2014.DESCR_AGENZIA, 2014.DG, 2014.DESCRIZIONE
HAVING Sum(2014.CONTANTI)>=5000*[CONTAGIORNI]

and add the following to your code:

Code: Select all

    QRY.Parameters("CONTAGIORNI").Value = WorkingDays(DATA1, DATA2)

Hans, please see my SQL, have a sintiax error!!!!

Code: Select all

SQL = "SELECT DESCRIZIONE" & _
          " FROM " & TABELLA & "" & _
          " WHERE DATA Between #" & Format(Me.DAL.Text, "MM/DD/YYYY") & "# And #" & Format(Me.AL.Text, "MM/DD/YYYY") & "# AND T='L'" & _
          " GROUP BY DESCRIZIONE" & _
          " HAVING Sum(CONTANTI)>=4990*" & GG & "" & _
          " ORDER BY DESCRIZIONE"

NOTE
GG is the reuslt of:

DATA1 = Me.DAL.Text
DATA2 = Me.AL.Text

GG = WorkingDays(DATA1, DATA2)

GG as dimensioned Long

ops...

RESOLVED my self.
Tks in other case

[HansV]

Good!