CODEWITHSUNDEEP
sqlms-access
tslupphaug
tslupphaug

Reputation: 17

How can I combine a union query with a max query?

First of all, my data is gathered from the following tables:

My goal is to combine the results of the below mentioned A (a UNION query) and B (a MAX query), and to make them work together (see C):

A) The union query:

SQL code:

    SELECT ProjectID,0 as [0/DocID],ProjectTitle
    FROM tblProjects

    UNION ALL SELECT ProjectID,DocID,DocTitle
    FROM tblDocuments

    ORDER BY ProjectID, [0/DocID];

RESULT:

    ProjectID    | 0/DocID | Project/DocTitle 
    ------------------------------------------
    1            | 0       | Project 1        
    1            | 1       | Document 1.1     
    1            | 2       | Document 1.2
    2            | 0       | Project 2
    2            | 3       | Document 2.1
    2            | 4       | Document 2.2

B) The max query:

SQL code:

    SELECT tblDocuments.DocID, Max(tblRevisions.SubDate) AS MaxOfSubDate
    FROM tblDocuments LEFT JOIN tblRevisions ON tblDocuments.DocID = tblRevisions.DocID
    GROUP BY tblDocuments.DocID;

RESULT:

    DocID    | MaxOfSubDate 
    -----------------------
    1        | 22.02.2016        
    2        | 20.02.2016     
    3        | (blank)
    4        | 21.02.2016

C) Combining A and B:

(NB! I do not necessarily mean I want to compile the code from A and B into one single query. It could also be through a set of different queries: i.e. a third query which gathers data from A and B).

SQL code:

    ? I have absolutely no clue ?

DESIRED RESULT:

    ProjectID    | 0/DocID | Project/DocTitle | MaxOfSubDate | Rev  | ReturnDate 
    ---------------------------------------------------------------------------------
    1            | 0       | Project 1        | 0            |      | 
    1            | 1       | Document 1.1     | 12.02.2016   |  03  | 22.02.2016
    1            | 2       | Document 1.2     | 10.02.2016   |  04  | 15.02.2016
    2            | 0       | Project 2        | 0            |      |
    2            | 3       | Document 2.1     | (blank)      | (bl) | (blank)
    2            | 4       | Document 2.2     | 21.02.2016   |  02  | (blank)

Upvotes: 0

Views: 249

Answers (2)

tslupphaug
tslupphaug

Reputation: 17

Create a new query (B2) that asks for tblRevisions' correlating data to query B's maxOfSubDate. Query B2: "qryLatestRevDetailsAll" (created using design view):

SELECT tblDocuments.DocID, tblDocuments.DocTitle, tblDocuments.OurDocNo, tblDocuments.ClientDocNo, tblRevisions.OurRev, tblRevisions.ClientRev, tblRevisions.SubDate, tblRevisions.SubCode, tblRevisions.RetDate, tblRevisions.RetCode, tblRevisions.Comments, tblRevisions.Field1
FROM tblProjects 
INNER JOIN ((qryMAXSubDate 
  INNER JOIN tblDocuments ON qryMAXSubDate.DocID = tblDocuments.DocID) 
  INNER JOIN tblRevisions ON (qryMAXSubDate.MaxOfSubDate = tblRevisions.SubDate) 
  AND (tblDocuments.DocID = tblRevisions.DocID)) ON tblProjects.ProjectID = tblDocuments.ProjectID;

Step 2: combine the union query (A) with B2:

 SELECT quniProjectsDocuments.ProjectID, quniProjectsDocuments.[0/DocID], quniProjectsDocuments.ProjectTitle, quniProjectsDocuments.OurDocNo, quniProjectsDocuments.ClientDocNo, [qryLatestRevDetailsAll].OurRev, [qryLatestRevDetailsAll].SubDate, [qryLatestRevDetailsAll].RetDate
 FROM quniProjectsDocuments 
 LEFT JOIN qryLatestRevDetailsAll ON quniProjectsDocuments.[0/DocID]=[qryLatestRevDetailsAll].DocID
 ORDER BY quniProjectsDocuments.ProjectID, quniProjectsDocuments.[0/DocID];

Thanks a lot to @randyh22 for pointing me in the right direction!

Upvotes: 0

randyh22
randyh22

Reputation: 473

Do exactly what you said. Create your first query A and your second query B and then create a third query C that will join the two results.

SELECT A.*, B.MaxOfSubDate, B.MaxOfOurRev, B.MaxOfClientRev
FROM A
LEFT OUTER JOIN B On A.[0/DocID] = B.DocID

Upvotes: 1

Related Questions