Go4Expert

Go4Expert (http://www.go4expert.com/)
-   MS Access (http://www.go4expert.com/forums/ms-access-forum/)
-   -   Complicated query needed (http://www.go4expert.com/forums/complicated-query-needed-t792/)

HairyMike 8May2006 14:04

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.

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. 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 :)

shabbir 8May2006 20:14

Re: Complicated query needed
 
I am not sure if its possible in Access because it needs subquery and if its allowed it can be done as follows.
Code: SQL

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".

HairyMike 8May2006 20:29

Re: Complicated query needed
 
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

shabbir 8May2006 22:22

Re: Complicated query needed
 
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.


All times are GMT +5.5. The time now is 18:31.