?
Solved

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

Posted on 2011-03-14
4
Medium Priority
?
286 Views
Last Modified: 2012-05-11
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
Comment
Question by:melodymurray
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 1

Expert Comment

by:gjaya26
ID: 35132767
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
 
LVL 57

Expert Comment

by:HainKurt
ID: 35132797
access & PL/SQL :)

post your query (simplified)...

0
 

Author Comment

by:melodymurray
ID: 35134214
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
 
LVL 57

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 35138256
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question