Results 1 to 2 of 2
  1. #1
    12345678 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    3

    find the closest year

    Hi,

    I have tables "A" and B" that are linked through the variable CN6. I want to select the variables of Table B (it is variable INDEX in this case) where the year in Table B is the closest to the year in table A (by closest I mean the closest lower or equal year)
    I wrote the following query
    SELECT A.Deal, B.INDEX
    FROM A INNER JOIN B ON A.CN6 = B.CN6
    WHERE (((B.INDEX)=Max([B].[year]<=[B].[Year])));

    I am receiving an error that I cannot use an aggregate function in the where clause.


    Any help will be greatly appreciated

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here's a link to aggregate functions in sql.
    http://www.sql-tutorial.com/sql-group-by-sql-tutorial/

    You'll fiind more if you google
    "sql aggregate functions"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to display week of the year
    By wongray in forum Access
    Replies: 2
    Last Post: 12-28-2010, 04:46 AM
  2. default month and year
    By beefyalby in forum Forms
    Replies: 3
    Last Post: 12-05-2010, 11:40 PM
  3. Year to date sum
    By jzacharias in forum Database Design
    Replies: 6
    Last Post: 09-10-2010, 10:38 AM
  4. fiscal year
    By RedGoneWILD in forum Programming
    Replies: 4
    Last Post: 08-04-2010, 01:38 PM
  5. by year by month
    By nkuebelbeck in forum Reports
    Replies: 21
    Last Post: 03-24-2010, 01:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums