DATA PULL FROM OPEN AND HISTORY TABLES 1

[BHAT100]

My report is follows:

GP1: CUSTOMER CLASS(RM00201)
GRP2: CUSTOMER(RM00101)
Detail: DOCUMENT# DOCUMENT AMOUNT 0-30 30-60 60-90 OVER90


and I am using following tables:

RM OPEN: RM20101
RM HISTORY: RM30101
RM APPLY OPEN:RM20201
RM APPLY HISTORY: RM30201
RM CUST MASTER: RM00101

HOW CAN I PULL DATA IN OPEN AND HISTORICAL TABLES TOGETHER IN ONE COLUMN. I AM PULLING DOCUMENT NUMBER AND DOCUMENT AMOUNT AND DATE FROM BOTH TABLES SO THAT I CAN CALCULATE AGING ON DOC DATE
Any Idea would be really appreciated

Thanks

Bhat

 

[Turkbear]

Hi,
A few details, please:
What database and Version?
What Crystal Version?

Do you have access to the database and can you create views or stored procedures?


In general, you could create a view that uses the UNION operator to 'merge' the 2 tables as needed.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[BHAT100]

Thanks for reply.

Answer to questios are:

Great plains ver 8.00
crystal reports 9.00

I have access in SQL SERVER through enterprise manager but it does not accept 'UNION ALL' command. is there any other way in crystal to pull data from open and historical tables as mentioned above. These table have same fields only difference of data i.e.open or historical

Thanks

Bhat

 

[lbass]

Do you have "Add command" as an option when you select your datasource? You should be able to do a Union All there.

-LB

 

[BHAT100]

To Paraphrase:"The Help you get is proportional to the Help you give

That's what I have started doing for great plains, frx and crystal issues

Thanks for reminder, Turkbear

Bhat

 

[BHAT100]

Sorry for bothering you again.

I don't see any add command option. but in query analyser. I created view but it is not merging all the records/ transactions of BOTH tables.

I am using following view:-

CREATE VIEW dbo.RM20101_V2 AS

SELECT SourceTable = 'RM20101',CUSTNMBR, CPRCSTNM, DOCNUMBR, CHEKNMBR, BACHNUMB, BCHSOURC, RMDTYPAL,TRXSORCE, DUEDATE, DOCDATE, POSTDATE, PSTUSRID, GLPOSTDT,
LSTEDTDT, ORTRXAMT, CURTRXAM, SLSAMNT, DISCDATE, TRXDSCRN, SLPRSNID, VOIDDATE FROM RM20101
UNION ALL
SELECT SourceTable = 'RM30101',CUSTNMBR, CPRCSTNM, DOCNUMBR, CHEKNMBR, BACHNUMB, BCHSOURC, RMDTYPAL,TRXSORCE, DUEDATE, DOCDATE, POSTDATE, PSTUSRID, GLPOSTDT,
LSTEDTDT, ORTRXAMT, CURTRXAM, SLSAMNT, DISCDATE, TRXDSCRN, SLPRSNID, VOIDDATE FROM RM30101

any idea why view is missing records. it seems to me it is picking all customers but when i am compairng with aged historical report it shows missing transactions in few customers.

Any idea would be great help to me

thanks

BHAT

 

[BHAT100]

Sorry for bothering again.

The view is picking up all the records. The aging report has difference due to write off and exchange loss/gains.

what are tables for write off and exchange gain/loss in Great Plains

Thanks for your help

 

[BHAT100]

Thanks, I have found tables
RM20201(OPEN)
RM30301(HISTORY)

Bhat