Results 1 to 8 of 8
  1. #1
    Ajay371 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Posts
    4

    Talking Finding Records of Company Stock which is Purchased & Sold

    Hello Friends,

    I am trying to build a query for a Stocks Buy/Sale Records.
    Here are the Fields of query that i built.

    Date
    (Date when stock is Bought or Sold)
    PersonalID (Person who Bought or Sold)
    Company (Company whose Stock is Bought or Sold)
    Quantity (Quantity Bought or Sold)


    Price (Price at which it is Bought or Sold)
    TaType (Buy or Sale)

    Now I want to build a query which should give me result of Single Person who has Sold the Stocks of the Companies with Date of Purchase & Sale Records.
    It should Exclude records of Purchase.
    Please help am kind of new to complex queries and these seems complex to me...

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    in your query in the criteria area

    in the PersonalID column, put the ID of the person your want the result for
    int the TaType column put 'Sale' (assumes this is a text field)

  3. #3
    Ajay371 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Posts
    4
    Quote Originally Posted by Ajax View Post
    in your query in the criteria area

    in the PersonalID column, put the ID of the person your want the result for
    int the TaType column put 'Sale' (assumes this is a text field)
    Thanks Ajax,
    Well that will bring up just Sales record
    What I like for my result is like this

    Date PersonalID Company Qty Price TaType

    1/1/2017 Ajay ABC 100 10 Purchase
    2/1/2017 Ajay XYZ 200 20 Purchase
    3/1/2017 Ajay ABC 100 10 Sale
    1/1/2017 Sanjay ABC 75 30 Purchase
    1/1/2017 Ajay PQR 100 10 Purchase
    4/1/2017 Ajay PQR 100 10 Sale
    5/1/2017 Ajay DEF 50 5 Purchase

    I would like my result for Ajay with ABC & PQR company Purchase & Sale Record, and filter all other where there is only Purchase and no sale of any stock.
    I hope this helps...

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    sounds like this is homework and in any event, I don't understand your question. Please see this link for how to build queries

    http://www.opengatesw.net/ms-access-...cess-Query.htm

  5. #5
    Ajay371 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Posts
    4
    Quote Originally Posted by Ajax View Post
    sounds like this is homework and in any event, I don't understand your question. Please see this link for how to build queries

    http://www.opengatesw.net/ms-access-...cess-Query.htm
    Sorry Ajax!
    What I mean is the query should give me result of Only those company whose Stock is Bought and Sold
    If there's Only Purchase of stock of any company it should not come up.

    More Precisely, Company records should pass criteria of having Buy & Sale in TaType field.

    Thanks.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    Here is one possibility:

    SELECT * FROM tablename WHERE TaType = 'Purchase' AND Company IN (SELECT Company FROM tablename WHERE TaType = 'Sale');
    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.

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    I suspect this is going to suffer from 'mission creep'

    To me your requirement is unclear. As stated, these would also qualify

    Date PersonalID Company Qty Price TaType
    1/1/2017 Ajay ABC 50 10 Purchase
    2/1/2017 Ajay XYZ 200 20 Purchase
    3/1/2017 Ajay ABC 100 10 Sale
    1/1/2017 Sanjay ABC 75 30 Purchase
    6/1/2017 Ajay PQR 100 10 Purchase
    4/1/2017 Ajay PQR 100 10 Sale
    5/1/2017 Ajay DEF 50 5 Purchase

    plus these
    2/1/2017 Ajay ABC 100 10 Sale
    3/1/2017 Ajay ABC 100 10 Sale

  8. #8
    Ajay371 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Posts
    4
    Thanks June7
    Works like a charm!!!

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

Similar Threads

  1. Query To Subtract Stock Which Is Sold
    By paddington in forum Queries
    Replies: 12
    Last Post: 06-30-2017, 03:57 PM
  2. Replies: 4
    Last Post: 12-16-2016, 09:57 AM
  3. Replies: 3
    Last Post: 02-26-2013, 05:07 AM
  4. Replies: 2
    Last Post: 10-08-2012, 06:40 PM
  5. Replies: 8
    Last Post: 04-24-2012, 01:05 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