Crystal reports XI use table from two databases
I have two databases with some common fields such as stockcode and available quantity. So I basically want to take 1 table from both databases and compare results. Is this possible?
The only fields I would look at are stockcode, quantity, and date.
The only fields I would look at are stockcode, quantity, and date.
peter57r
If you are connecting directly to the databases and not via a Business oBjects interface then you can select both tables.
ASKER
Ok, one problem. In one table the driver is 103 but in the other table it is 03. How would I go about saying 03 is equivalent to 103?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, I will try that out.
Are you trying to link the tables in the SQL?
mlmcc
mlmcc
ASKER
I am using SQL, OLE(DB) to get the two databases into database expert. Then I just attach the two tables needed by common fields.
Is it a 1 to 1 relationship in the 2 databases?
mlmcc
mlmcc
ASKER
I don't know much about the sql part of making reports. The two tables match on stockcodes. The other field I mentioned is the warehouse field, where in one table a warehouse is '03' and in the other table the same warehouse is '103', a 1 is added to the front of the warehouses in that table.
I need these two fields to coexist and I think the report will work fine.
I need these two fields to coexist and I think the report will work fine.
I think you are going to have to use a subreport for one of the tables.
mlmcc
mlmcc
Can you create a linked table or a view in one of the databases that joins the tables?
mlmcc
mlmcc
ASKER
I was able to link the tables through the stockcode fields and data showed. I need to get Table A warehouse '03' to be equivalent to Table B warehouse '103'.
Kind of a If Table A = '03 then add a 1 to the front and compare to table B. I think I need to make a view but I have never made one.
Kind of a If Table A = '03 then add a 1 to the front and compare to table B. I think I need to make a view but I have never made one.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I don't have DBA, we are pretty much a 1 person IT squad.
Ok it should look something like this
SELECT '1' & InvWarehouse.Warehouse as Table03Warehouse, InvWarehouse.Stockcode, InvWarehouse.QtyOnHand, , BTWarehouseHistory.Datetim
FROM InvWarehouse INNER JOIN BTWarehouseHistory ON InvWarehouse = BTWarehouseHistory.Warehou
InvWarehouse has no date field it is always the current date.
Do I create a view by going to 'Add command' and putting it in there?
Ok it should look something like this
SELECT '1' & InvWarehouse.Warehouse as Table03Warehouse, InvWarehouse.Stockcode, InvWarehouse.QtyOnHand, , BTWarehouseHistory.Datetim
FROM InvWarehouse INNER JOIN BTWarehouseHistory ON InvWarehouse = BTWarehouseHistory.Warehou
InvWarehouse has no date field it is always the current date.
Do I create a view by going to 'Add command' and putting it in there?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, thanks for the help.