Results 1 to 3 of 3
  1. #1
    StewartS is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2014
    Location
    Yorkshire
    Posts
    2

    Create Count in Query


    I have a query which returns the following details

    Contract_id PO_number etc
    1694 1234
    1694 1234
    1695 1235
    1695 1236

    So there are incidents where a contract_id can have a single PO_Number against it or Multiple PO_Numbers I am grouping this to connect to a table to bring back some values matching on the Contract_ID with the PO_Number as a field in the new query. If I group on contract and PO then I get individual for most of the outputs but of course multiple details where there are more than one different PO_Numbers to the Contract. I think if I could have a count on the PO_number to return the data as. I can resolve the issue.

    Contract_id PO_number etc Count
    1694 1234 1
    1694 1234 1
    1695 1235 1
    1695 1236 2


    Can anybody help please.

    StewartS

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,771
    What you show is a ranking calculation. http://allenbrowne.com/ranking.html

    Try:

    Query1
    SELECT DISTINCT Table1.Contract_id, Table1.PO_Number FROM Table1;

    Query2
    SELECT Contract_id, PO_Number, (SELECT Count(Contract_id) As HowMany FROM Query1 AS Dupe
    WHERE Dupe.Contract_id=Query1.Contract_id AND Dupe.PO_Number <= Query1.PO_Number) AS BeatenBy
    FROM Query1;

    Query3
    SELECT Table1.Contract_id, Table1.PO_Number, Query2.BeatenBy
    FROM Query2 INNER JOIN Table1 ON (Query2.PO_Number = Table1.PO_Number) AND (Query2.Contract_id = Table1.Contract_id);
    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
    StewartS is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2014
    Location
    Yorkshire
    Posts
    2
    Thanks this is just what I wanted

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

Similar Threads

  1. Replies: 7
    Last Post: 01-16-2014, 09:17 AM
  2. Using Date and Occurence count to create a name
    By DatabaseIntern in forum Forms
    Replies: 1
    Last Post: 07-11-2013, 11:08 AM
  3. COUNT Group Count Records COUNT FUNCTION
    By PMCOFFEY in forum Access
    Replies: 9
    Last Post: 11-09-2012, 09:40 PM
  4. Replies: 7
    Last Post: 05-02-2012, 08:04 AM
  5. Count Query
    By athomas8251 in forum Queries
    Replies: 11
    Last Post: 12-12-2011, 06:46 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