Sum Unique Values Between a Date Range (1 Viewer)

[Garindan]

Hi all, I can't work out how to do this query...

qselProductSalesCalculator shows a date when a product was purchased, the name of the product, the salesperson, and the quantity of the product, as follows:-

Salesperson Date_TimeOfPurchase Product Quantity

Mike 24/05/2009 Mulch 10
Mike 10/06/2009 Mulch 3
Mike 10/06/2009 Compost 20
Mike 21/06/2009 Mulch 8
Jim 03/08/2009 Bark 15
Jim 03/08/2009 Mulch 10
Rob 08/12/2009 Compost 5
Rob 31/12/2009 Grass Seed 3
etc etc

I/the boss, want to be able to view how much of each product a salesperson has sold between two dates. I already have this data in a subform, and a main form set up to search by salesperson and date range.

What I can't figure out is how to sum and group the query. So if for example I seached for Mike, between 01/05/2009 and 01/07/2009 I would like it to show...

Mike Mulch 21
Mike Compost 20

Any idea's? I know I don't need/want the date to show, but I still need it in the query don't I to be able to search between dates.

Sorry I've been working hard at this database for a few days now and my brains starting to get a bit confused lol

 

[Galaxiom]

Set the date range criteria but untick Show for the Date field.
Group by Salesperson and Product and Sum the Quantity

 

[Garindan]

Thanks fountain of knowledge but seriously thanks for helping out, you have helped me loads

I had tried the group by product and salesperson, sum on quantity, but I'm not sure how to tie the date into my search form. The search form currently has the following code:-

Option Compare Database
Option Explicit

Private Sub btnClear_Click()

    Dim intIndex As Integer
    
    ' Clear all search items
    Me.txtStartDate = ""
    Me.txtEndDate = ""
    Me.cmbSalesperson = ""
    
    DoEvents
    Me.fsubProductSalesCalculatorDetails.Form.RecordSource = "SELECT * FROM qselProductSalesCalculator WHERE 1=0;"
    
End Sub

Private Sub btnSearch_Click()
    
    ' Update the record source
    Me.fsubProductSalesCalculatorDetails.Form.RecordSource = "SELECT * FROM qselProductSalesCalculator " & BuildFilter
    
    ' Requery the subform
    Me.fsubProductSalesCalculatorDetails.Requery
    
End Sub


Private Sub Form_Load()
    
    ' Clear the search form
    btnClear_Click
    
End Sub

Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim intIndex As Integer
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.

    varWhere = Null  ' Main filter
    
    ' Check for Salesperson
    If Me.cmbSalesperson > "" Then
        varWhere = varWhere & "[Salesperson] LIKE """ & Me.cmbSalesperson & "*"" AND "
    End If
    
    ' Check for Start Date
    If Me.txtStartDate > "" Then
        varWhere = varWhere & "([Date_TimeOfPurchase] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
    End If
    
    ' Check for End Date
    If Me.txtEndDate > "" Then
        varWhere = varWhere & "([Date_TimeOfPurchase] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
    End If
    
    
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
    
    BuildFilter = varWhere
    
End Function

Can I get this to work with date criteria or do i need to do it differently?

 

[jdraw]

Based on Glaxiom's suggestion, the SQL in your function would be based on the query you have and look something like

SELECT 
qselProductSalesCalculator.Salesperson
, qselProductSalesCalculator.Product
, Sum(qselProductSalesCalculator.Quantity) AS SumOfQuantity
FROM qselProductSalesCalculator
GROUP BY qselProductSalesCalculator.Salesperson, qselProductSalesCalculator.Product, qselProductSalesCalculator.Date_TimeOfPurchase
HAVING (((qselProductSalesCalculator.Date_TimeOfPurchase) Between [COLOR="Red"]#5/1/2009#[/COLOR] And [COLOR="Red"]#7/1/2009#[/COLOR]));

You have to use your textboxes to get the date range you want and then substitute into this SQL.

 

[Garindan]

Sorry could you explain a bit more? I'm unsure of the linking the text boxes to the query criteria bit

When I use criteria between two dates in the query it shows separate records for each date in the results which is not what I want. I would like it to show the total quantity for a product between two dates, so each product would only have one entry and a large total quantity.

Edit - I've been playing a bit more and this gives me the results I want:-

SELECT tblSalesperson.Salesperson, tblProducts.Product, Sum(tblCustomerPurchasedItems.Quantity) AS SumOfQuantity
FROM tblProducts INNER JOIN (((qselCustomerDetails INNER JOIN tblCustomerPurchases ON qselCustomerDetails.CustomerNumber = tblCustomerPurchases.CustomerNumber) LEFT JOIN tblSalesperson ON qselCustomerDetails.SalespersonID = tblSalesperson.SalespersonID) INNER JOIN tblCustomerPurchasedItems ON tblCustomerPurchases.PurchaseNumber = tblCustomerPurchasedItems.PurchaseNumber) ON tblProducts.ProductID = tblCustomerPurchasedItems.ProductID
WHERE (((tblCustomerPurchases.Date_TimeOfPurchase) Between [start date] And [end date]))
GROUP BY tblSalesperson.Salesperson, tblProducts.Product;

Is this correct?

So how can I translate this to my form? Do I leave the query as including all four fields (including date) but have SQL in my form?

 

[jdraw]

Your original post said you were having trouble with a query.
Glaxiom suggested an approach to give you the results you wanted.

I basically used the Query you provided and queried that (along Glaxiom's guide) to give the result you wanted.

Then you showed a bunch of vba code including SQL for a subform recordsource and a combo and textbox to select parameters for a Where clause.

With the aggregate function you don't use a Where, but you do use a HAVING.

You could use the SQL I gave and replace the StartDate and EndDate with selections from controls on your Form, then build the proper SQL substituting your control values as appropriate.

You seem to have included your original query code with subsequent code to create a new query that gives the result you need -- so use it and include StartDate and EndDate from your controls in the SQL. Use the SQL as the recordsource for the subform.

 

[Galaxiom]

To refer to the value in a control on a form:
[Forms]![formname].[controlname]

This can be used directly in SQL used many situations.

However the SQL in CurrentDb.Execute cannot refer to objects other than the tables and queries. In this case the value must be concatenated into the string.

And since it is a date the format must be #mm/dd/yyyy#

Many developers use "SELECT ..... #" & Format(dateexpression, "mm/dd/yyyy") & "# etc"

However a better globally reliable expression that includes the # in the format is:

"SELECT ..... " & Format(dateexpression, "\#mm\/dd\/yyyy\#") & " etc"

 

[DCrake]

I have often seen the date expression format using the \#mm\/dd\/yyyy\#" syntax. Have normally associated this with Access and SQL and or Uk and US formats. But what is the logic behind it? how soes it actually interpret it? Seems an unusual question from someone with my experience but have never had the ned to use it as yet. Just interested that's all

 

[Galaxiom]

Backslash indicates the next character is literal.

Although the forward slash is best known as the date separator in many regions, it is actually a replacement marker in the format string and in a date format string it will substitute the regional date separator which might not be the forward slash. (I learnt this at this site.)

So \/ (backslash fowardslash) means literal forward slash and is guaranteed to give the right sql in any region.

In my databases I have a function called DateSQL which makes for very tidy code.

It has an optional argument to include the time which then uses the format string:
"\#mm\/dd\/yyyy hh\:nn\:\ss\#"

It is completed by an optional SQL Server argument that uses the single quote instead of the hash.

 

[DCrake]

Thank you very much and when the alcohol level subsides at 00:55 am I will thank you even more.

I think it is time for bed.

zzzzzzzzzzzzzzzzzzzzzzzz

 

[Garindan]

I'm sorry, I'm really confused! I realise now that it's the record source of fsubProductSalesCalculator that you have been talking about changing, and not qselProductSalesCalculator itself.

So I have tried

SELECT qselProductSalesCalculator.Salesperson, qselProductSalesCalculator.Product, Sum(qselProductSalesCalculator.Quantity) AS SumOfQuantity
FROM qselProductSalesCalculator
GROUP BY qselProductSalesCalculator.Salesperson, qselProductSalesCalculator.Product, qselProductSalesCalculator.Date_TimeOfPurchase
HAVING (((qselProductSalesCalculator.Date_TimeOfPurchase) Between #5/1/2009# And #7/1/2009#));

And it doesn't produce the results I want!

But

SELECT qselProductSalesCalculator.Salesperson, qselProductSalesCalculator.Product, Sum(qselProductSalesCalculator.Quantity) AS SumOfQuantity
FROM qselProductSalesCalculator
WHERE (((qselProductSalesCalculator.Date_TimeOfPurchase) Between #5/1/2009# And #7/1/2009#))
GROUP BY qselProductSalesCalculator.Salesperson, qselProductSalesCalculator.Product;

does product what I want. So then I have to replace the two dates in the source query with [Forms]![frmProductSalesCalculator].[txtStartDate] and [Forms]![frmProductSalesCalculator].[txtEndDate]. Is this correct?

So..

SELECT qselProductSalesCalculator.Salesperson, qselProductSalesCalculator.Product, Sum(qselProductSalesCalculator.Quantity) AS SumOfQuantity
FROM qselProductSalesCalculator
WHERE (((qselProductSalesCalculator.Date_TimeOfPurchase) Between [Forms]![frmProductSalesCalculator]![txtStartDate] And [Forms]![frmProductSalesCalculator]![txtEndDate]))
GROUP BY qselProductSalesCalculator.Salesperson, qselProductSalesCalculator.Product;

Is this correct?

However, now I'm unsure what to put in the code of frmProductSalesCalculator to show the results. I currently have

Option Compare Database
Option Explicit

Private Sub btnClear_Click()

    Dim intIndex As Integer
    
    ' Clear all search items
    Me.txtStartDate = ""
    Me.txtEndDate = ""
    Me.cmbSalesperson = ""
    
    ' Requery the subform
    Me.fsubProductSalesCalculatorDetails.Requery
    
End Sub

Private Sub btnSearch_Click()
    
    ' Requery the subform
    Me.fsubProductSalesCalculatorDetails.Requery
    
End Sub


Private Sub Form_Load()
    
    ' Clear the search form
    btnClear_Click
    
End Sub

What do I add to the search button code etc to update the query with the dates from the text boxes? Sorry I know I'm a noob but I am learning a lot