Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Crystal, SQL & aliases 1

Status
Not open for further replies.

paula

Technical User
Feb 23, 2001
18
GB
Bit of a long shot, this one!

I'm having problems getting the information I want from linked tables (knowhow/research database on a SQL server).

Table 1 holds most of the obvious record fields (e.g. item ID, title, date added, added by ID etc.). However, to get the full name of the person who added the item, I need to link from the 'AddedByID' in Table 1 to the 'UserID' field in Table 2 (which holds all the user info.). I can then drop the FullName field from Table 2, into my report.

Which was fine, until I realised I also needed a further name in my report - the creator name. Of course, if I create a second link from Table 1 to Table 2 (CreatorID to UserID) and try and pull the name out in the same way as before, there's no way of distinguishing which name I want!

I'd be grateful for any help at all - if you can understand the above, of course!

Thanks.
 
You add a second instance of the table, and Crystal will ask you to give that table an alias. Then you treat this second instance as a second table. You will get different values depending on which table's fields you use.

Database menu has the option to set the alias of any table, even if it is only used once. This can make formulas much easier to read. Ken Hamady
Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)
 
Thanks Ken...

I've tried this, but it seems to block out all the data in the report when I update (having added the new column).

Excuse my pitiful lack of SQL knowledge, but do you think this is due to the type of link?

Thanks again,

Paula
 
If it eliminates all of the data, that means that it isn't finding any matches between those two tables. Are you sure that you have the correct fields linked? Try just those two tables and confirm that the link works correctly.

You can try changing the joins to Outer joins, but if the link eliminates everything when equal, than you may have no matches at all. Ken Hamady
Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)
 
Thanks Ken...I've put your advice into action.

It seems that when I work with just the 'AddedBy' usernames, 5 pages of results are returned. However, if I add in the 'CreatedBy' usernames, the report reduces to just 2 pages.

As far as I'm aware, some entries in 'CreatedBy' will be blank, but I'd like these to show in the report.

I'd be very grateful if you could give me any further suggestions.

Thanks again,
Paula
 
Use Database-Visual Linking Expert to open the window with the arrows. Highlight the arrow and look at it's options. Use a Left Outer Join here and that might help.

Also, make sure the arrow points TO the table that might have a match as opposed to FROM this table. In other words, both arrows should go out from the same starting table. Ken Hamady
Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top