I have very little knowledge of Crystal and pretty much just muddle through it, so please bear with me. I have two crystal reports, one based on an Access database and one based on a MAS200 database. I need to combine the information from these two reports into one. The report based on the Access database has calculations based on CARs issued to a vendor and how they've affected the company. The report based on the MAS200 database shows the number of shipments a vendor made to us and the total number of parts. I need to combine these into a "Vendor Scorecard", plugging the correct numbers into the correct places. The MAS200 database and the Access database have one field (vendor #) that will link. It's possible I may be able to add another field (vendor type) to the Access database to provide a second link. In my effort to provide as much information as possible in order to get as much help as possible, I've pasted in the SQL queries for each of these reports. Would I be better off to try to create this in Access? I wanted to use Crystal so it could be accessed through MAS200. Any help will be greatly appreciated. Thank you.
Jody
Report #1 (MAS200 Database)
SELECT
POM_RecptHistoryLine."PurchaseOrderNumber", POM_RecptHistoryLine."ItemNumber", POM_RecptHistoryLine."ReceiptQty",
POP_RecptHistoryHeader."ReceiptDate", POP_RecptHistoryHeader."VendorNumber", POP_RecptHistoryHeader."VendorName"
FROM
"POM_RecptHistoryLine" POM_RecptHistoryLine,
"POP_RecptHistoryHeader" POP_RecptHistoryHeader
WHERE
POM_RecptHistoryLine."PurchaseOrderNumber" = POP_RecptHistoryHeader."PurchaseOrderNumber" AND
POM_RecptHistoryLine."ReceiptType" = POP_RecptHistoryHeader."ReceiptType" AND
POM_RecptHistoryLine."ReceiptNumber" = POP_RecptHistoryHeader."ReceiptNumber" AND
POM_RecptHistoryLine."RequiredDate" = POP_RecptHistoryHeader."RequiredDate" AND
POM_RecptHistoryLine."UseTax" = POP_RecptHistoryHeader."UseTax" AND
POM_RecptHistoryLine."WarehouseCode" = POP_RecptHistoryHeader."WarehouseCode" AND
POM_RecptHistoryLine."ReceiptAmount" = POP_RecptHistoryHeader."ReceiptAmount" AND
POP_RecptHistoryHeader."ReceiptDate" >= {d '2007-01-01'} AND
POP_RecptHistoryHeader."ReceiptDate" <= {d '2007-01-17'}
ORDER BY
POP_RecptHistoryHeader."VendorNumber" ASC,
POM_RecptHistoryLine."ItemNumber" ASC
Report #2 (Access Database)
SELECT
A2001_CORRECTIVE_ACTION_TABLE.`CAR #`, A2001_CORRECTIVE_ACTION_TABLE.`DISRUPTION TYPE`, A2001_CORRECTIVE_ACTION_TABLE.`DATE ISSUED`, A2001_CORRECTIVE_ACTION_TABLE.`PROBLEM TYPE`, A2001_CORRECTIVE_ACTION_TABLE.`SupplierID`,
SUPPLIER_ID.`SUPPLIER NO`
FROM
`2001 CORRECTIVE ACTION TABLE` A2001_CORRECTIVE_ACTION_TABLE INNER JOIN `SUPPLIER ID` SUPPLIER_ID ON
A2001_CORRECTIVE_ACTION_TABLE.`SUPPLIER NO` = SUPPLIER_ID.`SUPPLIER NO`
WHERE
A2001_CORRECTIVE_ACTION_TABLE.`DATE ISSUED` >= {ts '2006-07-01 00:00:00.00'} AND
A2001_CORRECTIVE_ACTION_TABLE.`DATE ISSUED` < {ts '2006-09-30 12:00:01.00'}
ORDER BY
SUPPLIER_ID.`SUPPLIER NO` ASC,
A2001_CORRECTIVE_ACTION_TABLE.`PROBLEM TYPE` ASC,
A2001_CORRECTIVE_ACTION_TABLE.`DATE ISSUED` ASC
IP Logged
