Something Printable

[lilboi]

Hey guys!

I need something printable. In my case, it's message history.
I got recipient(s) and their messages.

I can't use the Data Reports because, it only allows 1 query return. In my case, I have to do 2 query returns.

User table:
UserID Name
1 User, One
2 User, Two
3 User, Three

Message table:
MsgID Recipient Body
1 2 "Hello"
2 1,2 "Hi"
3 1,2,3 "Bye"

The way I get the recipients name is to take The field Message.Recipient and parse each UserID by doing a select Name query on User table. Then I combine that with the Message.Body after.

How am I gonna do those 2 query on a Data Report?

Or is there a 1 select query that I can do for SQL that'll do that for me in 1 shot?

Like, get the messages with Recipient # 1.

Thanks!

 

[JoeAtWork]

Or is there a 1 select query that I can do for SQL that'll do that for me in 1 shot?

If you had a normalized table structure it would be easy. Unfortunately you have multi-values in the RecipientID field, which makes it much more complicated.

It's been a long time since I've used Data Reports. Are you sure you can't put a subreport (with a different query) nested inside another report?

 

[lilboi]

thanks Joe!

I'll try and look into subReport. I remember looking into it long ago but it was too complicated so I decided to stick with single report...

 

[lilboi]

Hey again, i looked into subreports and it seems everyone's using it for crystal reports which i don't have.

Any other possible way of outputting my message history, maybe on a window that can be printable instead of going through vb's limited data reports?

 

[JoeAtWork]

I'm 95% sure that VB6 ships with a version of Crystal Reports. Not the latest version by any chance, but I'm sure it would be good enough for your purposes.

You will have to do a little digging how to use it, however, as it's rather well hidden in the documentation.

Or you could go about normalizing your table structure such that
[tt]
Users MessageRecipients Messages
UserID------------UserID |--------MessageID
UserName MessageID---------| Message
[/tt]
When the tables are normalized, you would be able to create a single SELECT statement for the datasource of your report.

 

[BobRodes]

Crystal Reports definitely ships with VB6. You have to install it specifically though, and it's version 4 something. I believe most current is 9 or 10, so it's old. But you can indeed use it.

 

[JoeAtWork]

Yeah, I used it in VB versions 4 and 5. Then in version 6 they really de-emphasized it (actually almost felt like they were hiding it from us).

But even in the old version 4 and 5, it did at least as much as Access reports can do today.