asked on

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?

gjaya26

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.

END

HainKurt

access & PL/SQL :)

post your query (simplified)...

melodymurray

ASKER

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.

Thanks

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial