Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MAX function (2000)

    Hi all,

    A little background on my project:

    I have table of different databases and the number of times each database is searched by month. For example:
    Database 1 January 42 searches
    Database 2 January 38 searches
    Database 1 February 50 searches
    .....

    I want to create a report that lists the database with the most searches per month. I envision it looking like the attached Word document.

    I tried using the MAX function in my report, but it gave me the most number of searches per month for EACH database -- so I had different databases with the same numbers, the maxiumum of each database.

    Can anyone help?

    Thanks,
    Kindra
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: MAX function (2000)

    Try a Crosstab query, the wizard will guide you thru.
    Any problems just post back.

    Sorry, I misread your post, this will not suffice.

    If you are expecting the database with the most searches in a month you could try the TOP predicate, but this will only return one record.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MAX function (2000)

    Step 1:

    Create a query based on your table. Add the month and the number of searches to the design grid. Turn it into a Totals query (option in the Query menu); leave month as Group By, but set the Total option for the number of searches to Max. This query returns the maximum number of searches for each month. Save it as qryMaxSearch

    Step 2:

    Create a query based on your table and on qryMaxSearch. Join them on the month AND on the number of searches (so there will be two lines joining them). Add the database, month, and number of searches from the table to the design grid. Turn it into a Crosstab query (option in the Query menu); set the database to Group By in the Total row and Row Heading in the Crosstab row, the month to Group By and Column Heading, and the number of searches to Sum and Value. This will leave unused cells empty, which I would prefer. If you really want the zeros, change the number of searches to Nz(Sum([Searches]),0) and set the Total row to Expression (replace Searches by the actual name of the field you are using.)

  4. #4
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MAX function (2000)

    I didn't explain my table structure very well. Attached is a document that hopefully will describe it better. I'm not sure that I can do what you are suggesting Hans, without a major overhaul of my tables. Or perhaps I'm just not able to extrapolate what you're saying to work with my table structure.

    Thanks everyone!
    Kindra
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MAX function (2000)

    The structure you have chosen is very inflexible; you will have to modify the table structure if you want to add another month. If possible, organize your table as follows:

    <table border=1><td>Database</td><td>Month</td><td>Searches</td><td>104</td><td>July 2003</td><td align=right>3</td><td>104</td><td>August 2003</td><td align=right>4</td><tr><td>

  6. #6
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MAX function (2000)

    That's what I was afraid of -- thanks for the info Hans! I will do as you have suggested.

    Kindra

  7. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MAX function (2000)

    Okay, so I had misunderstood what my user wanted.

    What she wants is to be able to look at a specific database and year, and then determine which month out of that year had the most searches. I used the MAX function in order to determine which month had the most, but whenever i try to add the Month (and the in the total field I have: Group By) to the query, it adds all of the months, when I only want the name of the month that has the most searches (i.e,. it returns all 12 months and I only want 1 month).

    Here's my SQL:

    SELECT DBSTATS.MONTH, Max(DBSTATS.STAT) AS MaxOfSTAT
    FROM DBSTATS
    GROUP BY DBSTATS.MONTH, DBSTATS.DATABASE_ID, DBSTATS.TYPE, DBSTATS.YEAR
    HAVING (((DBSTATS.DATABASE_ID)=[Forms]![frmREPORTS]![cboDatabaseID]) AND ((DBSTATS.TYPE)=6) AND ((DBSTATS.YEAR)=[Forms]![frmREPORTS]![cboYear]));

    Thanks in advance...
    Kindra

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MAX function (2000)

    As in my previous reply, you will have to do in two steps.

    1. Create a query qryMaxOfStat that returns the maximum number of searches:

    SELECT Max(DBSTATS.STAT) AS MaxOfSTAT
    FROM DBSTATS
    WHERE (((DBSTATS.DATABASE_ID)=[Forms]![frmREPORTS]![cboDatabaseID]) AND ((DBSTATS.TYPE)=6) AND ((DBSTATS.YEAR)=[Forms]![frmREPORTS]![cboYear]))

    2. Create a new query based on DBSTATS and qryMaxOfStat. Join them on STAT vs. MaxOfStat. Add MONTH and STAT to the query design grid. This query will return the month with the most searches, and the number of searches.

    Note: if you have problems with the parameters, as in your other thread, you must declare them explicitly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •