Results 1 to 4 of 4
  1. #1
    dsk96m is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    13

    Question Summary query

    So I have a table full of data. I am trying to create a query that will give me a summary. So here is an example of what the data looks like: * symbolizes unique fields

    ID* Status Team_name Team ID Persons_Name Persons_ID CC Role Proposed


    1 Pending Team 1 1 Name 1 1234 PA L 1000
    2 Pending Team 1 1 Name 2 1235 PA C 800
    3 Pending Team 1 1 Name 6 1239 PA P 500

    4 Pending Team 2 2 Name 3 1236 SA L 1000
    5 Pending Team 2 2 Name 4 1237 PA P 500

    6 Pending Name 5 1238 L1 L 1000


    So if you look at the team 1 there are three different roles and three different amounts. within a team there will only be at max 3 amounts. So I can get the max using max(proposed) and the min using min(proposed). I am trying to get it to look like this 1000/800/500. So far i have the 1000 and the 500 using PROPOSED(L/P): Max([proposed_amt]) & "/" & Min([proposed_amt]). I need to get the value in the middle. Ive tried everything. That is my first step.

    I am trying to get results like this:
    Team_Name Team_ID Proposed(L/C/P)
    Team 1 1 1000/800/500



    Thank you for the help guys.

  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
    You want to concatenate values from related records, check this http://allenbrowne.com/func-concat.html

    Or might be able to do a subquery or DLookup that finds value for each team that is > Min and < Max
    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
    dsk96m is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    13
    Awesome, that work perfectly. Thank you so much.

    My next question is sort of the same: This is what I have
    IIf([empl_cc]="PA" Or [empl_cc]="OA" Or [empl_cc]="SA" Or [empl_cc]="C84" Or [empl_cc]="BG" Or [empl_cc]="8Z" Or [empl_cc]="KB440" Or [empl_cc]="KE477","Y","")

    so i have a team with more than 1 person. I want to show Y if anyone in that team matches the above. Right now I am getting two results, one with Y because there is someone that satisfies the above and there is also someone who doesnt. So both rows are showing up, where i just want one. Would another function be the best idea or is there a simpler way of doing 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
    Where did you put that expression?
    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.

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

Similar Threads

  1. Summary table query Access 2007
    By DesCall in forum Access
    Replies: 4
    Last Post: 05-05-2011, 02:38 AM
  2. Replies: 0
    Last Post: 11-17-2010, 01:21 AM
  3. No Summary option in Query Wizard
    By LilMissAttack in forum Queries
    Replies: 1
    Last Post: 11-15-2010, 04:32 PM
  4. Summary Query - How to?
    By ritch in forum Access
    Replies: 16
    Last Post: 02-17-2010, 03:18 PM
  5. Product in a Summary Query
    By Fletch in forum Queries
    Replies: 0
    Last Post: 12-11-2008, 03:14 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