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