Solved

JOIN problem

Posted on 2008-10-23
11
167 Views
Last Modified: 2012-05-05
I have a problem with generating report... problem with data combination...

if i select the first option i can generate --One report
if i select the second option i can generate --Second report (inner join)

Third option none of the report will work

How can i generate both report at same time:

Is there any method that we can go to code view and edit INNER join to some other join?


0
Comment
Question by:jaisonshereen
  • 6
  • 5
11 Comments
 

Author Comment

by:jaisonshereen
Comment Utility
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
can you clarify the data/tables?

maybe you don't want a JOIN, but a UNION?
0
 

Author Comment

by:jaisonshereen
Comment Utility
Can you make out do you want me to explain?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
JOIN is to "match" records, based on the criteria.

UNION is to append the 2 results
say you have 2 tables, each 10 records. a union of the 2 tables will return 20 records:

SELECT * FROM TABLE1
UNION ALL
SELECT * FROM TABLE2

a JOIN needs a matching criteria, and can, based on the data, return with the same 10+10 records from 0 to 100 rows:
0 rows if there are no matching rows
100 rows if every row matches with all the rows (ie the result is a cross matrix of the 2 tables)

in normal cases, the join will return 10 rows if all the rows match once.

0
 

Author Comment

by:jaisonshereen
Comment Utility
This is the query
queryDetailDesc.JPG
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
so union is not what you need.

now, I don't get what you understand by:

if i select the first option i can generate --One report
  -> what do you mean exactly by  "one report" ?
  * note: the first option is the INNER JOIN, actually

if i select the second option i can generate --Second report (inner join)
  * note: the second option is the LEFT OUTER JOIN, actually

Third option none of the report will work
  -> can you clarify, please?

How can i generate both report at same time?
  -> also here, it's not clear...

sorry to ask you so many questions, but I don't get the "big picture", yet.

0
 

Author Comment

by:jaisonshereen
Comment Utility
Np angell heard about u a lot ...


if i select the first option i can generate --One report
  -> what do you mean exactly by  "one report" ?
  * note: the first option is the INNER JOIN, actually

this report you suppose "report A"


if i select the second option i can generate --Second report (inner join)
  * note: the second option is the LEFT OUTER JOIN, actually


this report you suppose "report B"

Third option none of the report will work
  -> can you clarify, please?


this report you suppose "i cannot generate either report A or B "

How can i generate both report at same time?
 
I need to generate report A and B .. by selecting any of the option above or by editing the code
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
let me understand:
you have 1 query, and want to run 2 reports on it, "at the same time" meaning: with the same query, without modification?

then, you will have to duplicate the query, one with the LEFT JOIN (option 2), and one with the INNER JOIN (option 1)
0
 

Author Comment

by:jaisonshereen
Comment Utility
ok

I need to generate same report with some criteria, this criteria is selected by down box and calling a query which interns call another query...

the another query is what i given here ...above in snapshot...


so if a select a value A in dropdown i can generate report A when i select the option 1 radio button which is a INNER JOIN

but if i select a value B in dropdown i can generate report B when i select the option 1 radio button which is a LEFT OUTER JOIN


I cannot creat both by selecting any of the radio buttons INNER JOIN or LEFT OUTER JOIN
0
 

Author Comment

by:jaisonshereen
Comment Utility
Sorry typo:

but if i select a value B in dropdown i can generate report B when i select the option 2 radio button which is a LEFT OUTER JOIN
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
as I said, you will need 2 queries, you cannot have it in 1 query.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

763 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

9 Experts available now in Live!

Get 1:1 Help Now