Solved

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

Posted on 2011-03-14
4
280 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
  • 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 51

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 51

Accepted Solution

by:
HainKurt earned 500 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now