Help with SQL and lookup 2 files.

[ChrisH2]

Hi all,

I have 3 files:- Customer, Items and History.
I want to group by customer and show all items followed by all historical items as follows:-

Customer: Fred Bloggs
Item No.: 1001
1002

Hist No. 5001
5002

Each file has a customer code but using visual link expert I can not use "lookup all of one and all of others (A to B, A to C)" as it is disabled.

The only way I can resolve this is by using a subreport but I think this is not efficent. Does anyone know how to do this using an sql query in crystal.

I am using Crystal Dev 8.5 and MS SQL 7.

Thanks in advance,

Chris

 

[AndrewBaines]

Not sure what the problem is - the (A to B, A to C) is for file based databases, not real database like SQL.
Your report looks like it should just have the 3 tables linked normally ie Customer - History - Products, then have the history in detail, grouped by product and then customer.

 

[ChrisH2]

Thanks for your reply,

I am a bit new to crystal and yes your right about the (A to B, A to C) option.

But I have 3 files Customer, Items and Historical Items, nothing to do with products. I just want to group by customer and show all items for that customer followed by all historical items. I have managed this by adding a subreport of historical items to the group footer but was wondering if there is a better way.

Thanks,

Chris