Author |
Message |
Rob-VDMChem
Newbie
Joined: 17 Oct 2007
Online Status: Offline
Posts: 9
|
Topic: Merging two tables Posted: 19 Nov 2007 at 11:42am |
Is it possible to have the data from 2 subreports show up together and not separately? Allow me to clarify, I have a purchase order report I do on a monthly basis. Some PO's are OPEN (PO table) and some are CLOSED (POHIST table). I use the LastFullMonth filter on the orderdate field for showing the last full month. What I was wondering is can I have the data from the 2 different data sources displayed all together in order by PO Number rather than one report displaying first and the other at the end. Hope this makes sense. TIA!!
Edited by BrianBischof - 19 Nov 2007 at 2:07pm
|
IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 19 Nov 2007 at 12:58pm |
You have to use a SQL Command object and use the UNION keyword to join the data. Create two select statements that return the same fields and test them. Once they are working, put the UNION keyword between them to join the data into a single virtual table. That allows you to sort and filter the data as if it were one table.
I cover the UNION keyword and lots more database tricks in Chapter 11, "Maximizing SQL Performance", of my new CR Encyclopedia book.
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
IP Logged |
|
Rob-VDMChem
Newbie
Joined: 17 Oct 2007
Online Status: Offline
Posts: 9
|
Posted: 19 Nov 2007 at 1:53pm |
Thanks. You are the man!
|
IP Logged |
|
Rob-VDMChem
Newbie
Joined: 17 Oct 2007
Online Status: Offline
Posts: 9
|
Posted: 20 Nov 2007 at 6:33am |
Ok Brian I have a new issue. I tried to create a simple SELECT command in the SQL expression editor, SELECT "PO"."ORDERDATE" FROM "MP2PROD"."dbo"."PO" "PO" - MP2PROD being the ODBC database name) It keeps coming back saying "incorrect syntax near keyword SELECT" I've tried it with and without the WHERE ("PO"."ORDERDATE">={ts '2007-10-01 00:00:00'} AND "PO"."ORDERDATE"<{ts '2007-11-01 00:00:00'}) Any suggestions!!!
PS I'm ordering your book today!!!
|
IP Logged |
|
Lugh
Senior Member
Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
|
Posted: 20 Nov 2007 at 7:04am |
I'll admit that I've never really used the SQL Expression bit before.
But, when I look at it, it seems to be using single quotes around the field names, not double
quotes. Could that be your issue?
What kind of database are you running this against?
|
IP Logged |
|
Rob-VDMChem
Newbie
Joined: 17 Oct 2007
Online Status: Offline
Posts: 9
|
Posted: 20 Nov 2007 at 7:13am |
Thanks for the reply. I have tried it both ways with single and double quotes with the same results. It's an access 2000 database ODBC connection. It is the maintenance software our company uses called MP2.
|
IP Logged |
|
Rob-VDMChem
Newbie
Joined: 17 Oct 2007
Online Status: Offline
Posts: 9
|
Posted: 20 Nov 2007 at 8:16am |
I think I found my problem. I was trying to create the command int he expression not the command area. I will try it and let you know. Sorry about that!
|
IP Logged |
|
Rob-VDMChem
Newbie
Joined: 17 Oct 2007
Online Status: Offline
Posts: 9
|
Posted: 20 Nov 2007 at 11:36am |
OK, here is what I have int he SQL COMMAND, SELECT PONUM,ORDERDATE,SUBTOTAL FROM POHISTH UNION SELECT PONUM,ORDERDATE,SUBTOTAL FROM PO Do the common fields need to be linked?
|
IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 20 Nov 2007 at 12:01pm |
No linking is necessary. What you are doing is creating two distinct lists of data from two different tables. The UNION keyword then appends the second list to the first list into a virtual table and makes it all appear as one single table. Thus, the number of rows in the new table will be equal to how many rows are in the first SELECT plus how many rows are in the second SELECT.
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 20 Nov 2007 at 12:02pm |
Oh yeah - thanks for supporting the forum by buying a book! If more people did this, I could spend more time creating new content for the site.
Edited by BrianBischof - 20 Nov 2007 at 12:04pm
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
IP Logged |
|
|