| Author |
Message |
Kitty1
Senior Member
Joined: 14 Apr 2010
Online Status: Offline
Posts: 159
|
   Topic: Combine two files into one
Posted: 20 Jan 2011 at 8:21am |
Hello,
I want to write a report that will pull data from 2 different tables, but combine them as one - based on a common field. Specifically, I want to use field PART NUMBER in our inventory file to look up 1. all open lines on purchase orders, also 2. all open work orders. The data has to be blended as one file, sorted by part number. Is there a way to do this???
|
 IP Logged |
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 20 Jan 2011 at 9:23am |
Do you have a third table that identifies all of your part numbers and what they relate to? If you have that, this is not difficult. If you don't have that, you're going to have to write a "Command" which is a SQL Select statement instead of selecting tables and having Crystal create the SQL for you.
Assuming you have three tables, Parts, PurchaseOrders, and WorkOrders, this should get you going in the right direction:
1. Link from Parts to PurchaseOrders on Part Number. Right-click on the link and select "Link Options" and make this a Left Outer Join. This will include all parts from the Parts table even if they're not in PurchaseOrders.
2. Link from Parts to WorkOrders. Make the link a Left Outer Join as in step 1.
3. Set up the record selection formula - In the Select Expert, select any field to get you started - it doesn't matter which field because you're not actually going to use it. Click on "Show Formula" and then on "Formula Editor". You'll need to enter something like the following:
(not IsNull({PurchaseOrders.PartNumber}) or not IsNull({WorkOrders.PartNumber)) and
(IsNull({PurchaseOrders.PartNumber}) or {PurchaseOrders.OpenOrder}= 1) and
(IsNull({WorkOrders.PartNumber}) or {WorkOrders.OpenOrder} = 1)
This assumes that there could be a purchase order without a work order or a work order without a purchase order. If there will always be both a purchase order and a work order, you can skip the steps to change the joins and just use the Select Expert to pull in open purchase orders or open work orders.
-Dell
|
|
|
|
IP Logged |
|
Kitty1
Senior Member
Joined: 14 Apr 2010
Online Status: Offline
Posts: 159
|
Posted: 21 Jan 2011 at 7:44am |
hello and thank you for your reply. what i want to do is read all the records, both work orders and purchase orders, as if they are one file. in other words, i use the part to link to each file, but i would like to somehow output them to a temporary file, where i could then sort both files together by due date. Is this possible ?
|
|
IP Logged |
|
Kitty1
Senior Member
Joined: 14 Apr 2010
Online Status: Offline
Posts: 159
|
Posted: 21 Jan 2011 at 8:11am |
|
just another point on the report i'm writing and what i'm trying to do. what i want to do is when a person enters a part number for parameter, i want to print the on-hand qty. of the part, then, in order of date, depending on if it is a work order or a purchase order, add or subtract and keep a running total of what the qty. on hand will be as of that date.
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 21 Jan 2011 at 10:05am |
How are you SQL skills? The only way I can think of to do what you need is to write command that has a UNION query that will pull together the work orders and the purchase orders as if they were in the same table.
You mention files. What type of database are you connecting to? Commands are only available for databases - they're not available for file-based data. For example, if your report is reading data from Excel files, you'll need to have a way of loading them into a database in order to create this report.
-Dell
|
|
|
|
IP Logged |
|
Kitty1
Senior Member
Joined: 14 Apr 2010
Online Status: Offline
Posts: 159
|
Posted: 24 Jan 2011 at 7:10am |
Hi, Thanks again for your follow-up. :-)
I have no SQL skills. :-( What I do know about Crystal reports is self-taught. I'm actually pulling the information from tables. Is there an easy way to explain how to do a Union of the two tables ?
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 24 Jan 2011 at 9:30am |
First off, what type of database are you connecting to? I need that to determine whether a command is even an option. Then I would need to know the structure of your tables, how they "link together", and which fields you need. From there I can probably put together some sample SQL for you.
-Dell
|
|
|
|
IP Logged |
|
Kitty1
Senior Member
Joined: 14 Apr 2010
Online Status: Offline
Posts: 159
|
Posted: 25 Jan 2011 at 10:12am |
|
Hi, I'm connecting to ODBC data tables.
|
|
IP Logged |
|
Kitty1
Senior Member
Joined: 14 Apr 2010
Online Status: Offline
Posts: 159
|
Posted: 25 Jan 2011 at 10:16am |
I did read about Union queries, but according to what I read, both tables must have the same table structure. you are not able to perform a union on two tables with different fields. These tables both have a common field (part number) but I want to look at P.O. # in the purchase order table, and Work Order # in the job file and use this as one field, also qty on order/qty. committed and due dates. They do not have the same table structures.
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 25 Jan 2011 at 11:03am |
If they have similar structures and the fields you're using are of the same type, you can use them - the field names don't have to be the same, just the field types. If there is a field in one table that you need, but no corresponding field in the other table, use Null in the select.
The field names in your result set will be the ones from the first Select in the union query.
Here's an example
Select
PO_Number as OrderNumber,
Order_date,
qty as quantity
from purchase_orders
union
Select
WO_Number as OrderNumber,
Due_Date as Order_Date,
qty_on_order as quantity
from work_orders
This pulls data from two tables with different field names, but the same structure in terms of the fields that are included in the query.
-Dell
|
|
|
|
IP Logged |
|
|
|
