Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA 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 September 6th, 2007, 06:30 AM
Registered User
 
Join Date: Sep 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Aprile
Default finding highest value

Hello, everyone.

I am trying to find the highest site program year for each site number. I have a SiteNumber and a SiteProgramYear. I was told to use this DMAX("site_program_year","tblsss","site_number=" & "****")but all this is doing is pulling up the highest site program year.

Any help would be appreciated.

Regards,


Aprile
www.accessdatabaserepair.com - Access Repair Tool
 
Old September 7th, 2007, 10:42 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You do this in SQL using a derived table, but I am not sure this is done in Access ANSI 89 SQL.

Try creating a local table, and a delete query to empty it before each code run.

Then a query to list all the Site_Numbers (SELECT DISTINCT Site_Number...)

Then when you run your report, delete all data in the local table, open the distinct query, take the first site number, take the DMAX() for that sitenumber from the first table, and post it to the local report table. Then move on to the next site number. When that is all done, open the report on the local report table.

The local report table will then have all the Site_Numbers, and the results of all the DMAX() functions. I think you will have to take the Site_Number into a variable (sSite As String) and do this:

   sSite = rs("Site_Number")
   ...
   DMAX("site_program_year","tblsss","site_number='" & sSite "'")


Is that helping any?

mmcdonal
 
Old October 2nd, 2007, 02:59 PM
pjm pjm is offline
Authorized User
 
Join Date: Jul 2006
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Unless I'm missing something here, the following query should give you the resulting
table that you want.

SELECT tblsss.SiteNumber, Max(tblsss.SiteProgramYear) AS SiteProgramYear
FROM tblsss
GROUP BY tblsss.SiteNumber;


-Phil-
 
Old October 3rd, 2007, 07:13 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You are correct with that. I was thinking there were other figures associated with the sites, like highest sales figures for each site.

mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
Selecting highest count stolte XSLT 2 November 19th, 2008 04:00 PM
first n elements with highest counts in a group manish_jaiswal XSLT 6 February 8th, 2008 09:16 PM
Finding the highest amount keithd Excel VBA 2 May 25th, 2005 01:44 PM
Finding the highest value of a query ry Oracle 1 December 18th, 2004 12:40 PM





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