Mother of all sql queries needed

[HairyMonkeyMan]

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.

http://www.mnd.co.uk/drawing-register-relations.GIF

Problem
I am generating the following report:
http://www.mnd.co.uk/drawing-register-report.gif
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. I plain english, I would describe that I need 22 records for each file that show a revision code if that revision has been issued on that date.

If you can come up with anything, your my hero :thumb:

[olivthill]

Sorry, I don't understand very well your question.
In "return 22 records", what do these records contain? Let's suppose you want to have at least file_id.
In "if that revision has been issued on that date", what is the latter date? Let's suppose you want to have revision_timestamp equal to issue_date.

Maybe the following query is what you need:
SELECT TOP 22
files.file_id
FROM files, revisions, issued_revisions, issued
WHERE
files.file_id = revisions.files_id
AND revisions.revision_id = issued_revisions.revision_id
AND issued_revisions.issue_id = issued.issue_id
AND revisions.revision_timestamp = issued.issue_date;
If there are files with no revisions, or revisions without issued_revisions, or issued_revisions without issued, then you'll need to have outer joins instead of the above implicit inner joins. You can get outer joins by switching from "SQL mode" to "creation (graphical) mode", and rightcliking on the links of the query.

[HairyMonkeyMan]

Hi olivthill,
Thanks for your reply..

I'll try and clarify my problem a little here.. I know what I need to have in my head, its just a little hard to put it into words ;)

When I said 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:

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

now the issue_id's I need to use here are returned from the query: SELECT TOP 22 issued.issue_id, issued.issue_date FROM issued ORDER BY issued.issue_id DESC; So I was thinking subquery is needed, and your probably right, a couple of outer joins.

Thanks for taking the time to help, and hope this is a little clearer..

Kind regards :)