Two Table Terror!!

[Monkeyboy126]

Hi

I have 2 IT Helpdesk tables (SQL), 1 storing Problem records and the other storing Change requests. Both contain similar fields such as open datetime, analyst ID, user name, summary, close datetime etc.

My requirment is to produce a report based on analyst activities for the day (so how many calls have they closed etc). The alalyst name is obtained from a third table called 'Contact' and is related via the ID in the other two tables.

I can't select all three tables and link them as I get the usual 'unsupported' error message. The issue happens when utilising the problem and change tables as there doesn't seem to be a common link between them.

I can the following and produce two seperate reports (one for probs and the other for changes):

Probrec.Analyst -> Contact.last name

Any ideas... I realise I have probably explained this badly but I hope you get the idea.

Regards


Paul

 

[cmpgeekinGa]

you said the analyst is listed in both the problem and changes tables as well as the contact table...


are the analysts information listed differently in each table? i am just wondering why you can not connect them if you started with the contact table and then linked it to the others based in the analysts' information...

if i misread something i do apologize...

Thanks for everything...

I run Crystal 8.5 on an Oracle 8 database...
cmpgeek

 

[synapsevampire]

Linking 3 tables does not produce "...the usual 'unsupported' error message"

If you have different data sources then you might get this, as can other problems.

Try supplying technical information:

Crystal version
Database used
Example data (with relevant tables/fields)
Expected output

Text descriptions can be helpful, but only when the other info is known.

Also try rephrasing this "The issue happens when utilising the problem and change tables as there doesn't seem to be a common link between them." My response would be to stop utilising the problem

-k

 

[IdoMillet]

If each Analyst can have multiple "Problems" and multiple "Changes" then linking would produce "Record Inflation".

One solution is to create a main report listing just "Problems" and insert the changes as a linked subreport.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Monkeyboy126]

Good point

OK, in the problem (and change) table there is an Analyst ID field, which I link to the contact table. The contact table contains fist name, last name, address etc.

So I have:

Prob.Assignee-----!
!
-------Contact.Lastname
!
Change.Assignee--!

Does this help or even make sense??

CR 8.5
SQL Database

 

[IdoMillet]

Just reverse the direction of one of the links (select the link in Crystal, Right-click, ...).

This still doesn't address the issue I raised above.

hth,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[IdoMillet]

It also doesn't address Outer Join requirements...


CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

I thought that you couldn't link these?

You might create a Union query of the prob and change joined to the contact table as a View on the SQL Server database, or try a subreport as Ido suggested.

-k