How do I create a query with 2 tables and 5 queries all left joined?

I'm working on a database of consultants, their contracts and amendments to those contracts.  I have 3 queries that calculate values for conditional data. For instance, I have qryEndDate, which produces the contract's original end date unless the contract has been amended in which case it displays the max end date from the amendments corresponding to the contract.

I have 2 queries that count the number of amendments or payments for a particular contract.

Then there's the main contract table.

I tried making a query to hold all of the data that I'd like to go into a contracts report but because it comes from so many places, it gave me an error message about having too many joins.

How can I create the query?

Who is Participating?
HainKurtConnect With a Mentor Sr. System AnalystCommented:
try something like this:

select t1., t2.col1, t3.col2, t4.col3, t5.col4
from table1 as t1
left join table2 as t2 on
left join table3 as t3 on
left join table4 as t4 on
left join table5 as t5 on
order by

it will show all record from table1 and the columns from joined tables, if there are no matching records in joined tables those columns will be null
I would suggest using a PL/SQL stored proc to get the work done.

Create procedure proc_name (var1 IN type1, var2 IN type2, varOut OUT TYPE3)

//declare local variables

//query to get original end date --store in one local variable
//query to get no of payments - store in local variable 2

//query the contracts table with required columns where end date = var1 and noofpayments = var2
return the result of above query in the OUT variable.

HainKurtSr. System AnalystCommented:
access & PL/SQL :)

post your query (simplified)...

melodymurrayAuthor Commented:
SELECT tblA.A_ID, tblA.DateCreated, tblA.ProjectCodeID,(....), tblA.StartDate, qry1.EndDateC, qry2.WorkDaysC, qry3.DailyRateC, qry4.TotalDaysCharged, qry5.NumAmendments
FROM (((((tblA LEFT JOIN tblB ON tblA.A_ID = tblB.A_ID) INNER JOIN qry1 ON tblA.A_ID = qry1.A_ID) INNER JOIN qry2 ON tblA.A_ID = qry2.A_ID) INNER JOIN qry3 ON tblA.A_ID = qry3.A_ID) INNER JOIN qry4 ON tblA.A_ID = qry4.A_ID) INNER JOIN qry5 ON tblA.A_ID = qry5.A_ID;

The joins were chosen by the Access. I tried to simplify it, but it's so long. The (...) represents some more fields from the tblA that I'm selecting.

 I want to show ALL contractIDs (tblA.A_ID) and the corresponding values if they exist.

I'm pretty new to access. I understand how to used it, but I'm not familiar with VBA or SQL.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.