Complicated query needed

Discussion in 'MS Access' started by HairyMike, May 8, 2006.

  1. HairyMike

    HairyMike New Member

    Joined:
    May 8, 2006
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    Greetings gurus and friends :)

    I am posting here as a kind of last resort to solve a problem.. I have spent a lot of time in the last week reading up on subqueries etc, but still have this problem.. None of my sql savy friends seem to be able to get their heads around this one either.

    Background
    I am building a document register using ms access. It tracks documents that are issued to subcontractors/clients, revisions that are made to the documents, and previous revisions that have been recalled.

    [​IMG]

    Problem
    I am generating the following report:
    [​IMG]
    The box at the top with the 'Issue Date' is obtained from a subreport where I execute the following sql: SELECT TOP 22 issued.issue_id, issued.issue_date FROM issued ORDER BY issued.issue_id DESC; This gets me the latest 22 document_issue records.

    What I need to do next is return 22 records for each file (from another subreport - linked by issue_id) for each revision of a file that has been issued. As you can probably gather, most of these records are going to be blank. When I say I need the query to return 22 records for each file record.. I mean that I need a query to return records that are like this:
    Code:
    issue_id    file_id    revision_code
    20             1           A4
    21             1
    22             1           C1
    23             1           C3
    24             1
    25             1
    26             1           D1
    ....  etc for 22 records
    20             2        
    21             2           A1
    22             2
    23             2           D2
    ....  etc for every file record
    If you can come up with anything, your my hero :)
     
  2. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    I am not sure if its possible in Access because it needs subquery and if its allowed it can be done as follows.
    Code:
    SELECT TOP n issued.issue_id, issued.issue_date FROM issued WHERE file_id in (SELECT file_id FROM issued ) ORDER BY issued.issue_id DESC
    If it does not allow the sub queries then you need to be taking the file_id and then put it in the "IN CALUSE".
     
  3. HairyMike

    HairyMike New Member

    Joined:
    May 8, 2006
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    Hi sabbir, and thanks for your reply ;)

    Unfortunately, that doesn't help me. The file_id doesn't come from the issued table as more than one may be issued at one time (if it did, it would be a lot easier). The revision_code and file_id data that I need to get come from the revisions table. There is also a table called issued_revisions which has the issue_id and revision_id of all revision records that have been issued.

    Just to recap... I need to get the revision_code, file_id and issue_id (issue_id returned from the query: SELECT TOP 22 issued.issue_id, issued.issue_date FROM issued ORDER BY issued.issue_id DESC)

    Thanks for your efforts sabbir.

    ps:it is possible to use subqueries in ms access
     
  4. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    It hardly matters in whatever table it is it can be sub queried. You can get from the issue table or from issue_revision table.
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice