Solved

JOIN problem

Posted on 2008-10-23
11
169 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
ID: 22784685
0
 
LVL 142

Expert Comment

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

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

Author Comment

by:jaisonshereen
ID: 22784766
Can you make out do you want me to explain?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22784792
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
ID: 22784857
This is the query
queryDetailDesc.JPG
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22784918
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
ID: 22785000
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]
ID: 22785309
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
ID: 22785374
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
ID: 22785396
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
ID: 22785635
as I said, you will need 2 queries, you cannot have it in 1 query.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

777 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