Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 24th, 2007, 02:51 AM
Authorized User
 
Join Date: Mar 2006
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default Lowest and highest values in report list

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!
 
Old July 24th, 2007, 06:48 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old July 24th, 2007, 07:28 AM
Authorized User
 
Join Date: Mar 2006
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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!
 
Old July 24th, 2007, 07:44 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old July 24th, 2007, 09:48 AM
Authorized User
 
Join Date: Mar 2006
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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!!!


 
Old July 24th, 2007, 09:51 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

What is the code for the other control?

mmcdonal
 
Old July 25th, 2007, 03:05 AM
Authorized User
 
Join Date: Mar 2006
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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!!

 
Old July 25th, 2007, 06:26 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
multi-column list box values moved to 2nd list box sbmvr Access VBA 1 May 14th, 2007 01:58 PM
Select the latest date, then the lowest price. Stuart Stalker SQL Server DTS 2 October 12th, 2006 03:42 AM
lowest Price Fetching dpkbahuguna Beginning VB 6 2 February 28th, 2006 07:15 AM
get a lowest price Jane SQL Language 1 March 11th, 2005 10:02 AM
Finding the row with the lowest time value Paulsh Access VBA 3 November 16th, 2004 03:06 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.