Complicated query needed

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.



Problem
I am generating the following report:

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

 

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