Hiya folks!

I have a list that shows the total sales of different products, such as the following:

PRODUCT SALES
Hamburgers 40 Euros
French Fries 0 Euros
Coca Cola 20 Euros
Ginger Ale 5 Euros

I have been able to create a field that shows the highest sales value and lowest sales value in the list using the min() and max() functions.

There are two things I cant seem to do:

1. In the above example, I have one article with "zero" sales value, and I do not want this to be considered using the min() function, so the result should be "5 euros" instead of "0 euros".

2. For each result that is given back by the function, I want the name of the product to appear in the footer of the group. For example, if the result should show the following for the above example:

Highest sales: Hamburger (40 euros)
Lowest sales: Ginger Ale (5 euros)

Thank you!

What happens if you have a tie? This would be something to develop in a query and paste into a text box. Or, develop in code and paste.

I would favor code. Since the form is based on a query, I presume, I would run subs against that query, or a totals query running against that query, and pull these results without referencing the report.

So for this one, I would do something like:

Dim sSQL As String

sSQL = "SELECT Top 1 qryYourTotalsQuery.ProductName, Max(qryYourTotalsQuery.ProductSales) AS MaxProductSales
FROM qryYourTotalsQuery ORDER BY Max(qryYourTotalsQuery.ProductSales) DESC"

This would give you the max from your query. Then do the same with to get the min. Then do the call to this data in the method of your choice. I think a simple

Me.Max.RowSource = sSQL

might work. You may have to open a recordset and do

sProductName = rs("ProductName")
sTopSales = rs("MaxProductSales")
Me.MaxProductName = sProductName
Me.MaxSales = sTopSales

but this should not be too tough. There might be a more elegant way, but I think this will work.

Did that help at all?

mmcdonal

Thanks for your reply!

Just one thing... I am not very good at code... but if I am given a piece of code and told where to plug them in (for example "put this code in the afterupdate event of a specific field", I can manage).

I used the Design View of the query to build the query in the report.

So where would I put or create the codes that you gave me?

A tie? Hmmmm... didnt think about that. Can we somehow show more than one result? I think this should be a rare occurence.

Thank you!

Well, I would use the following sort of code in the On Format event of either the Header or Footer, whereever you want these text boxes.

Dim rs As ADODB.Recordset
Dim sSQL As String
Dim sProductName As String
Dim sTopSales As String

sSQL = ""SELECT Top 1 qryYourTotalsQuery.ProductName, Max(qryYourTotalsQuery.ProductSales) AS MaxProductSales FROM qryYourTotalsQuery ORDER BY Max(qryYourTotalsQuery.ProductSales)DESC"

Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

sProductName = rs("ProductName")
sTopSales = rs("MaxProductSales")

rs.Close

Me.MaxProductName = sProductName
Me.MaxSales = sTopSales





mmcdonal

Hi again!

I have inserted the code.... but two minor problems:

1. The report is based on a query that was built from within the report. So not sure how to 'refer to' the query (in your example I would need to replace the qryYourTotalsQuery.ProductSales with the name of the control holding that data in my case [ProductSales]. However, in that same line you have 'FROM qryYourTotalsQuery' . How would I refer to the underlying query within the report?

2. The sSQL = ""Select line is showing an error (end of statement is missing).

Thanks!!!

What is the code for the other control?

mmcdonal

Hi again!

I am not sure which code you are asking about? This is what I have:

The query I built from within the report design is as follows:

SELECT tblSalesDate.SalesDate, tblArticlesSold.ArticleCode, tblArticlesSold.QuantitySold, tblArticlesSold.ProductCategory, tblArticlesSold.Product, tblArticlesSold.Price, tblArticlesSold.Cost,
FROM tblSalesDate INNER JOIN tblArticlesSold ON tblSalesDate.DateID = tblArticlesSold.DateID
WHERE (((tblSalesDate.SalesDate)>=[Forms]![frmSelectDatesSingle]![From] And (tblSalesDate.SalesDate)<=[Forms]![frmSelectDatesSingle]![To]) AND ((tblArticlesSold.Category) Not Like "Ice Cream Flavours" And (tblArticlesSold.Categoria) Not Like "Side Dishes"));

The text boxes that I created to hold the result are called:

MaxProductName (for the name of the product)
MaxSales (for the value of the product that had the most sales value registered)

The code I inserted in the OnFormat event of the header section where I want the resultis as follows:

Dim rs As ADODB.Recordset
Dim sSQL As String
Dim sProductName As String
Dim sTopSales As String

sSQL = ""SELECT Top 1 [Product], Max([QuantitySold]) AS MaxProductSales FROM ************ ORDER BY Max([QuantitySold])DESC"

Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

sProductName = rs("Product")
sTopSales = rs("MaxProductSales")

rs.Close

Me.MaxProductName = sProductName
Me.MaxSales = sTopSales

Thank you!!

Take your query SQL and create a static query with the same parameters, then refer to that query name in the code.

Did that help?


mmcdonal