Results 1 to 5 of 5
  1. #1
    label027 is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2011
    Posts
    8

    Filtering dupe Data in a single field


    I have a database that calculates fees for the use of certain products. There are fees for each rep's use of each product and also a firm fee, which is a fee that the firm pays if they have one or more reps using a certain product.

    Right now I have 2 queries filtering out those firms that have to pay firm fees, lets say Product A has a firm fee of $50 and Product B has a firm fee of $100.

    The problem I am having is there are firms that have reps that just use product A and reps that use product B, so they are getting charged $50 and $100. I want to only charge the firm $100 since it includes every product that is cheaper than that. I hope this makes sense. Example:

    Firm 1 product A
    Firm 2 product A
    Firm 3 product B
    Firm 4 product A
    Firm 4 product B

    Is there any way to filter so each firm is listed only once in the first field and is charged with the higher price in the second field?

    Sorry, I would attach the database but it has sensitive information and it is a HUGE database. Thank you for the help!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Maybe:
    SELECT Firm, Max(Fee) as MaxFee FROM table GROUP BY Firm;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    label027 is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2011
    Posts
    8
    That works perfect, except I want to add a description field to the query as well. This is what I have that works (I know the table/field names are terrible):

    SELECT [DELETE BEFORE USE: Firm Fees].[Firm ID], Max([DELETE BEFORE USE: Firm Fees].[Firm Fee]) AS MaxFee
    FROM [DELETE BEFORE USE: Firm Fees]
    GROUP BY [DELETE BEFORE USE: Firm Fees].[Firm ID];


    This is what I want, but doesn't work, just an addition of Product Description:

    SELECT [DELETE BEFORE USE: Firm Fees].[Firm ID], Max([DELETE BEFORE USE: Firm Fees].[Firm Fee]) AS MaxFee, [DELETE BEFORE USE: Firm Fees].[Product Description]
    FROM [DELETE BEFORE USE: Firm Fees]
    GROUP BY [DELETE BEFORE USE: Firm Fees].[Firm ID], [DELETE BEFORE USE: Firm Fees].[Product Description];

    I understand why it doesn't work, because the descriptions on two of the same Firm ID's are different so it lists both of them. I just do not know a way around it.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Try:

    SELECT Table1.Firm, Max(Table1.Fee) AS MaxFee FROM Table1 GROUP BY Table1.Firm;

    SELECT Table1.Firm, Table1.Fee, Table1.Product
    FROM Query1 INNER JOIN Table1 ON Query1.Firm = Table1.Firm
    WHERE Table1.Fee=[MaxFee];

    Nested:
    SELECT Table1.Firm, Table1.Fee, Table1.Product
    FROM (SELECT Table1.Firm, Max(Fee) AS MaxFee FROM Table1 GROUP BY Table1.Firm) As Query1
    INNER JOIN Table1 ON Query1.Firm = Table1.Firm
    WHERE Table1.Fee=[MaxFee];
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    label027 is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2011
    Posts
    8
    That works perfect. Thanks!

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

Similar Threads

  1. Replies: 1
    Last Post: 05-17-2011, 05:19 AM
  2. Filtering on Field Names from Table
    By reddog1898 in forum Access
    Replies: 3
    Last Post: 05-13-2011, 10:47 AM
  3. Exporting data from single fields
    By Kipster1203 in forum Import/Export Data
    Replies: 1
    Last Post: 08-06-2010, 10:02 AM
  4. Filtering/sorting data
    By jemelton in forum Access
    Replies: 5
    Last Post: 06-09-2010, 01:47 PM
  5. Filtering data
    By billybong in forum Access
    Replies: 1
    Last Post: 11-07-2009, 12:20 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