[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

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?

0
melodymurray
Asked:
melodymurray
  • 2
1 Solution
 
gjaya26Commented:
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
0
 
HainKurtSr. System AnalystCommented:
access & PL/SQL :)

post your query (simplified)...

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

Thanks
0
 
HainKurtSr. 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 t1.id=t2.id
left join table3 as t3 on t1.id=t3.id
left join table4 as t4 on t1.id=t4.id
left join table5 as t5 on t1.id=t5.id
order by t1.id

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
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now